• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

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

Jørgen
0
Jorgen
Asked:
Jorgen
  • 3
  • 2
1 Solution
 
LucasCommented:
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

0
 
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?
0
 
LucasCommented:
Sorry for the late reply.

Change this:

SERVER=yourSQLservername

to

SERVER=192.168.1.255 (or whatever your ip is)
0
 
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?

regards

Jørgen
0
 
LucasCommented:
Jorgen,

Not sure if you've see this article.

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

Good luck.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now