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
2 comments:
Good Post. Thanks
Hi. Any idea why I would get
Cannot open database "Database1" requested by the login. The login failed.
I'm using Windows Authentication. Thanks !
Post a Comment