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. (https://social.msdn.microsoft.com/Forums/en-US/434232bf-3351-49b1-a80f-a788b02bf7ce/connecting-to-2012-localdb-from-vba?forum=sqlexpress) — Adit2789, May 23 '17 at 19:44
Maybe you need `If rs Is Nothing Then` as suggested in [this post](https://stackoverflow.com/questions/24688816/vba-excel-automation-error-unspecified-error) — Jacob H, May 23 '17 at 19:52

score 1 · Accepted Answer · answered May 23 '17 at 21:27

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"

Thanks a lot! Worked like a charm. – Adit2789 May 23 '17 at 21:36 — Adit2789, May 23 '17 at 21:36

SQL to VBA: Error importing table from SQL into excel

1 Answers1