VBA Excel To SqlServer

Question

What is the best way to write VBA code to connect to SQL Server 2005 from Excel?

The users of the excel file might run XP, Vista, Win7 and I want to prevent driver installation as much as possible.

My understanding is that XP uses MDAC while Vista/Win7 uses DAC. Does that mean that a reference to MDAC 2.8 will not work on a Vista machine and the other way around?

Will my VBA code work on both if I don't add a reference and use late binding, e.g. CreateObject("ADODB.Connection")?

Answer 1

I've done this using MS ADO 2.0 (the oldest version found on my workstation, added it as a reference). It's working on all the PC's I've tried it, you only have to enable macros (which wasn't good news at all)

Dim dbConnection As ADODB.Connection
Dim connStr As String

'Recordset variables
Dim rsData As ADODB.Recordset
Dim sql As String
connStr = "Provider=SQLOLEDB;" & _
          "Data Source=MyServer\MyInstance;" & _
          "Initial Catalog=MyDatabase;" & _
          "Integrated Security=SSPI;" & _
          "Application Name=MyExcelFile"

Set dbConnection = New ADODB.Connection
dbConnection.ConnectionString = connStr
dbConnection.Open

Set rsData = New ADODB.Recordset
rsData.Open "SELECT field FROM table", dbConnection
Dim field as String
Do While Not rsData.EOF
    'this is where each row will be processed
    field = rsData.Fields(0).Value
    'do what's needed with field
    rsData.MoveNext
Loop