Linked tables / refreshing links takes long

[juvi]

hello,

I have got an access database splitted into 3 mdbs.

frontend.mdb (has forms and code)
backend.mdb (stores tables with data for work)
archive.mdb (stores tables with data produced)

in the fronted I have linked about 15 tables from backend.mdb (local) and 3
tables from archive.mdb (archive.mdb is on a server in the network)

At startup I am refreshing the links to the actual path of both databases
(backend.mdb and archive.mdb) in my frontend.mdb ..... and this takes a lot
of time: here is my code:

Sub TabloLinkleriniKontrolEt(sSourceFile As String)
Dim daTaban As Database, tbTablo As TableDef
Set daTaban = CurrentDb

For Each tbTablo In daTaban.TableDefs
If InStr(tbTablo.Connect, "DATABASE=") > 0 Then
Debug.Print tbTablo.Connect
If InStr(tbTablo.Connect, NetworkBackENDDB) > 0 Then
If tbTablo.Connect <> ";DATABASE=" & NetworkSyncPathBackEND
& NetworkBackENDDB & ";Pwd=" & UsersDBPWBE Then
tbTablo.Connect = ";DATABASE=" & NetworkSyncPathBackEND
& NetworkBackENDDB & ";Pwd=" & UsersDBPWBE
tbTablo.RefreshLink
End If
End If
If InStr(tbTablo.Connect, backEndDB) > 0 Then
If tbTablo.Connect <> ";DATABASE=" &
Application.CurrentProject.path & "\" & backEndDB & ";Pwd=" & UsersDBPWBE Then
tbTablo.Connect = ";DATABASE=" &
Application.CurrentProject.path & "\" & backEndDB & ";Pwd=" & UsersDBPWBE
tbTablo.RefreshLink
End If
End If
End If
Next
daTaban.Close
End Sub

Hope somebody can help me with this issue.

BR,
juvi

 

[Alex Dybenko]

hi,
The trick is to open backend file before relink process and close it after:

http://accessblog.net/2009/10/how-to-relink-access-tables-faster.html


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Daniel Pineault]

Could you quantify 'a lot of time': 30 secs, 1min, 5mins, 10mins...
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.