Re-link VERY slow when other users connected to BE

[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?

 

[Allen Browne]

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.

 

[Brian]

Thank you again, Allen.

That solved the problem for me.

 

[John Spencer]

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

 

[John Spencer]

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

 

[FPS, Romney]

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

 

[Allen Browne]

Good news!