Microsoft KB Archive/293470

= FIX: Performance Decreases When the Source MDB File of Linked Access Table Is Open =

Article ID: 293470

Article Last Modified on 7/28/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.0
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5

-



This article was previously published under Q293470



SYMPTOMS
SELECT statements that are executed through ADO against a linked table in a Microsoft Access database return significantly slower when the Access file that acts as the source of the link is opened on another computer.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

This bug was corrected in Microsoft Data Access Components version 2.7.



Steps to Reproduce Behavior
 Create a new, empty Access 2000 database named Child.mdb. Open the Child.mdb file in Access, and add a linked table that connects to the Access Northwind sample database Customers table. For clarity, name the newly linked table &quot;Linked_Customers&quot;. Create a new Visual Basic 6.0 project. Form1 is created by default. Add a reference to Microsoft ActiveX Data Objects 2.x. Add a command button (cmdTest) to Form1.  Add the following code to the cmdTest_Click event of Form1: Dim adoRS As ADODB.Recordset Dim sngTM As Single Dim adoCN As New ADODB.Connection

adoCN.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;Data Source=<full path and file name for Child.mdb&quot; Set adoRS = New ADODB.Recordset sngTM = Timer adoRS.Open &quot;SELECT * FROM LINKED_CUSTOMERS&quot;, _ adoCN, adOpenForwardOnly, adLockReadOnly, adCmdText adoRS.MoveFirst MsgBox &quot;Elapsed Time = &quot; & Timer - sngTM adoRS.Close Set adoRS = Nothing adoCN.Close Set adoCN = Nothing </li> Change the Data Source property of the connection string to the Child.mdb path.</li> Run the Visual Basic project, and click the command button to execute the code. Note the elapsed time.</li> On a second workstation that has the appropriate version of Access installed, open the Northwind sample database.</li> Click the command button again on the default form of the Visual Basic project that is running on the first workstation. Notice that the elapsed time is significantly greater than it was the first time.</li> Close the instance of the Northwind sample database that is running on the second workstation.</li> Stop the execution of the Visual Basic project that is running on the first workstation.</li>  Change the connection string in the sample code as follows: &quot;Driver={Microsoft Access Driver (*.mdb)};&quot; & _ &quot;DBQ=Child.mdb;DefaultDIR=<path to Child.mdb>&quot; </li> Repeat steps 8 through 10. You may notice similar behavior with this second connection string that uses the ODBC Driver. However, the performance decrease is slightly less than that observed with the OLE DB connection string.</li></ol>

Keywords: kbbug kbfix kbmdac270fix kbjet kbclient kbmdacnosweep KB293470

-

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

© Microsoft Corporation. All rights reserved.