using remote MS Access database which connects to remote SQL server

Question

We have a Microsoft Access database + application (on Server A) which connects to a remote SQL server (Server B) using System DSN ODBC connection (on Server A) to the SQL database server.

The users are open this Access database remotely as it is on a shared location on the server A. They still have to create a local ODBC connection on their computers to connect to Server B.

Is there anyway that they can access the Access database and not have to create a local ODBC connection?

thanks in advance

Answer 1

You can eliminate the need for the local DSN on each machine by using a bit of code in the Access database. You do of course still need the appropriate ODBC driver installed but I expect the one you need is probably standard on Windows anyway.

A local DSN will be required to initially create the database.

The following in what I use for dynamically relinking to a MySQL database, so you will need to edit it accordingly. The code is called from the AutoExec macro or can be run manually or from a form.

Note that this is not my code but I've been using it for a very long time and don't recall where I originally got it. All I've done is edit it to suit my requirements.

Option Compare Database

Public Function ReLinkTables()

Dim dbPUBS As DAO.Database
Dim tdfPUBS As DAO.TableDef
Dim strTable As String
Dim strConnect As String
Dim InFile As Integer


' Set the following variables tosuit your DB connection
Dim Server As String
Dim Database As String
Dim User As String
Dim Password As String

On Error GoTo 0
Set dbPUBS = Nothing
Set dbPUBS = CurrentDb
strConnect = "DRIVER={MySQL ODBC 3.51 Driver};" _
    & "SERVER=" & Server & ";" _
    & "DATABASE=" & Database & ";" _
    & "UID=" & User & ";" _
    & "PWD=" & Password & ";" _
    & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384

' Refresh Access linked tables
For Each tdfPUBS In dbPUBS.tabledefs
    ' Only attempt to refresh link on tables that already
    ' have a connect string (linked tables only)
    If Len(tdfPUBS.Connect) > 0 Then
        strTable = tdfPUBS.Name

        ' Set the tables connection string
        tdfPUBS.Connect = strConnect

        ' and refresh the link
        tdfPUBS.RefreshLink
    End If
Next

' Refresh Connect String for all Pass-Through Queries
'strMsg = "Refreshing links for all Pass Through Queries."
'DoCmd.Echo True, strMsg
'For Each qdfPUBS In dbPUBS.QueryDefs
    'If Len(tdfPUBS.Connect) > 0 Then
        'qdfPUBS.Connect = strConnect
    'End If
'Next

End Function

Answer 2

Don't use a DSN when you define the link to the SQL Server. Instead specify the actual connection string (should be an option, but I haven't worked with Access for years).