Link/relink all tables in back end

[LGarcia]

Hi all,
I'm using Function fRefreshLinks (courtesy of Dev Ashish) to relink tables
to my back end when the front end is opened. Works fine. Occassionally we'll
add a few tables to the back end. I believe that since the Connect property
of these new tables is not set, they are not inclued in the relinking. Can
anyone offer some help as to how I would go about making sure all tables in
the backend get linked or relinked?
Thanks!
LGarcia

 

[Guest]

Without a complete re-write of the fRefreshLinks function, you will need to
manually establish the linked tables the first time, for any new tables.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[punjab_tom]

Dude don't listen to this crap

just use Access Data Projects; you create a table ONCE and there is no
extra work

these dipshits around here are too stupid to learn SQL Server; don't
be like them!

-Tom

 

[Guest]

Assuming the names are the same for the tables in the back end and the
existing linked tables in the front end something along these lines should do
it:

Public Function LinkNewTables(strPath As String)

Dim dbsFront As DAO.Database, dbsBack As DAO.Database
Dim tdfFront As DAO.TableDef, tdfBack As DAO.TableDef
Dim strTable As String

' return references to front and back ends
Set dbsFront = CurrentDb
Set dbsBack = OpenDatabase(strPath)

For Each tdfBack In dbsBack.TableDefs
' check if link to table exists and if not create link
strTable = tdfBack.Name
On Error Resume Next
Set tdfFront = dbsFront.TableDefs(strTable)
If Err <> 0 Then
DoCmd.TransferDatabase acLink, _
"Microsoft Access", strPath, _
acTable, strTable, strTable
End If
Err.Clear
Next tdfBack

End Function

As well as calling the fRefreshLinks function call the above, passing the
full path to the back end file into the function as a string expression e.g.

LinkNewTables "F:\SomeFolder\SomeSubfolder\SomeDb_be.mdb"

In reality you'd get the path from somewhere and pass it as a string
variable. I'm not sure if the fRefreshLinks function, with which I'm not
familiar, has a way of getting the path to the back end if the current links
are not valid, but if it does you can make use of that.

Ken Sheridan
Stafford, England