Problems with ADODB connection in VBA

Question

I have a VB.NET function that I want to connect to use to connect to a SQL Server database and return a recordset. I am new to VB and to SQL, so I hope it is something simple, but I am sure that my connection string and SQL query are correct, as they were checked by a teammate. I have a copy of the Exception message following the code

' Retrieve Record Set 
Private Function GetRecords() As ADODB.Recordset
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    With conn
        .ConnectionString = "Server=12.345.678.9;Database=db01;User Id=userid01;Password=passwordyword"
        .Open() ' Exception Thrown Here
        rs.Open("SELECT * FROM [Table] ;", "sql")
        rs.Close()
        .Close()
    End With
    Return rs
End Function

Exception Message:

An exception of type 'System.Runtime.InteropServices.COMException' occurred in ThisProject.dll but was not handled in user code
Additional information: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Any and all help would be greatly appreciated! Thanks!

In your connection string, you have to provide which driver are you using? for example, oledb, oracleoledb.. etc... — Thit Lwin Oo
– Thit Lwin Oo, Commented Mar 18, 2014 at 18:02

Thit Lwin Oo · Accepted Answer · 2014-03-18 18:10:11Z

1

Here you try.

m_sConnStr = "Provider='SQLOLEDB';Data Source='MySqlServer';" & _ 
 "Initial Catalog='Northwind';Integrated Security='SSPI';"

Connection String for VBA

answered Mar 18, 2014 at 18:10

Thit Lwin Oo

3,4283 gold badges22 silver badges23 bronze badges

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

Comments

Jimmy Smith · Accepted Answer · 2014-03-18 18:56:52Z

1

This should work. Note that the connection also needs passed in the rs.Open

Private Function GetRecords() As ADODB.Recordset
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    With conn
        .ConnectionString = "Driver={SQL Server};Server=12.345.678.9;Database=db01;Uid=userid01;Pwd=passwordyword;"
        .Open() ' Exception Thrown Here
        rs.Open("SELECT * FROM [Table] ;", conn)
        rs.Close()
        .Close()
    End With
    Return rs
End Function

answered Mar 18, 2014 at 18:56

Jimmy Smith

2,4511 gold badge16 silver badges19 bronze badges

Collectives™ on Stack Overflow

Problems with ADODB connection in VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related