Microsoft Access Runtime and ODBC links

Posted on 2011-04-25
Last Modified: 2012-05-11
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

Question by:Jorgen
    LVL 13

    Expert Comment

    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

    LVL 4

    Author Comment

    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?
    LVL 13

    Accepted Solution

    Sorry for the late reply.

    Change this:



    SERVER= (or whatever your ip is)
    LVL 4

    Author Closing Comment

    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?


    LVL 13

    Expert Comment


    Not sure if you've see this article.

    Good luck.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now