Connect To SQL Database

[Guest]

Hi, I have this in an Excel book and I want to know if I can use it to open
the connection in Access and then be able to see the tables. (XXX is where
there is actual information.)

strConnect = "Provider='SQLOLEDB';Data Source='XXX';Initial
Catalog='XXX';User Id=XXX;Password=XXX;"

 

[Jim Polec]

what is this

 

[Douglas J. Steele]

Not really.

Your best bet is to go through File | Get External Data | Link Tables, set
the Files of Type combo (bottom left of the dialog) to ODBC databases(),
then select the DSN from the dialog that appears. That'll let you see all of
the tables, and to create linked tables pointing to them.

Once you've done that, you can use the code in
http://www.accessmvp.com/djsteele/DSNLessLinks.html to convert your linked
tables to DSN-less, so that you don't have to distribute the DSN to other
users.

(Note that you must use an ODBC connection string, not an OleDB one, such as
you have, from Access)