Complex query using external database

  • Thread starter Thread starter Vladimir
  • Start date Start date
V

Vladimir

Hello,

Does MS Access support subj like that SELECT * FROM
[ODBC;DSN=<system_dsn_or_smth_else>;]?
What is the syntax of SQL statement to use with OLE DB (for example, Jet OLE
DB Provider)?

Regards,
Vladimir.
 
Sure:

SELECT *
FROM SomeTable
IN "" [ODBC;DSN=<system dsn>]

You can also specify more than just DSN - see the Connect String property in
help.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

P.S. It's OK to cross-post to a couple of relevant newsgroups, but posting
to 7 is a bit over the top. Please see:
http://www.mvps.org/access/netiquette.htm
 
When I try to write smth like this SELECT * FROM
[Microsoft.Jet.OLEDB.4.0;Database=C:\...\FILE.MDB;] I get error.

John Viescas said:
Sure:

SELECT *
FROM SomeTable
IN "" [ODBC;DSN=<system dsn>]

You can also specify more than just DSN - see the Connect String property in
help.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

P.S. It's OK to cross-post to a couple of relevant newsgroups, but posting
to 7 is a bit over the top. Please see:
http://www.mvps.org/access/netiquette.htm

Vladimir said:
Hello,

Does MS Access support subj like that SELECT * FROM
[ODBC;DSN=<system_dsn_or_smth_else>;]?
What is the syntax of SQL statement to use with OLE DB (for example, Jet OLE
DB Provider)?

Regards,
Vladimir.
Click to expand...
Click to expand...
 
Ah. You cannot use ODBC when the file in an Access desktop database (.mdb).
An mdb file can contain hundreds of tables or queries, so you must specify
the object name in the FROM clause. Use the IN clause to point directly to
the file:

SELECT * FROM SomeTable
IN "C:\...\File.mdb";

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Vladimir said:
When I try to write smth like this SELECT * FROM
[Microsoft.Jet.OLEDB.4.0;Database=C:\...\FILE.MDB;] I get error.

John Viescas said:
Sure:

SELECT *
FROM SomeTable
IN "" [ODBC;DSN=<system dsn>]

You can also specify more than just DSN - see the Connect String
Click to expand...
property
in
help.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

P.S. It's OK to cross-post to a couple of relevant newsgroups, but posting
to 7 is a bit over the top. Please see:
http://www.mvps.org/access/netiquette.htm

Vladimir said:
Hello,

Does MS Access support subj like that SELECT * FROM
[ODBC;DSN=<system_dsn_or_smth_else>;]?
What is the syntax of SQL statement to use with OLE DB (for example,
Click to expand...
Jet
OLE
DB Provider)?

Regards,
Vladimir.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top