ADO Connection String SQLOLEDB

[lameid]

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;" & _
    "Trusted_Connection=yes"

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

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.

 

[dhookom]

I typically use ODBC like:

[COLOR=#4E9A06]    'set the variables[/color]
    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

 

[lameid]

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.

 

[dhookom]

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

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()  [COLOR=#4E9A06]'function to get the connection String[/color]
    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

 

[lameid]

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

 

[dhookom]

Here is my function for your reference:

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

 

[lameid]

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.