Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


ADO Connection String SQLOLEDB

ADO Connection String SQLOLEDB

ADO Connection String SQLOLEDB

The Native SQL driver is not installed on most machines here so I was string to switch to the native SQLOLEDB provider but some nuance does not work...

My connection string that did work looked like...


strConn = "Provider=SQLNCLI11;" & _
    "Server=ServerName\INSTANCENAME,PortNumber; Database=DBNAME;" & _

This does not work... Opening the connection I am getting "Invalid Connection string attribute"


strConn = "Provider=SQLOLEDB;" & _
              "Source=ServerName\INSTANCENAME,PortNumber;" & _
              "Initial Catalog=DBNAME;" & _

Any idea where I am going wrong? ServerName is alphanumeric as is the instance and the PortNumber is of course numeric.

RE: ADO Connection String SQLOLEDB

I typically use ODBC like:

CODE --> vba

    'set the variables
    strServer = "ServerName\INSTANCENAME,PortNumber"
    strDatabase = "DBNAME"
    strConnect = "ODBC;Driver={SQL Server};Server=" & strServer & _
        ";Database=" & strDatabase & ";Trusted_Connection=Yes" 

Hook'D on Access
MS Access MVP 2001-2016

RE: ADO Connection String SQLOLEDB

I will give that another try but I tried similar. I realize now I need to specify I am using ADO to open a connection and then eventually will open a stored sproc as a recordset.

RE: ADO Connection String SQLOLEDB

Here is code (actually from Excel) to open an ADODB recordset to fill a combo box on a form. It might help you some.

CODE --> vba

Sub UpdateSites()
    Dim strSQLSites As String, strConn As String, strSQL As String, intFac As Integer, intFacCount As Integer, strFacCode As String
    Dim intRow As Integer, intRows As Integer
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    ClearListBox "cboSite"
    strSQLSites = "SELECT [ID] " & _
        ", [Description] + ' ' + [Code] As Title " & _
        "FROM [REF_Plants] " & _
    Set cn = New ADODB.Connection
    strConn = GetOIConnectionString()  'function to get the connection String
    Set rs = New ADODB.Recordset
    cn.ConnectionString = strConn
    With rs
        .ActiveConnection = cn
        .Open strSQLSites
        Do While Not .EOF
            Me.cboSite.AddItem .Fields("ID")
            Me.cboSite.List(intRow, 1) = .Fields("Title")
            intRow = intRow + 1
    End With
End Sub 

Hook'D on Access
MS Access MVP 2001-2016

RE: ADO Connection String SQLOLEDB

The error I get...

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

Which seems weird to me as that looks like a DSN-Less connection string to me... and if I use a file DSN to link a table, it more or less matches it. For grins I made it match the tabledef.connect property and no luck (rearrange a couple elements order and add an App= attribute).

RE: ADO Connection String SQLOLEDB

Here is my function for your reference:

CODE --> vba

Public Function GetOIConnectionString() As String
    Dim strServer As String
    Dim strUID As String
    Dim strPwd As String
    strServer = "Server=MyServer\MyInstance,1433;"
    strUID = "Uid=MyLogin;"
    strPwd = "Pwd=MyPassword"
    GetOIConnectionString = "Driver={SQL Server};" & _
           strServer & _
           "Database=MyDataBase;" & _
           strUID & _
End Function 

Hook'D on Access
MS Access MVP 2001-2016

RE: ADO Connection String SQLOLEDB

I ended up just requesting the native client drivers on the target machine. It handles some datatype conversions better anyway, like all dates are dates.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close