Re: CurrentDb.TableDefs.Delete Slow to Execute
- From: "George Nicholson" <GeorgeNJunk@xxxxxxxxxxx>
- Date: Fri, 2 Feb 2007 18:50:59 -0600
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,
"Stan" <guy@xxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1170438052.692953.227490@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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 ------------------------------
.
- Follow-Ups:
- Re: CurrentDb.TableDefs.Delete Slow to Execute
- From: BeWyched
- Re: CurrentDb.TableDefs.Delete Slow to Execute
- References:
- CurrentDb.TableDefs.Delete Slow to Execute
- From: Stan
- CurrentDb.TableDefs.Delete Slow to Execute
- Prev by Date: RE: open arguments.
- Next by Date: Re: Why does Access and Excel use different SQL formats?
- Previous by thread: RE: CurrentDb.TableDefs.Delete Slow to Execute
- Next by thread: Re: CurrentDb.TableDefs.Delete Slow to Execute
- Index(es):
Relevant Pages
|
|
