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