How to Connect SQL Express 2005 from VBA

Excel VBA retrieve data from SQL Server 2005

Here is a way to connect to SQL Express 2005 from Excel VBA

Sub Connect2SQLXpress() Dim oCon As ADODB.Connection Dim oRS As ADODB.Recordset Set oCon = New ADODB.Connection oCon.ConnectionString = "Driver={SQL Native Client};Server=.\SQLEXPRESS;Database=DB1; Trusted_Connection=yes;" oCon.Open Set oRS = New ADODB.Recordset oRS.ActiveConnection = oCon oRS.Source = "Select * From Table1" oRS.Open Range("A1").CopyFromRecordset oRS oRS.Close oCon.Close If Not oRS Is Nothing Then Set oRS = Nothing If Not oCon Is Nothing Then Set oCon = Nothing End Sub

The code uses ActiveX Data Objects (ADO). You need to add a reference to it as shown below