VBA - Creating Table From Query and Connecting to A Linked Table

[Dragenesis]

How do I create a table with VBA using the results of a query? Also, I want to use ADO to access a table in the current access database that's linked to an external database table. However, whenever I try to run initiate the connection, it says that the file is currently open and being edited. Should I use another method to access the table?

 

[boblarson]

How do I create a table with VBA using the results of a query? Also, I want to use ADO to access a table in the current access database that's linked to an external database table. However, whenever I try to run initiate the connection, it says that the file is currently open and being edited. Should I use another method to access the table?

Post your code. I will bet you are opening a connection the wrong way. Just to give you a head start - the code for a connection to connect to a linked table is just:

Dim cnn As ADO.Connection

Set cnn = CurrentProject.Connection

and that is the connection string. No setting the normal string for accessing an outside db.

 

[Dragenesis]

My code is:

Dim con As ADO.Connection
Dim SQLstr As String

con.open CurrentProject.Connection
set SQLstr=[SQL Query]

 

[boblarson]

Don't use con.open CurrentProject.Connection use

Set con = CurrentProject.Connection