Creating a table link via ODBC in another ACCESS database

  • Thread starter Thread starter sailor_123
  • Start date Start date
S

sailor_123

I am trying to create a link of a table present in Postgres database (via
ODBC) into another ACCESS database.

So far, I succeeded in creating the ODBC link to current database and then
exporting the link to the third (target) ACCESS database. But current
database will be changed to read-only and therefore I can no longer create a
link in it.

Is there a method to directly create a link into another ACCESS database?

DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;DSN=my_dsn;", acTable, "ODBC_schema.source_table_name",
"current_db.target_table_name", , True

in the above call, how can I change current_db to path and filename of
another ACCESS database?
 
I believe you can instantiate another instance of Access, set its
CurrentDatabase appropriate, then run the DoCmd there:

Dim appAccess As Access.Application

Set appAccess = New Access.Application
appAccess.OpenCurrentDatabase "F:\Folder\File.mdb"
appAccess.DoCmd.TransferDatabase "ODBC Database", _
"ODBC;DSN=my_dsn;", acTable, "ODBC_schema.source_table_name",
"current_db.target_table_name", , True
app.Access.CloseCurrentDatabase
Set appAccess = Nothing

You can also use DAO, and set the TableDef object's Connect property
appropriately. (Pretty sure you can do this using ADOX as well, but I'm too
lazy to check!)
 
Back
Top