CODEWITHSUNDEEP
ms-accessvba
Jesus Espiga
Jesus Espiga

Reputation: 69

Import a query from one database to another

I'm trying to import a query from one database to another using the following syntax:

strDbFile = "P:\Cadworx P&ID Implementation\3 Piping\P&IDs Jesus Test\Testproject\myTest.mdb"
strSourceTableName = "qryComponents_Process_Lines_Report"
DoCmd.TransferDatabase acImport, "Microsoft Access", strDbFile, acQuery, strSourceTableName, "Test", False

Yet when I do this, it gives me an error saying that the table or query was not found. What I find odd is that it does not have the name that I assigned it just before running the TransferDatabase command. See below.

Error for Query name

Upvotes: 1

Views: 1067

Answers (1)

Jesus Espiga
Jesus Espiga

Reputation: 69

This is the solution:

strSourceTableName = "Components-Process Lines"                                                                   'Source Table
strLinkName = "Components-Process Lines"                                                                          'Final table name once link has been established

strConnect = "MS Access;PWD=" & strPassword & ";DATABASE=" & strDbFile

Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef

tdf.Connect = strConnect                                                                           'Establish link between databases
tdf.SourceTableName = strSourceTableName
tdf.Name = strLinkName
dbs.TableDefs.Append tdf

strSourceTableName = "qryComponents_Process_Lines_Report"
DoCmd.TransferDatabase acImport, "Microsoft Access", strDbFile, acQuery,    strSourceTableName, "Test", False

All I had to do is create a link to the original table where the query in the source database is source query comes from (located in source database). Once this is done, the import process was successful.

Upvotes: 1

Related Questions