Issues relinking to backend tables

[Beth]

I have tried everything I can think of to resolve this issue and I really
hoping for some help from the experts out here.
I have a front end database (Front.mdb) and I want my users to be able to
selectively link to different backend files (back1.mdb and back2.mdb) from
my switchboard form. To do that I have the code to loop through the
tabledefs and sets the path to the new linked file. All that works fine if I
am working from one machine.

If I have the system networked and user A is trying to relink to the backend
file that user B is using, the relink can take up to 5 minutes. If user B
closes back to the main menu where no tables are being accessed the relink
happens almost instantly.
I have checked everywhere I can think of to make sure there are no record
locking steps, but everything I have found says the tables are set with no
record locking.

I really need help to make this work. Any suggestions?
thanks,
Beth

 

[Dirk Goldgar]

I have tried everything I can think of to resolve this issue and I
really hoping for some help from the experts out here.
I have a front end database (Front.mdb) and I want my users to be
able to selectively link to different backend files (back1.mdb and
back2.mdb) from my switchboard form. To do that I have the code to
loop through the tabledefs and sets the path to the new linked file.
All that works fine if I am working from one machine.

If I have the system networked and user A is trying to relink to the
backend file that user B is using, the relink can take up to 5
minutes. If user B closes back to the main menu where no tables are
being accessed the relink happens almost instantly.
I have checked everywhere I can think of to make sure there are no
record locking steps, but everything I have found says the tables are
set with no record locking.

I really need help to make this work. Any suggestions?

I wonder if this is related to the same problem of LDB locking described
here:

http://www.granite.ab.ca/access/performanceldblocking.htm

Try having your code open a connection to the back-end in question --
say, a recordset that returns no records -- before entering the relink
process, and hold the recordset open until the relinking is done.

This is just a guess, so please let me know if it helps.

 

[Piri]

Beth, if indeed this is the issue I can vouch for the fix from Tony.
By opening the persistent connections as he recommends my re-linking
routine takes seconds, rather than the several minutes it used to take
at times.

What I did notice (and was the clue to the issue) was that the time
taken to re-link tables expotentiated outwards as more users were
connecting to the BE database(s).

Piri

 

[Beth]

The posts and the solution appear to mimick exactly what I am seeing. I
watched the Windows explorer window while I was opening the front end to see
that the .ldb file didn't appear until I left the menu and opened a data
form. The more users I have the worse the problem gets.
I took the following steps and the problem did not resolve. Am I missing
something?

When the splash screen on the front end opens I now have it open a form as
hidden. That form has a control source for a table in the backend that has
2 records. That form stays open until the entire front end is closed.
Is there somethine else I need to do to create the persistent link?
thanks for the help.
Beth

 

[Piri]

In my case I have a form for each back-end database - there are two.
In each BE I have a simple one field table for the purpose of providing
a source for the forms. I delete then re-link those source tables before
opening each "always open" form. That process can take a few seconds -
no more. Each form has a single text box using as its data source
control the field in the source table. That implies having a record in
the table opened. I'm not sure that having a blank for with the table as
its record source is enough.(Do you have any record open - could this be
your problem?)

Once my FE has gone through its load-up routine (which includes linked
table refreshing etc.), I close the hidden forms - I'm not sure of the
advantage (if one) of persisting though the whole FE session, presuming
the purpose of the forms has been effected once all operation links
have been completed.

For me the result has been remarkable.

Piri

 

[Albert D.Kallal]

How can you have a persistainct conection to the back end databsae when you
are re-lnking?

To re-link..you will/should close all tables. The only way I can think of
acheinvg a persicaton conenciotn is AFTER THE FIRST table is linked, you
then force a connecton to the back end...and your re-link code will then
perfome VERY well..

Here is the re-link I code I use...and not how after the first table is
linked..I then open it (that gives me my persistanct conenciton).

Of couse...after all is linked...obivlariy soehwre else in the appcaiton I
will have to again open a table to keep that persiaocnt coneciton open..and
that will keep perfoamcne high throught the apcpation..and not just the
linking process....

The code I use to re-link is follows
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal


Public Function askReLink(strReLinkDir As String, Optional bolPrompt As
Boolean = True) As Boolean

Dim strMyPrompt As String
Dim mytables As TableDef
Dim strOld As String

Dim strTo As String
Dim strTable As String

Dim rstFirst As DAO.Recordset
Dim bolFirst As Boolean


Dim intTablePtr As Integer

Dim strBackPart As String


strMyPrompt = "This is a updated version of this program." & vbCrLf
strMyPrompt = strMyPrompt & "It needs to re-attach to files on your
system" & vbCrLf
strMyPrompt = strMyPrompt & "This can take about 30 seconds and will not
harm data" & vbCrLf & vbCrLf
strMyPrompt = strMyPrompt & "press ok to continue"

Beep

If bolPrompt = True Then
If MsgBox(strMyPrompt, vbOKCancel, "This is a new version") <> vbOK
Then
askReLink = False
Exit Function
End If
End If

askReLink = True


On Error GoTo Berr
For Each mytables In CurrentDb.TableDefs
strOld = mytables.Connect
strBackPart = strGetDbTable(mytables.Connect)

strTo = strReLinkDir & strBackPart

If Len(mytables.Connect) > 0 Then
If Left(mytables.Connect, 10) = ";DATABASE=" Then
mytables.Connect = ";DATABASE=" & strTo
mytables.RefreshLink
If bolFirst = False Then
Set rstFirst = CurrentDb.OpenRecordset(mytables.Name)
bolFirst = True
End If
End If
End If

Next mytables

If bolFirst = True Then
rstFirst.Close
Set rstFirst = Nothing
End If


If bolPrompt = True Then
MsgBox "Update is complete.....Thank you....", vbInformation, AppName
End If

Exit Function


Berr:
askReLink = False
DoCmd.Close acForm, "frmReLinkDisp"
MsgBox "Could not re-link" & vbCrLf & Error(Err), vbCritical, " Files not
found"
askReLink = False


End Function

 

[Albert D.Kallal]

Yikes...lets try this again with the spell check!!!


How can you have a persistent connection to the back end database when you
are re-linking?

To re-link..you will/should close all tables. The only way I can think of
achieving a persistent connection is AFTER THE FIRST table is linked, you
then force a connection to the back end...and your re-link code will then
perform VERY well..

Here is the re-link I code I use...and not how after the first table is
linked..I then open it (that gives me my persistent connection).

Of course...after all is linked...obviously somewhere else in the
application I
will have to again open a table to keep that persistent connection open..and
that will keep performance high through the application..and not just the
linking process....

The code I use to re-link is follows
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal


Public Function askReLink(strReLinkDir As String, Optional bolPrompt As
Boolean = True) As Boolean

Dim strMyPrompt As String
Dim mytables As TableDef
Dim strOld As String

Dim strTo As String
Dim strTable As String

Dim rstFirst As DAO.Recordset
Dim bolFirst As Boolean


Dim intTablePtr As Integer

Dim strBackPart As String


strMyPrompt = "This is a updated version of this program." & vbCrLf
strMyPrompt = strMyPrompt & "It needs to re-attach to files on your
system" & vbCrLf
strMyPrompt = strMyPrompt & "This can take about 30 seconds and will not
harm data" & vbCrLf & vbCrLf
strMyPrompt = strMyPrompt & "press ok to continue"

Beep

If bolPrompt = True Then
If MsgBox(strMyPrompt, vbOKCancel, "This is a new version") <> vbOK
Then
askReLink = False
Exit Function
End If
End If

askReLink = True


On Error GoTo Berr
For Each mytables In CurrentDb.TableDefs
strOld = mytables.Connect
strBackPart = strGetDbTable(mytables.Connect)

strTo = strReLinkDir & strBackPart

If Len(mytables.Connect) > 0 Then
If Left(mytables.Connect, 10) = ";DATABASE=" Then
mytables.Connect = ";DATABASE=" & strTo
mytables.RefreshLink
If bolFirst = False Then
Set rstFirst = CurrentDb.OpenRecordset(mytables.Name)
bolFirst = True
End If
End If
End If

Next mytables

If bolFirst = True Then
rstFirst.Close
Set rstFirst = Nothing
End If


If bolPrompt = True Then
MsgBox "Update is complete.....Thank you....", vbInformation, AppName
End If

Exit Function


Berr:
askReLink = False
DoCmd.Close acForm, "frmReLinkDisp"
MsgBox "Could not re-link" & vbCrLf & Error(Err), vbCritical, " Files not
found"
askReLink = False


End Function

 

[Beth]

Wonderful! I added the lines to force the connection after the first table
and it cut my connection time down to about 15 seconds. I can be happy with
that.
Have a great day!
Beth

 

[Dirk Goldgar]

How can you have a persistent connection to the back end database
when you are re-linking?

To re-link..you will/should close all tables. The only way I can
think of achieving a persistent connection is AFTER THE FIRST table
is linked, you then force a connection to the back end...and your
re-link code will then perform VERY well..

I was thinking that all you would need to do is open a connection to the
database, since you know its path before you start relinking; e.g,

Dim db As DAO.Database

Set db = Application.DBEngine.OpenDatabase(strNewDBPath)

' ... relink tables ...

db.Close

Although I suppose it might be necessary to actually open a recordset on
a table in that database.

However, plainly your code works, opening a recordset on the first
relinked table.

 

[Albert D.Kallal]

I was thinking that all you would need to do is open a connection to the

database, since you know its path before you start relinking; e.g,

Dim db As DAO.Database

Set db = Application.DBEngine.OpenDatabase(strNewDBPath)

' ... relink tables ...

db.Close

Although I suppose it might be necessary to actually open a recordset on
a table in that database.

I never tired the above...and always assumed you have to actually open a
table to force the .ldb file to be created.

I'll keep the above in mind...since it would be somewhat cleaner then the
flag in the loop idea that I currently use....