INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!

*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.

Jobs

ADO Connection String SQLOLEDB

ADO Connection String SQLOLEDB

(OP)
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...

CODE

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

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

CODE

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

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" 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: ADO Connection String SQLOLEDB

(OP)
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] " & _
        "ORDER BY [DESCRIPTION]"
    Set cn = New ADODB.Connection
    strConn = GetOIConnectionString()  'function to get the connection String
    Set rs = New ADODB.Recordset
    cn.ConnectionString = strConn
    cn.Open
    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
            .MoveNext
        Loop
        .Close
    End With
End Sub 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: ADO Connection String SQLOLEDB

(OP)
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 & _
           strPwd
End Function 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: ADO Connection String SQLOLEDB

(OP)
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!

Resources

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