Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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

share|improve this answer
    
I have included this function by updating and then updated macro to run this function when we open the database. It is still asking for the ODBC connection. –  Manjot Mar 9 '11 at 22:05
    
@Manjot, that suggests either the syntax or driver might be incorrect for your connection. The driver name is particularly fussy and must match perfectly, including case sensitivity. –  John Gardeniers Mar 9 '11 at 22:27
add comment

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

share|improve this answer
    
Okie Thanks.... –  Manjot Mar 9 '11 at 3:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.