CurrentDb.TableDefs.Delete Slow to Execute

[Stan]

Access 2003

I have a simple database with 5 tables, the database is split into a
frontend (FE) and backend (BE). The database is to be used on a peer
to peer network of 1 laptop and 1 desktop. There will be two users who
will use the DB simultaenously when the laptop user is in the office.
My plan is to have a main BE (called NetworkBE) on the desktop which
is used by both computers. When the laptop user leaves the office they
still need to be able to use the DB and hence i have a alternate BE on
the laptop (called LocalBE). If the laptop user is linked to NetworkBE
and closes the DB they are prompted to synchronise with LocalBE. This
ensure the user leaves the office with all current data. On returning
to the office the laptop FE detects the presence of NetworkBE,
resync's and then relinks to NetworkBE.

This all works but my issue relates to the
"CurrentDb.TableDefs.Delete" command used as part of my "RelinkTables"
function shown below. (Note: I have taken this code from another
posting on the net and admit i don't fully understand it!)
The issue is that when i relink to NetworkBE the
"CurrentDb.TableDefs.Delete" command is quite slow, (approx 5 seconds
per table). My tables are small, the largest has 10 fields and
currently 50 records. Note that relinking to LocalBE does not give
this delay, this is strange because at the moment LocalBE and
NetworkBE are identical and currently reside in the same local folder.
(The FE is also in the same folder). So this has nothing to do with
any network delays.

Any idea's why the command is slow and why it only does it for
relinking to NetworkBE?

Is there another way of relinking the tables which may not have this
problem?

Thanks

------------------ Code ----------------------
' Note OldPath and Newpath are passed the full path to NetworkBE and
LocalBE mdb files to relink the tables



Public Sub RelinkTables(ByVal OldPath As String, ByVal NewPath As
String)
Dim Errorcode As Long
Dim rstMSysObjects As dao.Recordset
Dim TableName As String

Do

Set rstMSysObjects = CurrentDb.OpenRecordset _
("SELECT ForeignName FROM MSysObjects " _
& "Where Database = '" & OldPath & "'" _
, dbOpenSnapshot _
)

If Not (rstMSysObjects.BOF And rstMSysObjects.EOF) Then

TableName = rstMSysObjects!ForeignName

CurrentDb.TableDefs.Delete (TableName) ' <------
Slow

DoCmd.TransferDatabase acLink, "Microsoft Access",
NewPath, _
acTable, TableName, TableName, True, False
End If

Loop Until rstMSysObjects.BOF And rstMSysObjects.EOF

rstMSysObjects.Close
Set rstMSysObjects = Nothing
End Sub

---------------- End of Code ------------------------------

 

[BeWyched]

Hi Stan

I don't know why the operation is so slow. My guess is that its a network
issue. I have an application which allows users to re-link to several
back-end databases. There are 3 back-end databses containing over 90 (many
very large)tables and the whole re-link process only takes 4 seconds

You could try my approach which is:

' Delete the existing links
' You might want an If statement to retain any local tables
' The On Error Resume Next line is to bypass the error that will be
triggered as the loop tries to delete the hidden system files - don't worry,
they won't get deleted.

Set tblDefs = Currentdb.TableDefs
For Each tbl in tblDefs
On Error Resume Next
DoCmd.DeleteObject acTable, tbl.Name
On Error Goto 0
Next
' Now re-link to the B/E
strConnection = ";database=" & "path and name of your B/E database"
Set dbLink = OpenDatabase("Path and name of your B/E database")
Set tblLinks = dbLink.TableDefs
For Each tbllink In tblLinks
Set tdflinked = db.CreateTableDef(tbllink.Name)
tdflinked.Connect = strConnection
tdflinked.SourceTableName = tbllink.Name
CurrentDb.TableDefs.Append tdflinked
Next

I don't know if this will be faster but its worth a go.

Good luck.

BW

 

[George Nicholson]

There is different approach on the Access Web:
http://mvps.org/access/tables/tbl0009.htm
Relink Access Tables through Code.

I don't think there's any reason to delete TableDefs at all <shiver>. All
you really need to do is reset the Connect property of each linked TableDef
& then use the RefreshLink method.


HTH,

 

[BeWyched]

Hi George.

I like your method but will it allow Stan's users to re-link to tables
residing in different locations? My understanding of his situation is that
the Users copy the B/E to their laptops where, presumably the F/E will need
to link to the copied B/E's. Then reverese the procedure when they re-appear
in the office.

If your suggestion can achieve this then its clearly the way forward.

Regards.

BW

 

[George Nicholson]

Yes it can be adapted to do that.

I adapted my startup routine to look in about 4 places for the appropriate
backend (app directory, path stored as registry setting, etc). If all those
failed, I give the user the option to search for the file via a standard
OpenFile dialog. I also (under advanced functions) allow the user to link
up to a different backend if they want.

Both use that code to ddo the actual linking when the file is found.

HTH,