Link Tables Via Code

[KurtRos]

Hey everyone I'm new here but pretty familiar with Access etc. I am extrememl;y stumped on this and I have never attempted this so any answers and details would be helpful. As we all know this is a learn new things as you develop type of application or at least for me.

I have a database that exists in one building and is functionnig fine using linked tables (File/Get External Data/Link Tables) but I now need to make this same application work for multiple locations which each have its own tables that are seperate from each other location. I was thinking a Main form that opens with radio buttons and based on the one you select would determine which tables it would be linked to when it opens. I figured Radio1 be SEDC, Radio2 be SWDC.

I have a database I have setup as a storage database for just the tables for each location that are on different servers in each location to increase traffic speeds.

Example:

SEDC (RadioButton1)
SEDC Database is located on a erver with 50 different tables in it "//sedc1/data/shared/warehouse/SEDC Warehouse.mdb"

SWDC (RadioButton2)
SWDC Database is located on server with same tables in it but different data for that location "//swdc1/data/shared/warehouse/SWDC Warehouse.mdb"

So on So on...

 

[Dennisk]

I developed a system that did that, If the FE could not find the backend it would prompt for the location of the BE then automatically link the table from that BE. Works like a dream.

 

[KurtRos]

Sounds great but here is the issue we are talking about 50+ users that make $8.00 hr. that probably arent going ot have no clue where the backend is lol...

So I would like ot try and make it simple and let them select the radio button based on the one they want to work with...

Any idea how to do that...

 

[Dennisk]

If you where the tables are going to be then its as simple as the following code to relink
just remove the code that handles a localDb

Private Function RefreshLinks(strFileName As String) As Boolean
' Refresh links to the supplied database. Return True if successful.
On Error GoTo Err_handler
Dim lngCount As Long
Dim varReturn As Variant

Dim dbs As Database
Dim tdf As TableDef

varReturn = SysCmd(acSysCmdInitMeter, "Linking ....Please Wait", 20)

' Loop through all tables in the database.
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs

' If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
' Ignore Local Table
If Right$(tdf.Connect, 9) <> "Local.MDB" Then
lngCount = lngCount + 1
varReturn = SysCmd(acSysCmdUpdateMeter, lngCount)
tdf.Connect = ";DATABASE=" & strFileName
Err = 0
On Error Resume Next
tdf.RefreshLink ' Relink the table.
End If
If Err <> 0 Then
RefreshLinks = False
Exit Function
End If
End If
Next tdf
varReturn = SysCmd(acSysCmdClearStatus)
RefreshLinks = True ' Relinking complete.

Exit_Handler:
dbs.Close
Set dbs = Nothing
Set tdf = Nothing
Exit Function

Err_handler:
MsgBox Err & " " & Err.Description
Resume Exit_Handler

End Function