Re-link VERY slow when other users connected to BE

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

FE: MDE on five WinXP Pro, Access 2003 runtime PC's.
BE: MDB on Win2003 server

The path to the BE on my test system is different than the path to the BE
for the live users. When I distribute a new MDE, I trap an error on the first
attempt to contact the BE, and then re-link automatically. Here is the code,
in brief (omitting the dimensioning of some variables, etc.)

Set dbProgram = CurrentDb
For TableCount = 0 To dbProgram.TableDefs.Count - 1
If dbProgram.TableDefs(TableCount).Connect <> "" Then
dbProgram.TableDefs(TableCount).Connect = ";DATABASE=" &
FilePathData & ""
dbProgram.TableDefs(TableCount).RefreshLink End If
Next TableCount

The entire process takes perhaps 5-10 seconds when I am the only user
connected to the DB. If, however, any other user is already connected when I
get to this point, the process takes perhaps five minutes. If I have the
other user get out of the app while this is running, it seems to release
something, and the re-link finishes almost immediately.

Ideas?
 
OpenDatabase() on the back end before you run your linking code:

Dim dbData As DAO.Database
Set dbData = OpenDatabase(FilePathData)
'put your linking code here.
dbData.Close
Set dbData = Nothing

Although you don't use dbData for anything, it has the effect of holding the
file open for the entire operation, and you will find it executes more
quickly.
 
TRY this.

After you have relinked the first table. Establish a connection to it and keep
the connection open until you finish relinking.

Dim tfConnected as Boolean
Dim rstAny as DAO.Recordset

Set dbProgram = CurrentDb
For TableCount = 0 To dbProgram.TableDefs.Count - 1
If dbProgram.TableDefs(TableCount).Connect <> "" Then
dbProgram.TableDefs(TableCount).Connect = ";DATABASE=" & _
FilePathData & ""
dbProgram.TableDefs(TableCount).RefreshLink

If tfConnected = False then
tfConnected = True
Set rstAny = dbProgram.OpenRecordset( _
"SELECT * FROM [" & _
dbProgram.Tabledefs(TableCount) & _
"] WHERE 1=2")
End If
End If
Next TableCount

IF tfConnected = True then
rstAny.Close
Set rstAny = Nothing
End If


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Darn, I wish I had seen Allen Browne's response before I posted.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Thank you, Allen! Wish I would have run across this before. It' always taken
about 2 1/2 min. for people to link to our file server to transfer data from
their laptops if someone already had the main BE open. Now it takes about 5
seconds.
Mark
 
Back
Top