CODEWITHSUNDEEP
sql-servervbaexcel
Adit2789
Adit2789

Reputation: 149

SQL to VBA: Error importing table from SQL into excel

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

Upvotes: 0

Views: 319

Answers (1)

David Pedack
David Pedack

Reputation: 492

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"

Upvotes: 1

Related Questions