Performance Issues after converting to Access 2002 3

[jayde]

I have just upgraded to Access 2002 from Access 97. It seems to take a lot longer to open tables in Access 2002 than it did in Access 97. Not even an empty table comes up quickly. It seems to be true with local tables and linked tables. This is a brand new Pentium 1.8 running Windows XP. Is there something I can do to improve performance to at least the level of Access 97. I plan to move the data to SQL Server over the next month so a temporary work-around would be fine. Any suggestions would be appreciated.

Cheers,

Jayde

 

[jfischer]

Here are a couple of things you could try in order to help the performance of Access 2000.

1) Access 2000 has a new feature called SubDataSheets that it sets up by default when you convert Access 97 MDBs. The SubDataSheets are set to [Auto] and should be set to [None]. These SubDataSheets are included based on the Relationships you have defined. When you open a main table, Access 2000 then also opens all the sub tables related to it--big performance problem in some cases. To turn off the SubDataSheets, open a main table in Design mode, choose Properties, and set SubDataSheets to [None]. You'll need to check each of your tables to see which ones Access 2000 has set to [Auto].

2) Access 2000 has a feature called "Name AutoCorrect" on the Tools, Options, General tab that can impact performance in some cases. Uncheck this option to see if it helps.

 

[jayde]

The subdatasheet was the problem. If (or when) someone else has the problem, I found a nifty little script (MS Knowledge Base Q261000) that loops through all of a database's tables and set the subdatasheet property to none. I had more than a hundred table so it was really handy.

Thanks so much JFischer this was a hair puller!!

\ \ \ | | / / /
[---O-O---]
\ (_) /
{{{-}}}
--OooO--OooO---
Jayde

Function TurnOffSubDataSheets()
  Dim MyDB As DAO.Database
  Dim MyProperty As DAO.Property
  
  Dim propName As String
  Dim propType As Integer
  Dim propVal As String
  
  Dim strS As String
  
  Set MyDB = CurrentDb
  
  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 &quot;Error: &quot; & Err.Number & &quot; on Table &quot; _
                 & MyDB.TableDefs(i).Name & &quot;.&quot;
                 MyDB.Close
                 Exit Function
             End If
          End If
          
      End If
  Next i

MsgBox &quot;The &quot; & propName & _
&quot; value for all non-system tables has been updated to &quot; & propVal & &quot;.&quot;

MyDB.Close

End Function

In the Immediate window, type the following text, and then press ENTER to run the function:
TurnOffSubDataSheets
Note that after a short time, you receive a message box that tells you that the subdatasheet Name property on all nonsystem tables in the database has been set to [NONE] .