Access VBA update table from external ODBC table

Question

I am trying to update one of my local database table with an external ODBC table in Access VBA.

Part of the update code is shown as below.

Dim db As Database
Dim qdf As QueryDef
Dim updateQuery As String

updateQuery = "My_Update_Query"

Set db = CurrentDb()
Set qdf = db.CreateQueryDef
qdf.SQL = updateQuery

qdf.Execute dbFailOnError

My code stops running at the qdf.Execute line. I got the error message "Object invalid or no longer set." Error code 3420.

In my update query, I have set my local table field value equal to external ODBC table field value. The external ODBC table is already imported as a link table in my Access.

May I know if I still need to make an explicit connect to the external table here? If so, how can I go about this?

Thanks a lot in advance for the help.

I always use recordsets for this type of operation if that helps that way you can specify the connection string directly and run transactions on the recordset. — engineersmnky
Do you have a saved query named My_Update_Query, and are you trying to execute that query? If so, does it work without error when you test it in the Access query designer? — HansUp
To me this looks possibly like a missing object library reference. VBA > Tools > References. — Smandoli
@HansUp I got a pop up window asking me to enter parameter value for the linked table. That's why I'm confused if that linked table is really connected when I execute the sql. — got2nosth
@engineersmnky are you referring to the recordset.edit method? with that it seems I have to update my record one by one instead of executing a simple update query. is there an easy way to achieve that? another reason I did not choose record set is I'm not sure if it can return me the result of the update, being it success or failure. — got2nosth

Mike Mike · Accepted Answer · 2013-12-20T19:21:29

"My_Update_Query" is not a valid SQL statement, which is what qdf.SQL would need. If that's the name of a query you want to run use:

Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs("My_Update_Query")
qdf.Execute

Or eliminate the whole QueryDef stuff and just say:

DoCmd.OpenQuery "My_Update_Query"

As long as the ODBC table is linked to your database you shouldn't have to explicitly connect to it each time.

Access VBA update table from external ODBC table

1 Answers