asked on

Change table links in VBA

Hello Experts,

I would like to link tables to a backend database "on the fly" when a user selects an option or pushes a button etc.

How would I go about deleting the current table links and re-linking them to a new network path? The reason I am doing this is that I would like to have 1 tool that can work in any region, whereas I currently have 3 tools, 1 for each region.

Thanks in advance for your help!

phileoca

what we do at our company is use Map Network drives. We picked S, so all of our links go to the S drive, so no matter where we go, to connect, to the backend (which we have 2 duplicates at 2 satellite locations, 1 one via the web server) connecting to the S drive connects it to the backend

However, you can Dynamically create links, however you'll have to specify the path and name before hand... which really doesn't make it "on the fly".

<>< KT

sph3rion

ASKER

I can specify the path and name in VBA right?

The problem is that someone set up 3 different databases with identical tables (names and format) with autonumbers and the like as primary and foreign keys, it's not a design I would have come up with but I must maintain it. So since I can't merge any of the data into one database, I need the tool to function as if it were.

I know it can be done as I have seen code that looks like it can be made to work in this way but I need some help with it.

nico5038

Personally I use this function to relink to the _be.mdb in the same directory as the frontend:

Function fncRelink()
'functie om de gelinkte tabellen te verversen naar de "_be" database
'Hierbij wordt aangenomen dat de "_be" database in dezelfde map staat
'als de frontend

Dim td As TableDef

For Each td In CurrentDb.TableDefs
If Len(td.Connect) > 0 Then
td.Connect = ";DATABASE=" & Left(CurrentDb.Name, Len(CurrentDb.Name) - 4) & "_be.mdb"
td.RefreshLink
End If
Next

End Function

But I guess it gives you an indication :-)

Nic;o)

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sph3rion

ASKER

Thanks very much!

I ran into some work but I will try out this code in a few hours