Connecting a Multi User Access Front End to SQL Server Back End

Question

Thank you for your help as always!

I have built an Access database that we intend to distribute to ~100 end users. I am currently running a pilot program for 5 users. I have migrated the tables to SQL 2014 Server but currently the back end of the pilot program is in a shared network folder (location unknown to them). I distributed the Access front end (accde file type) by saving the file on that shared Network and instructing the end users to save a copy to their desktop. The pilot is working fine, if not a little slow.

The issue that I am having is this:

I am able to set up an ODBC Connection on my machine and am able to connect to the SQL Server backend through that connection. However, I am not sure how to get my end users Access to the tables on the server. Do I need to create a user name for each user on the server and give them read and write access? Do I also need to create an ODBC connection on each machine that we plan to install the Front End on? I would prefer to not have to create an ODBC connection on each machine, is it possible to work around this? Thank you!

Access Version: 2013 SQL: 2014 Working on SSMS 2014

-Charlie

Answer 1

A few things:

When you create a linked table, simply use the ribbon import and link – and then ODBC database. Just choose a file DSN. The reason for this is that access by DEFAULT will use a DSN-less connection. In simple terms, this means when you link the tables, then you can distribute your applcatation to each workstation and there no need to setup a SYSTEM/Machine DSN.

So just keep in mind that use the default file DSN – once Access creates the link to SQL server, then such links are DSN-less, and you don’t need any setup on each workstation.

As for creating users on SQL server? Well, you likely don’t need to unless you want some kind of special security for each user. If you using SQL logon, then MAKE sure that during the above linking process you “check” the save password option. Once again, since by default the linked tables are DSN-less, then every user will in fact be using the one and same SQL User/password and thus this will be transparent to each user (they will not have to log on).

If you using windows authentication for SQL logons, then security is setup with the windows system and not SQL server. In this case then each users windows logon will be used to control (permit) use of the SQL server. If you not using a domain controller, then you be using SQL logons, and likely just the one logon that you are using will suffice. Often even in a corporate environment because I don’t want to call up the IT admin folks for each logon and permissions to SQL server, then I still OFTEN choose SQL logons. Thus “once” the IT admin folks give me enough rights to the SQL server, then I am free to create my own logons, or just use the “one same” logon for everyone and thus don’t have to waste time bothering the IT folks.

A few additional final points: Ignore suggesting to use all kinds of ADO and VBA code and connection strings etc. – they are not required. In fact in most cases you want to AVOID ADO code in your application. And oleDB is being depreciated for SQL server (which ADO tends to rely on).

You STILL as a matter of good deployment want to place the front end program you have on each workstation. Just like you install word on each workstation, or your accounting packages, now that YOU ARE developing software, then you install your software on each workstation like the IT industry done for the last 30 years. You can certainly share data on a shared folder, but you install the actual application (word, Excel, or in this case YOUR applcatation on EACH workstation. And you should compile the accDB to an accDE before any deployment.

So you don’t really need any special code on start up to “connect” or “link” to SQL server if your deployment to such users is on your same network. If you are developer or consultant “offsite”, then you likely need to add some code on start up to re-link to THEIR sql server that no doubt would be different than the one your developing with offsite. So some ability to re-link to a “different” SQL server then the one your are developing with would be required if you not able to develop on site, or that the SQL server you working with is a “copy” or “test” version of the Actual production SQL server being used.

Answer 2

You do not need an ODBC connection on every machine. You can create the connection by using ODBC connection strings via DoCmd.TransferDatabase. This is persistent, so you can just execute the connection/updates on your development copy of the front end and the connection will exist and persist in any end user copies of that that you make.

You do have to deal with the authentication issue. Depending on your security situation you can create a single "database user" and include the username and password to the above connection. If you are in a AD environment you can use that for authentication. Or you create individual SQL accounts for each user or user group. No matter what you do the users will be able to see and edit the tables if Access is to have read/write permission to them. You can secure individual tables by using views to link them to Access instead of the tables themselves.

I would advise against having users make a local copy of the front end. That makes it a nightmare to make sure your users are using the latest version of your front end. Encourage them to make a shortcut instead. Unless you have per-user local temp tables or code that dynamically edits forms/reports there is not much reason not to have everybody using the same front end file. Even in that case create a deployment batch file that creates/overwrites the front end copy for each user.

Sample code that links to SQL server database:

Public Sub LoadDatabase()

Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim d As DAO.Recordset
Set d = MyDB.OpenRecordset("SELECT TableName FROM tblLinkTables")
d.MoveFirst
Do While Not d.EOF
    DoCmd.DeleteObject acTable, d!TableName
    DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server};Server=MYSQLSERVERNAME;Database=MyDatabaseName;Uid=DatabaseUser;Pwd=DatabaseUserPassword", acTable, d!TableName, d!TableName, False, True
    d.MoveNext
  Loop
d.Close
Set d = Nothing

End Sub

where the list of tables to link exists on a local table called tblLinkTables with a single field TableName for each table to be linked. This is a very simplified version. The one I actually use has the capability to have different local table names than the SQL tables and also link views and create primary keys (optionally) for them.

Answer 3

The following is one example of how you can obtain a connection to SQL Server. It supports using either a Trusted Connection (where the windows user is authorized access, or you can specify a sqlserver defined userid and password.

' The following are some of the parameters the connection will use.
Global gv_DBS_SQLServer                 As ADODB.Connection
Global gvstr_SQLServer_Name             As String
Global gv_SQLServer_DSN                 As String
Global gvstr_SQLServer_Database         As String

'Call the GetConnection Function
' Pass Name of Server; Database Name; Connection Variable; adUseServer; True if using Password; False if not using a Trusted Connection;
' Sample
    If GetConnection(gvstr_SQLServer_Name, gvstr_SQLServer_Database, _
            gv_DBS_SQLServer, adUseServer, True, False) = False Then
        MsgBox "Unable to connect to SQL Server", vbOKOnly, "No Connection"
    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 strConnectString    As String
Dim strDisplay          As String

    On Error GoTo ERROR_HANDLER
    GetConnection = False
Retry_Connection:
    If cnLocal Is Nothing Then Set cnLocal = New ADODB.Connection
    If cnLocal.State = adStateOpen Then
        Debug.Print "Connection already open -- -will not reopen!!"
        GetConnection = True
        GoTo Proc_Exit
    End If
    With cnLocal
        Debug.Print "Use TRUSTED CONNECTION (ABOVE)"
        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=UUUUUUU;Password=" & DecryptString("PPPPPPPP") & ""

            strDisplay = "Driver={SQL Server};" & _
                         "Server=" & strDSN & ";" & _
                         "Database=" & strDatabase & ";" & _
                         "User Id=UUUUUU;Password=PPPPPPP"

        End If

         Debug.Print "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_Connect: " 
    DocAndShowError
    Resume Proc_Exit
    Resume Next
    Resume
End Function