Change the MS Access link tables connection

Question

0.00/5 (No votes)

See more:

I have an access file containing link tables referring to excel file. Each table refer to a specific sheet. The link tables have static connection.
Problem: How can I change the connection of the link tables because the location of the excel file is always changing.

Posted 16-Sep-10 23:39pm

ziadb86

Add a Solution

Comments

Sandeep Mewara 17-Sep-10 14:28pm

Update from OP:
In access I have created a module with the below function:
Public Sub RefreshODBCLinks(newConnectionString As String)
Dim db As DAO.Database
Dim tb As DAO.TableDef
Set db = CurrentDb
For Each tb In db.TableDefs
If Left(tb.Connect, 4) <> "" Then
tb.Connect = "Excel 12.0;DATABASE=" & newConnectionString & ";HDR=Yes"
tb.RefreshLink
Debug.Print "Refreshed ODBC table " & tb.Name
End If
Next tb
Set db = Nothing
End Sub

Then in the Intermidiate window i have call it and test it.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

aayu · Answer 1 · 2010-09-16T23:42:00

Solution 1

When changing the connection string of a linked table, you must then refresh the link. For example:
Code:
td.Connect = "MS Access;Database=C:\MyBackEnd.mdb;"
td.RefreshLink

The RefreshLink command commits the change to the connection string.
Reply With Quote
:rose::thumbsup:

Posted 16-Sep-10 23:42pm

aayu

Comments

ziadb86 17-Sep-10 5:43am

can u please insert an entire example because I'm lost? Can I use OleDb?

aayu 17-Sep-10 6:02am

you use your old database and then go throw the change of connection string and linked table then refresh the link

ziadb86 17-Sep-10 7:21am

if I opened the database, how can I retrieve the linked tables in order to change their connection string?