Microsoft KB Archive/261000

= BUG: Slower performance on linked tables in Access 2000 =

Article ID: 261000

Article Last Modified on 7/5/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q261000





For a Microsoft Access 2002 and later version of this article, see 275085.

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
You may notice that linked tables in your database have slower performance when you open tables, or that performing updates takes longer than you expect.

You may notice this behavior after you convert the database from an earlier version of Microsoft Access, although this is not the only case in which these symptoms may occur.



CAUSE
The database has many linked tables that also have many relationships, and the table that you are opening has its Subdatasheet Name property set to [Auto]. This can make the table slow to open. Subdatasheets are a new feature in Access 2000. Therefore, you are more likely to notice this behavior after you convert a database from an earlier version.



RESOLUTION
To work around this behavior, set the Subdatasheet Name property on each table in the back-end database to [NONE]. You can do this manually or by using code.

Setting the Subdatasheet Name property manually
To set the Subdatasheet Name property manually, follow these steps:
 * 1) In the back-end database, open a table in Design view.
 * 2) On the View menu, click Properties.
 * 3) Set the Subdatasheet Name property to [NONE].
 * 4) Save and then close the table.

Setting the property for all tables by using code
You can use a Visual Basic for Applications function to automatically set the Subdatasheet Name property for all nonsystem tables in a database to [NONE]. To do this, follow these steps:  Open the back-end database. On the Database window, click Modules, and then click New. On the Tools menu, click References. Make sure the Microsoft DAO 3.6 Object Library check box is selected, and then click OK.  Type or paste the following code in the new module. 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 = &quot;SubDataSheetName&quot; propType = 10 propVal = &quot;[None]&quot; rplpropValue = &quot;[Auto]&quot; 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 &quot;The &quot; & propName & &quot; value for &quot; & intCount & &quot; non-system tables has been updated to &quot; & propVal & &quot;.&quot; 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 & &quot; in TurnOffSubDataSheets routine.&quot; End If End Sub   In the Immediate window, type the following text, and then press ENTER to run the function: TurnOffSubDataSheets Note that after a short time, if all the tables are not already updated, you receive a message box that tells you that the SubDataSheetName property for <NumberOfTablesUpdated> non-system tables has been updated to [NONE]. </li></ol>

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

Additional query words: prb pra converted linked upgraded SubdatasheetName subdata sheet

<div class="securedata_section">

Keywords: kbbug kbnofix kbperformance KB261000

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.