CODEWITHSUNDEEP
sqlvbams-accessodbcdatabase-connection
got2nosth
got2nosth

Reputation: 618

Access VBA update table from external ODBC table

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.

Upvotes: 0

Views: 1639

Answers (1)

Mike
Mike

Reputation: 620

"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.

Upvotes: 2

Related Questions