VBA to connect to Database using ODBC data source

I like to connect to a SQL 2005 database from my Excel VBA project.

I have setup a ODBC data source called MSSQLDEV that connects to the SQL 2005 database

How do I build the connection string using this ODBC data source to connect to the SQL 2005?
thanks.

The technique I used is by creating a udf file. Double click it and the Microsoft Data Link Wizard will start. Specify your connection and test it. Then open the udf file in say notepad to inspect the connection string.

If you want an easy way of connecting to your database (not only through ODBC) you can have a look at my SQL*XL tool. It integrates fully into Excel and allows full featured database access. There is a connection wizard in SQL*XL that will try many different connection strings. There is the possibility to inspect or edit the connection string. I hope you find it useful.

SQL*XL homepage
www.oraxcel.com/projects/sqlxl

Best regards, Gerrit-Jan Linker
Developer of SQL*XL

Originally Posted by qed59

I like to connect to a SQL 2005 database from my Excel VBA project.

I have setup a ODBC data source called MSSQLDEV that connects to the SQL 2005 database

How do I build the connection string using this ODBC data source to connect to the SQL 2005?
thanks.

The ODBC DSN you created should contain all the information needed to connect to your database. It would be used like this:

Please Login or Register  to view this content.