VBA Excel for SqlServer

Question

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

Excel file users can run XP, Vista, Win7, and I want to prevent driver installation as much as possible.

I understand that XP uses MDAC, while Vista / Win7 uses DAC. Does this mean that the link to MDAC 2.8 will not work on a Vista machine and vice versa?

Will my VBA code work on both unless I add a link and use late binding, for example. CreateObject ("ADODB.Connection")?

Answer 1

I did this using MS ADO 2.0 (the oldest version found on my workstation added it as a reference). It works on all the PCs I've tried, you only need to enable macros (which was not 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 needed with field rsData.MoveNext Loop