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
Posts
Good Post. Thanks
ReplyDeleteHi. Any idea why I would get
ReplyDeleteCannot open database "Database1" requested by the login. The login failed.
I'm using Windows Authentication. Thanks !
Check database name....I think that is the problem.....
ReplyDeleteTHANK YOU sooooooooooooooo much
ReplyDeletethat works like a charm
hi all,
ReplyDeletewhen i run this code, i would get "run time error -2147467259
automation error unspecified error"
i used debug, and the error accord in oCon.Open
please help me.
Please check if the following helps you http://support.microsoft.com/kb/328306
ReplyDeletehttp://support.microsoft.com/kb/318432/
hi shasur,
DeleteThank u for your reply ,i have checked the link u posted. i didnt find any helpfully info
Iam getting Run time error
ReplyDeleteAutomation Error,
Unspecified Error
Thank you for being awesome!
ReplyDeleteIf anyone want to use the value in your code instead of printing it to your sheet use:
oRS.MoveFirst
Debug.Print oRS.Fields(0)
Instead of:
Range("A1").CopyFromRecordset oRS