How to link to a query in an Access Project (ADP) 1

[CharlieT302]

Hello,

I inherited a database that contains links to queries in an Access Project file. The query links display as table links in the .mdb file.

I have just created another query in the Project and would like to link to it the same way (from my .mdb file), but am unsure how the links were originally created. Does anyone know?

Thanks

 

[PWise]

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DRIVER=SQL Server;SERVER=SERVERname;APP=Microsoft Office 2003;DATABASE=dbname;Trusted_Connection=Yes;", acQuery, "Queryname", "Linkedtablename"

 

[CharlieT302]

Thank you,

Where do I create and run the code,? Is this a Module?

 

[PWise]

If this is a one time thing then press <Ctrl-G> and paste this code in the immediate window and press enter.
if you need this for more then on time create a form and paste this behind a command button

 

[CharlieT302]

Once the link is created, I should not need to do it again, since it is linked to a query and, therefore, dynamic.

However, "Paste it in the immediate window"? What do you mean by that?... the main database window for the .mdb file?

 

[CharlieT302]

Nevermind. Ctrl-G...got it. I was in a hurry before and didn't read the message thoroughly enough.

I will give it a try. Thanks

 

[PWise]

What i ment by a one time thing is that there is only 1 query that you want to link to but if today you want to link to querya and at some other point to a diffrent query use the second way

 

[CharlieT302]

Alright, I am just sitting down to do this. I haven't linked in this fashion before so please be patient. Obviously, I need to supply various information to the code. Are my assumptions below correct, or did I leave anything out?

Parameters From your code:
ODBC Database ? Do I replace this with anything
SERVERname Server where the Access Project resides
dbname Name of the Access Project
Queryname Name of the Access Project query to which I am linking.

Linkedtablename The desired name for the resulting link that connects to the query.

 

[PWise]

A Access Project is just a front end for a database usally Sql Server

ODBC Database = ODBC Database (No Change)
SERVERname = Server where the database resides
dbname = Name of the Database
Queryname = name of the query in the database(on the server)
Linkedtablename = The desired name for the resulting link that connects to the query

 

[CharlieT302]

I truly appreciate your help, but I want to make sure of one thing.

The query I wish to link to is created in the Access Project file. When you mention "database" (in the parameter below) is this the name given to the Access Project file or the name of the SQL data file?

dbname = Name of the Database

Side Note: If this matters; the query I am linking to was created simply by copying and pasting an existing query (within the Project file) and then removing an unwanted table.

 

[PWise]

SQL data file

 

[PWise]

Sorry that should be

Sql Database name

 

[CharlieT302]

Thank you ever so much. That worked great.