Connection string for VBA > ADO > (not necessarily Access)

[Isaac]

In Excel or Access, if I am using ADODB to connect using VBA to a SQL server using trusted connection windows auth, what would be the default driver?

This code gives no default driver specified:

 

[pbaldy]

I don't use ADO much but here's a connection string I used before successfully:

Public Const dbConnectionString As String = "DRIVER=SQL Server Native Client 10.0;SERVER=sqlsrv01..."

So I'd expect whatever ODBC driver you're using.

 

[sonic8]

So I'd expect whatever ODBC driver you're using.

ADO does not use ODBC. It uses OLEDB which is a different protocol/API than ODBC.
You can also use ODBC, but it will be wrapped by the "OLE DB Provider for ODBC Drivers" as an intermediary to translate between the two different APIs.

 

[ahmedjamalaboelez]

In Excel or Access, if I am using ADODB to connect using VBA to a SQL server using trusted connection windows auth, what would be the default driver?

This code gives no default driver specified:

View attachment 109434

this code work fine with me
insert following code in public module
don't forget to add reference
Microsoft Active X Data Object Library

'$<<-- Open Connection-->>
Public Function ServerConOpen(ConServer As String, ConUser As String, ConPassword As String, ConDatabase As String) As Boolean
    On Error Resume Next
    Set cnx = New ADODB.Connection
    cnx.ConnectionString = "Driver={SQL Server};Server=" & ConServer & ";uid=" & ConUser & ";pwd=" & ConPassword & ";Database=" & ConDatabase & ""
    cnx.ConnectionTimeout = 30
    cnx.Open
    ' select current connection stat
    Select Case cnx.State
        Case Is = 1
            ServerConOpen = True
        Case Else
            ServerConOpen = False
            DoCmd.CancelEvent
            MsgBox "Connection Error", vbCritical, "ERR. Connection"
            Exit Function
    End Select
End Function

'$<<-- Close Connection-->>
Public Sub ServerConClose()
    On Error Resume Next
    cnx.Close
    Set cnx = Nothing
End Sub

'$<<- Test Connection
Public Function TestConString()
    Call ServerConOpen("Your Server Name", _
         "Your User Name", _
         "Your User Password", _
         "You Database Name")
End Function

you can call sql server connection in your code as following to call parameterized Query

Public Function UpdateServer()
    If ServerConOpen = False Then
        DoCmd.CancelEvent
        Exit Function
    End If
    Dim cmd    As New ADODB.Command
    cmd.ActiveConnection = cnx
    cmd.CommandType = adCmdText
    Dim rs     As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM currency_info " & _
        "WHERE cur_id = '" & Me.frm_idx & "'")
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        With rs
            cmd.CommandText = "UPDATE currency_info SET " & _
                              "cur_name = '" & !cur_name & "', " & _
                              "cur_country = '" & UCase(!cur_country) & "', " & _
                              "cur_rate = " & CCur(!cur_rate) & ", " & _
                              "cur_status = '" & !cur_status & "', " & _
                              "cur_c_ver = " & CInt(!cur_c_ver + 1) & " " & _
                              "WHERE cur_id = '" & !cur_id & "'"
            cmd.Execute
        Else
        End If
        rs.Close
        Set rs = Nothing
        ServerConClose
End Function

if you want to call an stored procedure you can use following code sample

Public Function InsertServer()
    If ServerConOpen = False Then
        DoCmd.CancelEvent
        Exit Function
    End If
    Dim cmd    As New ADODB.Command
    cmd.ActiveConnection = cnx
    cmd.CommandType = adCmdStoredProc
    Dim rs     As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM currency_info WHERE cur_id ='" & Me.frm_idx & "'")
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        With rs
            cmd.CommandText = "currencydta_insert_new"
            cmd.Parameters.Append cmd.CreateParameter("@p_cur_id", adVarChar, adParamInput, 5, UCase(!cur_id))
            cmd.Parameters.Append cmd.CreateParameter("@p_cur_name", adVarChar, adParamInput, 50, !cur_name)
            cmd.Parameters.Append cmd.CreateParameter("@p_cur_country", adVarChar, adParamInput, 2, UCase(!cur_country))
            cmd.Parameters.Append cmd.CreateParameter("@p_cur_rate", adCurrency, adParamInput, , CCur(!cur_rate))
            cmd.Parameters.Append cmd.CreateParameter("@p_cur_status", adVarChar, adParamInput, 10, !cur_status)
            cmd.Execute
        Else
        End If
        rs.Close
        Set rs = Nothing
        ServerConClose
End Function

 

[Isaac]

Thanks all! Yes eventually solved it like:

sorry can't paste text from virtual work to home.

 

[cheekybuddha]

Here's some code you can use to see what ODBC drivers you have on your system:

'---------------------------------------------------------------------------------------
' Function  : GetODBCDrivers
' Author    : dm
' Date      : 03/11/2015
' Purpose   : Enumerate ODBC drivers
' Arguments : arrODBCDrivers  - Optional; array to store returned matches
'                               If omitted, results are simply print to Immediate Window
'             strFilter       - Optional; string to be found in driver name
'                               eg "MySQL", "SQL Server", "Excel"
'---------------------------------------------------------------------------------------
'
Public Function GetODBCDrivers(Optional arrODBCDrivers As Variant, _
                               Optional strFilter As String = "SQL") As Boolean
 
  Const ODBC_REG_PATH As String = "SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers"

  Dim arrDrivers() As Variant, arrValueTypes() As Variant, _
      i As Integer, vDriver As Variant

  Call EnumerateRegEntries(ODBC_REG_PATH, arrDrivers, arrValueTypes)
  If Not IsEmpty(arrDrivers) Then
    For Each vDriver In arrDrivers
      If (InStr(vDriver, strFilter)) Then
        If Not IsMissing(arrODBCDrivers) Then
          If i = 0 Then
            ReDim arrODBCDrivers(i)
          Else
            ReDim Preserve arrODBCDrivers(i)
          End If
          arrODBCDrivers(i) = vDriver
          i = i + 1
        Else
          Debug.Print vDriver
        End If
      End If
    Next
    GetODBCDrivers = (Err = 0)
  End If

End Function

You will also need code to read from the registry:

Public Function EnumerateRegEntries(strKeyPath As String, arrEntryNames As Variant, arrValueTypes As Variant) As Boolean
    
  Const HKEY_CLASSES_ROOT   As Long = &H80000000
  Const HKEY_CURRENT_USER   As Long = &H80000001
  Const HKEY_LOCAL_MACHINE  As Long = &H80000002
  Const HKEY_USERS          As Long = &H80000003
  Const HKEY_CURRENT_CONFIG As Long = &H80000005
  Const WINMGMTS_REG        As String = "winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv"

  Dim objReg As Object
 
  Set objReg = GetObject(WINMGMTS_REG)
  objReg.EnumValues HKEY_LOCAL_MACHINE, strKeyPath, arrEntryNames, arrValueTypes

End Function

You pass an array to the function to get the results:

Function TestIt() As Boolean

  Dim arr() As Variant, i As Integer
 
  If GetODBCDrivers(arr) Then
    For i = LBound(arr) To UBound(arr)
      Debug.Print arr(i)
    Next i
    TestIt = True
  End If
 
End Function

On my machine I get:

?TestIt
SQL Server
SQL Server Native Client 11.0
ODBC Driver 13 for SQL Server
ODBC Driver 17 for SQL Server
MySQL ODBC 8.0 ANSI Driver
MySQL ODBC 8.0 Unicode Driver
True

These are what you can use in your connection string (usually surrounded in handlebar brackets).

hth,
d