Connecting to SQL Sever From Access Forms Using OleDb

Question

I am working on a project to migrate a Microsoft access backend to a SQL Server backend and my customer insists on using Access to create forms to do any Insert, updates, and deletes. The problem is that he has SQL data tools on his machine and others on his team (without data tools) need to be able to use the forms too. I thought the best way to go about this is to use and OleDb connection through access to connect to the forms so his team can use it and my access knowledge is very limited. All I have so far is what is below

“Driver=SQLOLEDB;Data Source=SomeServer;Initial Catalog=SomeDatabase;Integrated Security=SSPI”

I know the user has creds on the SQL box and can connect through ODBC. We are just having trouble getting the OleDb to work. Any help on how to deploy an OleDB connection in an access form for be greatly appreciated.

Answer 1

Here is the connection we used for SQL Server. It supported using either Trusted Connection, or SQL Server authentication.

Call GetConnection(gvstr_SQLServer_Name, gvstr_SQLServer_Database, _
        cnConn, adUseServer, False, False)

    If GetConnection(gvstr_SQLServer_Name, gvstr_SQLServer_Database, _
            gv_DBS_SQLServer, adUseServer, True, False) = True Then
        gvbln_UsingSQLServer = True
        DoCmd.Hourglass True
        ReLink_SQLSERVER_Tables
    Else
        gvbln_UsingSQLServer = False
    End If



Public Function GetConnection(ByVal strDSN As String, _
    ByVal strDatabase As String, _
    ByRef cnLocal As ADODB.Connection, _
    ByVal CursorLoc As CursorLocationEnum, _
    ByVal UsePassword As Boolean, _
    ByVal blnTrusted As Boolean) As Boolean

Dim intWaitDuration     As Integer
Dim strConnectString    As String
Dim strDisplay          As String

Const CURRENT_METHOD As String = "GetConnection"
On Error GoTo ERROR_HANDLER
GetConnection = False
intWaitDuration = 60     
Retry_Connection:
If cnLocal Is Nothing Then Set cnLocal = New ADODB.Connection
If cnLocal.State = adStateOpen Then
        Write_To_Log "Connection already open -- -will not reopen!!"
    GetConnection = True
    GoTo Proc_Exit
End If

With cnLocal
    Debug.Print "Use TRUSTED CONNECTION (ABOVE)"
    If gvstr_Workstation = "my-pc" Then
        strConnectString = "Driver={SQL Server};" & _
                                "Server=" & strDSN & ";" & _
                                "Database=" & strDatabase & ";" & _
                                "Trusted_Connection=yes"
    Else
        If blnTrusted = True Then
            strConnectString = "Driver={SQL Server};" & _
                                "Server=" & strDSN & ";" & _
                                "Database=" & strDatabase & ";" & _
                                "Trusted_Connection=yes"
        Else
             strConnectString = "Driver={SQL Server};" & _
                                "Server=" & strDSN & ";" & _
                                "Database=" & strDatabase & ";" & _
                                "User Id=Sql_myuid;Password=ppppp"

            strDisplay = "Driver={SQL Server};" & _
                                "Server=" & strDSN & ";" & _
                                "Database=" & strDatabase & ";" & _
                                "User Id=S*********t;Password=****************"

        End If
    End If
     Write_To_Log "Will use Conn String: " & strDisplay
    .ConnectionString = strConnectString
    .CursorLocation = CursorLoc
    .Open
End With
GetConnection = True
Proc_Exit:
Exit Function

ERROR_HANDLER:
Debug.Print Err.Number & vbCrLf & Err.Description

Err.Source = "Module_Utilities: GetConnection  at Line: " & Erl
DocAndShowError
Resume Proc_Exit
Resume Next
Resume
End Function