ADODB.Connection.Open error "[Microsoft][ODBC Driver Manager] Data source name..."

[SeanDamnit]

I'm trying to familiarize myself with VBA and SQL, here's what I have so far based on some examples I've seen out there:

Sub GetDataFromADO()
    'Declare variables'
        Set objMyConn = New ADODB.Connection
        Set objMyRecordset = New ADODB.Recordset
        Dim strSQL As String

    'Open Connection'
        objMyConn.ConnectionString = _
           "OLEDB;Provider=SQLOLEDB;Password=xxxx;User ID=xxxx;Initial Catalog=master;Data Source=abc.com;"
        objMyConn.Open

    'Set and Excecute SQL Command'
        strSQL = _
        "SELECT iQclerk_Stores.Abbreviation, LanguageTranslations_2.FieldText AS 'Location' " & _
        "FROM ArchTelecom.dbo.iQclerk_Stores iQclerk_Stores, ArchTelecom.dbo.LanguageTranslations LanguageTranslations_2 " & _
        "WHERE iQclerk_Stores.StoreNameID = LanguageTranslations_2.ReferenceID AND ((iQclerk_Stores.Abbreviation='ALBN1'))"

    'Open Recordset'
        Set objMyRecordset.ActiveConnection = objMyConn
        objMyRecordset.Open strSQL

    'Copy Data to Excel'
        ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)

End Sub

However, getting the following error on the "objMyConn.Open" line:

Run-time error '-2147467259 (80004005)':

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Can anyone assist?

Thanks!

 

[Kyle123]

That's caused by an error in your connection string, are you sure it's correct?

You wouldn't normally prefix a connection string like that with OLEDB; try:

        objMyConn.ConnectionString = _
           "Provider=SQLOLEDB;Password=xxxx;User ID=xxxx;Initial Catalog=master;Data Source=abc.com;"

 

[SeanDamnit]

That's caused by an error in your connection string, are you sure it's correct?

You wouldn't normally prefix a connection string like that with OLEDB; try:

        objMyConn.ConnectionString = _
           "Provider=SQLOLEDB;Password=xxxx;User ID=xxxx;Initial Catalog=master;Data Source=abc.com;"

That worked. The OLEDB was there because that was what was recorded when I set up the connection string.

Thanks for the help!