Creating a table link via ODBC in another ACCESS database

[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?

 

[Douglas J. Steele]

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!)