SQL to VBA: Error importing table from SQL into excel

Question

In the code below, I keep getting an error message saying:

"Runtime Error '-2147467259 (80004005)': Automation Error Unspecified Error"

The error pops up when I try and open the connection. All I want to do is import data from a local SQL DB into excel.

I don't know if it matters, but I am using SQL server 2014 express.

Sub connect()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strConn As String
Dim connstr As String
Dim strSRV As String
Dim strDB As String
Dim sql_login As String


sqlquery = "SELECT top 10 * FROM [Source Contacts];"


connstr = "Driver={SQL Server native Client 11.0};Server=(LocalDB)\v11.0;AttachDBFileName=C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\SIT23DL_Validation.mdf;Database=SIT23DL_Validation;Trusted_Connection=Yes"

'Create the Connection and Recordset objects
Set conn = New ADODB.Connection
conn.ConnectionString = connstr
conn.Open


Set rs = conn.Execute(sqlquery)

If Not rs.EOF Then
    'Transfer result
    Sheets(1).Range("A1").CopyFromRecordset rs

'Close Recordset
rs.Close

Else
    MsgBox "Error: No records returned.", vbCritical
End If

'Clean up
If CBool(conn.State And adStateopen) Then conn.Close
Set conn = Nothing
Set rs = Nothing
End Sub

I based that on a thread I found on msdn regarding how to import sql tables from a local db. (social.msdn.microsoft.com/Forums/en-US/…) — Adit2789
– Adit2789, Commented May 23, 2017 at 19:44
Maybe you need If rs Is Nothing Then as suggested in this post — Jacob H
– Jacob H, Commented May 23, 2017 at 19:52

David Pedack · Accepted Answer · 2017-05-23 21:27:12Z

1

Agreed with the comments, most likely culprit is the connection string. Try connecting to your SQL instance using 'Microsoft SQL Server Management Studio' and attach the database through that interface (not through the connection string). If all that works, you can try the following (you may need to replace localhost with your actual machine name):

connstr  = "Provider=SQLOLEDB;Data Source='localhost\v11.0';Initial Catalog='SIT23DL_Validation';Integrated Security=SSPI;Trusted_Connection=Yes"

answered May 23, 2017 at 21:27

David Pedack

4922 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adit2789 Over a year ago

Thanks a lot! Worked like a charm.

Collectives™ on Stack Overflow

SQL to VBA: Error importing table from SQL into excel

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related