Relink to BackEnd when necessary

[Barry]

I would like to relink to back end tables but, only when necessary. If
nothing has changed, links not broken, backend file has not moved or new
frontend not implemeneted, no relinking or refreshing of links should occur.
I have found some code that calls up a dialog box etc but I would like to
check first if it needs to run. So, now my question is, how can I find out
if the links are broken? I understand that I need to loop through the
Tabledefs but what am I to expect as a return if the links are as they should
be? Your insight is greatly appreciated.
Thanks,
Barry

 

[Jack Leach]

The path to a particular linked table can be gotten by parsing this property:

Tabledefs("tablename").Connect

You can pull the path from there and put it through something like this:

If Len(Dir(strPath)) = 0 Then
'doesn't exist
fRefreshLinks
End If


Apparently you can also get the path to a table by querying the MSysObjects
table...

?DLookup("Database", "MSysObjects", "Name = '"TableName'")

(thanks to Bernie Brooks for my recent discovery of this method)

.... and that will keep your from parsing the path out of the Connect string.

hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Barry]

Thanks for the reply. I have more questions please. Does the Tabledefs
collection contain the connect information? In otherwords can't I loop
throught the collection and find the tables that are not connected?
Excluding the Msys tables.

Dim dbs As Database
Dim tdf As TableDef

Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If "Something to identify linked table here" then
fRefreshLinks
else
next
End If

What should be happening in the "something to identify linked table here"
portion? Or I am more confused than I thought?
Thanks,
Barry

 

[Jack Leach]

This will work if all your tables are linked to an access backend... if they
are from other sources you will need a different connection string. The
connection string property for each TableDef instructs Access where to table
resides, alone with what type of connection it is. For linked tables in
Access, the process is relatively simple. For linked tables to something
other than an access database, you have to interpret your connection string a
bit. For local tables, the connect string is a zero-length string.

Here's how to do it if you only have local and linked tables to an Access
database... your connection string will look like this:

;DATABASE=C:\Users\Jack\Desktop\New Microsoft Office Access Application.mdb

so loop your Tabledefs collection, and for each .Connect string, parse out
the filename, then check if the file exists. If it doesn't exist, call the
openfile dialog. If all your tables are linked to the same backend, you may
want to choose a table to test this on before you begin your loop, so the
user only has to pick a new file once.

This is aircode, but should be pretty close:

Public Function RelinkIfRequired()
Dim sNewFile As String 'new file (if required)
Dim sConnectFile as string 'current connection file
Dim td As TableDef


'pick a linked table to test it on...

sConnectFile = pfGetFileFromConnect( _
CurrentDb.TableDefs("Table1").Connect)

If Len(Dir(sConnectFile)) = 0 Then
'the file doesn't exist
sNewFile = <openfile dialog here... see mvps.org for example>
Else
'connection is correct, all done here
Exit Function
End If

For Each td In CurrentDb
If Len(td.Connect) <> 0 Then
td.Connect = Replace(td.Connect, sConnectFile, sNewFile)
td.RefreshLink
End If
Next td

Set td = Nothing

End Function



Private Function pfGetFileFromConnect(sConnect As String) As String
pfGetFileFromConnect = _
Replace(sConnect, ";DATABASE=", "")
End Function


hope this clears things up a bit

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Jack Leach]

For the record, this is almost the exact same thing that the fRefreshLinks
function provided by Dev Ashish at mvps.org does, except

1) Dev's provides any connection type, not just linked tables
2) A dialog is already set up to be called on ConnectFileNotFound
3) His routine does not first check to see if the links are broken, it just
relinks them anyway.

So, if you want to use his (much better) function, but only when the link is
broken, make your own "wrapper" function for it... pull a test connect, parse
out the path, check if it exists, and run fRefreshLinks if it doesnt...

Public Function RelinkIfRequired()
Dim sFile As String
sFile = CurrentDb.TableDefs("Table1").Connect
sFile = Replace(sFile, ";DATABASE=", "")
If Len(Dir(sFile)) = 0 Then
fRefreshLinks
End If
End Function



http://mvps.org/access/tables/tbl0009.htm


this might also be helpful
http://mvps.org/access/tables/tbl0007.htm

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Barry]

Jack:
Thanks for all the information. I will attempt to make this work. All my
tables are connected to an access back end. Again, I appreciate your
expertise.
Sincerely,
Barry