CODEWITHSUNDEEP
ms-accessvbaodbcms-access-2007
Alvi John
Alvi John

Reputation: 81

Updating the data in an ODBC datasource with that in local Access DB?

I have a remote ODBC data source 'A' whose values is to be updated according to the table 'B' in the local access database. How can I do the same ?. I tried using pass through queries, however I am not able to access both the remote and local source in ONE SINGLE query. How should I do the same?

How does link tables work? Can I link my local table to the ODBC database dynamically using VBA?

Upvotes: 0

Views: 263

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123584

In your Access database simply create a Linked Table for your ODBC data source:

ODBC.png

For detailed instructions, see

About importing and linking data and database objects

Once that is done, you can use the linked table and the local table(s) in the same query from within Access:

Query.png

Upvotes: 1

John Bingham
John Bingham

Reputation: 2006

You can't create a link dynamically that I am aware, though you could refresh a link that already existed.

What sort of joining is required? If you're just updating a single or a few rows, you might do this: (I can only write this using ado (means adding a reference to microsoft.activex data objects)

dim ss as string 'tempstr, sqlstr whatever you want to call it

dim cn as object: set cn = createobject("adodb.connection")
cn.Connection = [connection string required for ODBC datasource]
cn.Open

dim rst as object: set rst = createobject("adodb.recordset")
rst.open "SELECT required_data_column_list FROM localTable [WHERE ...]" _
    , currentproject.connection, adOpenStatic, adLockReadOnly
do while not rst.eof
    ss = "UPDATE ODBC_TableName SET ColumnA = '" & rst.Fields(3) & "' [, ... ]
    ss = ss & " WHERE ... "
    cn.Execute ss
    do while cn.State = adStateExecuting
    loop
    rst.movenext
loop
set rst = nothing     'these statements free up memory, 
set cn = nothing      'given that these objects are unmanaged

Hope this helps

Upvotes: 0

Related Questions