We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Microsoft Access Runtime and ODBC links

Jorgen
Jorgen asked
on
Medium Priority
400 Views
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

Jørgen
Comment
Watch Question

LucasMS Dynamics Developer
CERTIFIED EXPERT

Commented:
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
            tb.RefreshLink
           'Debug.Print "Refreshed ODBC table " & tb.Name
        End If
    Next tb
    Set db = Nothing
End Sub

Open in new window

JorgenConsultant

Author

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?
MS Dynamics Developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
JorgenConsultant

Author

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?

regards

Jørgen
LucasMS Dynamics Developer
CERTIFIED EXPERT

Commented:
Jorgen,

Not sure if you've see this article.

http://www.granite.ab.ca/access/terminalserver.htm

Good luck.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.