The Slow Database
Slow Access database by Adrian Price
Slow response in Acc2000 databases
Slow response in Acc2000 databases
The following code changes a table property from [Auto] to [None] in order to speed up the opening of forms. This can make a dramatic increase in speed in a database with server side data (March 2001).
'Run this to check that tables have SubDataSheets property set to [None] as default [Auto] slows performance.Private Sub TurnOffSubDataSheetsTest() Dim db As DAO.Database, sData As String Set db = CurrentDb TurnOffSubDataSheets db ' Check tables in this db sData = apCurrentDataFileName Set db = OpenDatabase(sData) TurnOffSubDataSheets db ' Check tables in server db Set db = NothingEnd Sub'220201: Following from KB Article ID: Q261000'ACC2000: Slower Performance on Linked TablesPrivate Function TurnOffSubDataSheets(MyDB As DAO.Database) Dim MyProperty As DAO.Property Dim propName As String Dim propType As Integer, i As Integer, intChangedTables As Integer Dim propVal As String Dim strS As String propName = "SubDataSheetName" propType = 10 propVal = "[NONE]" On Error Resume Next For i = 0 To MyDB.TableDefs.Count - 1 If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then If MyDB.TableDefs(i).Properties(propName).Value <> propVal Then MyDB.TableDefs(i).Properties(propName).Value = propVal intChangedTables = intChangedTables + 1 End If If Err.Number = 3270 Then Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName) MyProperty.Type = propType MyProperty.Value = propVal MyDB.TableDefs(i).Properties.Append MyProperty Else If Err.Number <> 0 Then MsgBox "Error: " & Err.Number & ": " & Err.Description & _ " on Table " & MyDB.TableDefs(i).Name & "." MyDB.Close Stop Exit Function End If End If End If Next i MsgBox "The " & propName & " value for " & intChangedTables & _ " non-system tables has been updated to " & propVal & ".", _ vbInformation, "Database: " & MyDB.Name MyDB.Close Set MyProperty = NothingEnd FunctionProperty Get apCurrentDataFileName() As String' Returns path to linked data. If error, returns ""On Error GoTo ErrorHandler' Get path to data from a sample table temp = CurrentDb.TableDefs(cSampleTable).Connect 'eg "MS Access;PWD=password;DATABASE=C:\Working\Data.mdb" apCurrentDataFileName = Mid$(temp, InStr(1, temp, "DATABASE=") + 9)ExitProperty:Exit PropertyErrorHandler: Select Case Err Case 3265 ' No data found in this collection eg not linked apCurrentDataFileName = gconEmpty Case Else MsgBox "Please report unexpected error " _ & vbCr & "Error " & Err & ": " & Err.Description, vbInformation, _ "apSettings.apCurrentDataFileName" apCurrentDataFileName = gconEmpty End Select On Error GoTo 0End Property The following changes a Tools|Options setting to improve speed.
Private Sub dbSettingTest()' Improves speed apSetProperty "Perform Name AutoCorrect", dbText, FalseEnd Sub