Anyone know the syntax for a select query of a table outside of the current db I am operating from.

For instance, let's say I am working out of SunsetForms.Mdb and am writing a query to SunsetMainDB.Mdb file. How would I write that query? And, do I need to establish some sort of permanent connection to the external .mdb file upon an open event or something?

I get stumped after something like this attempt:

SELECT JobName
FROM Master_Jobs;

How would the query know where the "Master_Jobs" table is if it is outside the the database which is running the query?

I presume you would want to do a connection through vba first.
If so, how would I go about that?

Thanks for any help you can give me.
Coby.

You can do this either through a DSN or a Jet Provider statement.

First you open a connection to the database using the DSN or provider statement. Then you create a recordset on the table using the connection.

So that would look something like (using ADO and a DSN):

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String

sSQL = "SELECT JobName FROM Master Jobs"

Set cn = New ADODB.Connection
cn.Open "DSN=YourDSNName;"
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic

...

Something like that. I use ADO and SQL back ends mostly.

Did any of that help?


mmcdonal

Look it up at: http://wrox.books24x7.com