Creating An OLEDB Connection From Excel To SQL

Question

I am looking to run SQL queries using VBA code in an Excel file. It may sound like a bad way to do things, but the purpose of this is to support legacy functionality on a project I'm working on.

I figured out how to create an ODBC connection, but it requires several steps which may be troublesome to implement on many computers, so I'm looking into the possibility of using OLEDB to get the job done.

My question is how to go about setting things up so I can run SQL queries in Excel using VBA.

Answer 1

Both ODBC and OLEDB require data providers installed on the target machine. If by "several steps which may be troublesome" you meant installing these, there'll be a little difference.

Provided your target operating systems don't include Windows versions below XP, and your target database is in this list of databases supported by MDAC by default, then you won't have to do anything special. You'd create an ADODB.Connection object and open it using one of the connection strings.