Linking Tables Programatically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a front end/Back end application and I need a way of re-linking the
tables automatically on deployment. Thanks in advance.
 
You can do this in vba by using

DoCmd.TransferDatabase acLink, "Microsoft Access", Filename, acTable,
TableName, TableName

The filename is your database to be linked
And tablename is the name of the table which you want to refresh.

Tablenames you can control in 2 ways. Either you have a table if all the
tables to be linked or you open the database to link from by using
dao.database en you query the tables which are available from the msysobject.
Attention don;t link the system tables.

- Raoul
 
I have the same situation... Relink to my local pc copy for programming, then
I relink to the network back end when I deploy. I put a status count in the
status bar during relinking.
I run it from a macro...
runcode
ReLinkTablesFromSpecificFile("PBL_Status_be.mdb","\\us038fp01\Data\USM\Hbg_camp\Access\PBL Status\BackEnd\PBL_Status_be.mdb")
============================
Public Function ReLinkTablesFromSpecificFile(OldFileName As String,
NewPathAndFile As String)
Dim Dbs As Database
Dim tdf As TableDef
Dim Tdfs As TableDefs
Dim returnvalue As Integer
Dim i As Integer
Dim connectfilename As String

Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs
On Error Resume Next

returnvalue = SysCmd(acSysCmdInitMeter, "Running to " & Tdfs.Count,
Tdfs.Count)
For i = 0 To Tdfs.Count - 1
returnvalue = SysCmd(acSysCmdSetStatus, "Running " & i & " of " &
Tdfs.Count)
If Tdfs(i).SourceTableName <> "" Then 'If the table source is other
than a base table
'odbc table - Tdf.Connect starts with ODBC;DSN=ADW
'linked table tdf.connect starts with ;DATABASE=\\us038fp01...
NAME OF DATABASE THAT it is in
connectfilename = Right(Tdfs(i).Connect, Len(Tdfs(i).Connect) -
InStrRev(Tdfs(i).Connect, "\"))
If connectfilename = OldFileName Then
Tdfs(i).Connect = ";DATABASE=" & LCase(NewPathAndFile) 'Set
the new source
Tdfs(i).RefreshLink 'Refresh the link err = 3024 means new
file could not be found
'DeleteLinkThenLinkTable Tdfs(i).SourceTableName,
LCase(NewPathAndFile)
End If
End If
Next
returnvalue = SysCmd(acSysCmdRemoveMeter)
End Function
=====================================
 
Back
Top