querydef: using external odbc

[mg]

how to i create a querydef in msaccess(2007) that references an extrnal
database using an odbc??
i have made on-the-fly queries to the odbc successfully in vba code, but i
cant figure out how to include the connectionstring information when i build
a query by hand in the access environment.

help!
and thanks

 

[MGFoster]

how to i create a querydef in msaccess(2007) that references an extrnal
database using an odbc??
i have made on-the-fly queries to the odbc successfully in vba code, but i
cant figure out how to include the connectionstring information when i build
a query by hand in the access environment.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Make the query an SQL Pass Thru query (Query > SQL Specific > Pass
Through). In the ODBC Connect String property place the connection
string.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSZDU+4echKqOuFEgEQK1XwCfZubqks5nxhkWgyTTJnhsLtF2c74An3k+
7sSpc3b1auLgYPGSWiFxU4q8
=jaBE
-----END PGP SIGNATURE-----

 

[.]

sorry, i am not understanding what to do or where to do it.....

 

[david]

The generic form of a select query is

select * from [database].

ODBC databases start with 'odbc' like this:

select * from [ODBC;fred].mytable

The rest of the connect string information goes in there after ODBC

Note: Jet will cache the ODBC connection, you may have trouble
if you try to connect to a different ODBC data source.

(david)

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

When looking at the Access Database Window there are tabs along the top
(or left side under Objects) of the window;

1. Click the Queries tab.
2. Click the New Query button.
3. The New Query dialog box will appear - select Design View.
4. The Query Design Grid will appear with the Table Selection dialog
box. Click the Close button - you will not be selecting a table.
5. On the Access menu (top of the Access window):
a. Select these menu items: Query > SQL Specific > Pass-Through.
6. The SQL view will appear.
7. Type in the SQL for the ODBC DB you want to access.
8. On the Access menu:
a. Select these menu items: View > Properties.
9. The query Properties dialog box will appear.
10. In the "ODBC Connect Str" property enter the ODBC connection string.

See this site for all your connection string needs:

http://www.connectionstrings.com/

11. Test the query and when finished close and Save the QueryDef - give
the QueryDef a name when saving it.

Try RTFM next time.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSZHqioechKqOuFEgEQLflwCgrC54K4DVrvCkh3EwF30EWzb9LNIAoLlJ
IZg0R8Qk8o8pObZOlDjri3Yg
=h0Ml
-----END PGP SIGNATURE-----