Attached tables & performance question

[John Moore]

From a performance standpoint, does it make any sense to "delete"
infrequently used attached tables and only reattach them using code if
they are needed for a specific form or function?

My commercial application is quite large and has over 45 attached
tables and it takes sometime to initially open on some of my client's
systems running over a LAN. The application is split properly between
a FE and BE, we maintain a persistent recordset connection on open,
and there are no recordsets loaded with the opening screen. One of our
developers thought we might be able to increase open speeds by only
attaching tables we needed so we detach the seldom used ones on
application close. We would like to know if this is worth doing.

Should we be detaching and reattaching on close and open respectively
or is this a bad idea or a waste of time?

Any and all help is appreciated.

TC

 

[Jeff Boyce]

What has your testing shown so far? The specifics of your situation may
result in performance differences when compared to other folks' setups...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

I would say that attaching and detaching tables is generally a waste of effort.

You might make sure that the tables in the backend do not have subdatasheets.

You can clear the subdatasheets by running this code in backend.

'Source: MS Knowledge Base #275085
'http://support.microsoft.com/Default.aspx?id=275085

Sub TurnOffSubDataSheets()
Dim MyDB As DAO.Database
Dim MyProperty As DAO.Property
Dim propName As String, propVal As String, rplpropValue As String
Dim propType As Integer, i As Integer
Dim intCount As Integer

On Error GoTo tagError

Set MyDB = CurrentDb
propName = "SubDataSheetName"
propType = 10
propVal = "[None]"
rplpropValue = "[Auto]"
intCount = 0

For i = 0 To MyDB.TableDefs.Count - 1
If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
If MyDB.TableDefs(i).Properties(propName).Value = rplpropValue Then
MyDB.TableDefs(i).Properties(propName).Value = propVal
intCount = intCount + 1
End If
End If
tagFromErrorHandling:
Next i

MyDB.Close

If intCount > 0 Then
MsgBox "The " & propName & " value for " & intCount & _
" non-system tables has been updated to " & propVal & "."
End If

Exit Sub

tagError:
If Err.Number = 3270 Then
Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
MyProperty.Type = propType
MyProperty.Value = propVal
MyDB.TableDefs(i).Properties.Append MyProperty
intCount = intCount + 1
Resume tagFromErrorHandling
Else
MsgBox Err.Description & vbCrLf & vbCrLf & _
" in TurnOffSubDataSheets routine."
End If
End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Tony Toews [MVP]]

From a performance standpoint, does it make any sense to "delete"
infrequently used attached tables and only reattach them using code if
they are needed for a specific form or function?

Not at all. On startup Access does not scan through the linked
tables. I know this because I've had tables linked to no longer
existent BEs and Access doesn't tell you until code or a form tries to
open a table.

My commercial application is quite large and has over 45 attached
tables

That's medium sized.

and it takes sometime to initially open on some of my client's
systems running over a LAN.

All LANs or just a few? Are these in fact WANs where the server is in
another building?

Have you done a decompile recently?

You've visited Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm ?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[John Moore]

I want to thank everybody who responded. You answered my question
about the number of attached tables and performance.

JM

 

[Ray Jefferson]

It is not necessary to reconnect them if they are already linked into the
front end .mdb. Typically its not the number of tables that would be causing
it to take a while, its the multi-user environment. For example, if you were
reconnecting the tables and nobody else was using the app, the tables will
relink relatively quickly. If another user has already started the app and
then you attempt to connect backend .mdb tables, it will take a while to do
this. Technically I do not know why this is, I just know it to be so. So
just skip it, tables are already attached anyway and all is good.

RJ
Database Whiz Consulting
www.databasewhiz.com

 

[Tony Toews [MVP]]

If another user has already started the app and
then you attempt to connect backend .mdb tables, it will take a while to do
this. Technically I do not know why this is, I just know it to be so. So
just skip it, tables are already attached anyway and all is good.

The solution to the performance problem when relinking tables and
another use is already in the backend is to
1) open a database variable against the backend MDB/ACCDB
2) once you've relinked the first table open a recordset against that
table.
Ensure you close the variable when finished.

Tony


--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Keith Wilby]

If another user has already started the app and then you attempt to
connect backend .mdb tables, it will take a while to do this.

Eh? Are you assuming that users share a FE file?

Keith.

 

[david]

this. Technically I do not know why this is, I just know it to be so.

It is because Windows caches the files and does all your network
operations locally.

That can't work when you have more than one user, so Windows
reverts to leaving the files on the server sending all network
operations across the network.

The second user is particularly slow, because Windows has
to recover the files from the first user, and put them back
on the server again.

Using a persistent connection is faster, and also prevents Windows from
switching between single-user mode and shared
mode.

(david)