Trying to query ODBC source without linked table

[adamss]

From what I've seen in other posts, it looks like I should be able to
run a query through Jet SQL that does something like

select MyField from MyTable in "ODBC;DSN=MyDSN;UID=;PWD="

When I do this, it tells me that it can't find the file
"c:\ODBC;DSN=MyDSN;UID=;PWD=". Is there a way to directly query
another database via ODBC just used Jet SQL?

Thanks.

 

[Douglas J Steele]

Simply create a linked table then use it like any other table.

Alternatively, create a pass-through query.

Change your SQL to Select MyField From MyTable (i.e.: what you have already,
without the in "ODBC;..." part) Instead, select SQL Specific from the Query
menu, and choose the Pass-Through option. In the Query properties window,
you should see a property labelled "ODBC Connect Str". Pasted what you had
before in your "ODBC;..." string into that property.

 

[Sylvain Lafontaine]

If the name and location of the database file are C:\MyMDB.mdb then the
following should work:

select MyField from MyTable in "C:\MyMDB.mdb" ...

Of course, writing something like C:\ODBC mean absolutely nothing; unless
you have a database file with the name ODBC. If you want to use a DSN, then
something like should work:

select MyField from MyTable in "ODBC;DSN=MyDSN;UID=;PWD=". " ...

However, this is very old stuff and I'm not sure if these will work.