code that link a database impedes performance when a form is opened 1

[maupiti]

I have database. When a database is opened, it runs the code
below that link it with the tables in another
access database. The problem is once this code is
executed, and then if I opened a form,
it takes 10 minutes for the form to comes up.
When I remove this code, it only takes
about 5 seconds for the form to comes up with
all the correct data.

1) What could cause it to take so long ?
2) Does access automatically linked the database
when you open the database
to where writing code to link it would be
redundant and slow ?

//////////////////////////////////

Public Sub LinkDatabase(sLoc As String)
Dim myrs As DAO.Recordset
Dim sDirectory As String
Dim sDatabase As String
Dim sTable As String
Dim sLoc2 As String
Dim db As Database
'Dim td As TableDef

DoCmd.Hourglass True
'link all tables and save new database location
Set myrs = CurrentDb.OpenRecordset("tbl000TableLinks")

On Error GoTo Err_Link:
With myrs
While Not .EOF
If !TableDatabase = "AuditAssist_Support.mdb" Then
sDirectory = Application.CurrentProject.Path & "\"
sDatabase = "AuditAssist_Support.mdb"
sLoc2 = sDirectory & sDatabase
Else
sDirectory = GetDBDir(sLoc)
sDatabase = Dir(sLoc)
sLoc2 = sLoc
End If
Set db = DBEngine.Workspaces(0).OpenDatabase(sLoc2, False, False, "MS Access;PWD=13453")
'Import tables from the specified Access database.
'For Each td In db.TableDefs
' sTable = td.Name
sTable = !TableName
' If Left(sTable, 3) = "tbl" Then
'delete linked table
CurrentDb.TableDefs.Delete sTable
'link table
DoCmd.TransferDatabase acLink, "Microsoft Access", sLoc2, acTable, sTable, sTable
' End If
'Next
.Edit
!TableDirectory = sDirectory
!TableDatabase = sDatabase
.Update
.MoveNext
Wend
End With
CurrentDb.TableDefs.Refresh
Set myrs = Nothing
Set db = Nothing

'MsgBox "Linked tables have been refreshed!", vbExclamation, "System Setup"
DoCmd.Hourglass False
Exit Sub

Err_Link:
Set myrs = Nothing
Set db = Nothing
DoCmd.Hourglass False
Select Case Err.Number
Case 3265
'table does not exist, skip delete
Resume Next
Case 32755
'cancel common dialog
MsgBox "Linked tables have NOT been refreshed!", vbExclamation, "System Setup"
Case Else
MsgBox Err.Description, vbCritical, "Error"
DoCmd.Quit acQuitSaveNone
End Select

End Sub

 

[PHV]

You may take a look here:

http://www.peterssoftware.com/tlk.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RickSpr]

I don't see any obvious problem with this code. I'd note, though, that the code for GetDbDir() is not included here, and could possibly be involved in the delay. Also, the use of the Dir() function seems strange to me--you seem to be assuming that there can only ever be one file in the directory being passed to LinkDatabase, and that it's an Access database. Are you sure of that?

Exactly how many rows exist in this "tbl000TableLinks" table? You're looping through every one of them. For all I can tell, you could be relinking each table numerous times because of multiple appearances in the "tbl000TableLinks" table.

To answer your second question, "Yes and no". Access doesn't automatically relink the tables, but it starts up with them still linked from the last time the database ran. You only need to be doing this relinking code if the sloc parameter is changing from whatever it was the last time LinkDatabase was called, even if that was in a previous session, or if the contents of the "tbl000TableLinks" table has changed.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[maupiti]

Hi RickSpr, and PHV thank you both for your help.
The GetDbDir() is below.

///////////////////////////////////////////

Function GetDBDir(Optional dbName As Variant) As String
Dim i As Integer

If IsMissing(dbName) Or IsNull(dbName) Then
dbName = CurrentDb.Name
End If

For i = Len(dbName) To 1 Step -1
If Mid(dbName, i, 1) = "\" Then
'Debug.Print "DBDir is " & Left(dbName, i)
Exit For
End If
Next i

GetDBDir = Left(dbName, i)
End Function

 

[RickSpr]

Nothing in that code would slow it down (though I have to say, it wouldn't act very nicely if the dbName doesn't have a "\" in it).

What about the number of records in the table? (Reread my earlier post.)

Have you stepped through the code that opens the form, so that you know for sure the delay is occurring on the DoCmd.OpenForm? Perhaps there is an unintended loop in the Form_Load or Form_Open event procedure instead.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein