Microsoft Access Runtime and ODBC links

Hello Everybody,

I have an Access database, where I have ODBC links to an SQL Database. Parts of this is working a lot like the task database template, that microsoft provides us with. I have a number of salespeople, that have a runtime version of Access with the application. For some reason the ODBC links does not work in the runtime version of the database. Can anybody help me to make this work?

best regards

Who is Participating?
LucasMS Dynamics DeveloperCommented:
Sorry for the late reply.

Change this:



SERVER= (or whatever your ip is)
LucasMS Dynamics DeveloperCommented:
Create a module, call it lets say "modDb".

Put the attached code in it, change the values to match yours of course.

Then create a macro, and in the macro:

Action: RunCode
Arguments: RefreshODBCLinks ()

Save the macro as "AutoExec"

Then try it in the run time environment.

It should refresh your ODBC tables.

Public Function RefreshODBCLinks()
    Dim db As DAO.Database
    Dim tb As DAO.TableDef
    Dim newconnectionstring As String
    newconnectionstring = "ODBC;DRIVER=SQL Server;SERVER=yourSQLservername;UID=yourdblogin;PWD=yourdbpass;DATABASE=yourdbname"
    Set db = CurrentDb
    For Each tb In db.TableDefs
        If Left(tb.Connect, 4) = "ODBC" Then
            tb.Connect = newconnectionstring
           'Debug.Print "Refreshed ODBC table " & tb.Name
        End If
    Next tb
    Set db = Nothing
End Sub

Open in new window

JorgenAuthor Commented:
I have now tested thoroughly in the last week and it works fine as long as I have my runtime working directly on the server, but in this case we will lock in to an IP adress, and for some reason nothing happens there. Do you have any idea of why that happens?
JorgenAuthor Commented:
Hi Lucas,

for some reason I never received a message, that you answered again.

But I am very satisfied.

Do you have any knowledge of doing the same thing remote access through a terminal server as well?


LucasMS Dynamics DeveloperCommented:

Not sure if you've see this article.

Good luck.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.