DOCUMENT:Q260856 21-JUL-2000 [foxpro] TITLE :PRB: File Access Denied Opening Table Accessed by Using ADO PRODUCT :Microsoft FoxPro PROD/VER:WINDOWS:3.0,3.0b,5.0,5.0a,6.0 OPER/SYS: KEYWORDS:kbActiveX kbADO kbDatabase kbvfp300 kbvfp300b kbvfp500 kbvfp500a kbvfp600 KbDBFDBC kbGr ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 5.0a, 6.0 ------------------------------------------------------------------------------- SYMPTOMS ======== When you attempt to open a Visual FoxPro table for exclusive use after the table has been accessed by using an ActiveX Data Object (ADO), the following error message appears: File access is denied This error message may continue to occur for up to 60 seconds after the table has been released from the ADO connection. CAUSE ===== When an application creates an OLE DB data source object (through ADO or an OLE DB consumer), OLE DB services query the data provider for supported information and provide a proxy data source object to the application. To the consuming application, this proxy data source object looks like any other data source object, but, in this case, setting properties merely caches the information in the local proxy. When the application opens a connection in ADO, the proxy data source object checks whether any unused connections already exist that match the specified connection information. If a matching unused connection is found, rather than creating a new object, setting properties, and establishing a new connection to the database, the proxy data source object uses the existing initialized data source object. When the application releases the data source object, it is returned to the pool. Any data source object that is released by the application and not reused after 60 seconds is automatically released from the pool. RESOLUTION ========== Disable ADO Resource pooling in the ADO Connection string. Resource pooling can be controlled in the connection string used to connect to a data source by adding one of the following OLE DB Services clauses, as appropriate: lcConnectString="DRIVER=Microsoft Visual FoxPro Driver;" + ; "SourceType=DBF;SourceDB="+SYS(5)+SYS(2003) + ";" + ; "Exclusive=NO;BackGroundFetch=NO;NULL=NO;Collate=MACHINE;" + ; "OLE DB Services = 0") -or- lcConnectString="DRIVER=Microsoft Visual FoxPro Driver;" + ; "SourceType=DBF;SourceDB="+SYS(5)+SYS(2003) + ";" + ; "Exclusive=NO;BackGroundFetch=NO;NULL=NO;Collate=MACHINE;" + ; "OLE DB Services = -2") -or- lcConnectString="DRIVER=Microsoft Visual FoxPro Driver;" + ; "SourceType=DBF;SourceDB="+SYS(5)+SYS(2003) + ";" + ; "Exclusive=NO;BackGroundFetch=NO;NULL=NO;Collate=MACHINE;" + ; "OLE DB Services = -4") -or- lcConnectString="DRIVER=Microsoft Visual FoxPro Driver;" + ; "SourceType=DBF;SourceDB="+SYS(5)+SYS(2003) + ";" + ; "Exclusive=NO;BackGroundFetch=NO;NULL=NO;Collate=MACHINE;" + ; "OLE DB Services = -6") STATUS ====== This behavior is by design. MORE INFORMATION ================ By default, service components are enabled for all Microsoft OLE DB providers. This is because Resource pooling provides high performance in an environment that exhibits frequent connects and disconnects from a backend database. The following table indicates valid connection string values and the OLE DB Services associated with each connection string: +------------------------------------------------------------------------------+ | Value in Connection String | Services Enabled | +------------------------------------------------------------------------------+ | "OLE DB Services = 0" | No Services | +------------------------------------------------------------------------------+ | "OLE DB Services = -1" | All Services (default) | +------------------------------------------------------------------------------+ | "OLE DB Services = -2" | All Services Except Pooling | +------------------------------------------------------------------------------+ | "OLE DB Services = -4" | All Services Except Pooling and Auto-Enlistment | +------------------------------------------------------------------------------+ | "OLE DB Services = -5" | All Services Except Client Cursor | +------------------------------------------------------------------------------+ | "OLE DB Services = -6" | All Services Except Client Cursor and Pooling | +------------------------------------------------------------------------------+ Note that Resource pooling is disabled when OLE DB Services is set to a value of 0, -2, -4, or -6. Steps to Reproduce Behavior --------------------------- 1. Create a program (.prg) file named Adodemo.prg using the following code: PUBLIC myconn, myrs CREATE TABLE adodemo (charfld1 c(10), charfld2 c(10)) FOR i=1 TO 3 INSERT INTO adodemo VALUES (REPLICATE(CHR(i+64),10),REPLICATE(CHR(i+65),10)) ENDFOR CLOSE ALL *!* Create an ADO Connection to the FoxPro Samples myconn=CREATEOBJECT('ADODB.CONNECTION') *!* Need a Client Side Cursor for this so that we get a recordcount myconn.cursorlocation=3 && adUseClient *!* Open the data source myconn.OPEN("DRIVER=Microsoft Visual FoxPro Driver;" + ; "SourceType=DBF;SourceDB="+SYS(5)+SYS(2003) + ";" + ; "Exclusive=NO;BackGroundFetch=NO;NULL=NO;Collate=MACHINE") *!* Create an ADO Recordset to be used as the datasource for the DataCombo control myrs=CREATEOBJECT('ADODB.RECORDSET') *!* Set the connection property of the recordset myrs.activeconnection=myconn *!* The recordset inherits the cursorlocation of the connection myrs.locktype=3 && adLockOptimistic myrs.OPEN("SELECT * FROM ADODEMO",,,,1) FOR i=0 TO myrs.recordcount-1 FOR j=0 TO myrs.FIELDS.COUNT-1 ?? myrs.FIELDS(j).VALUE + " " ENDFOR myrs.movenext ? ENDFOR myrs.CLOSE myconn.CLOSE USE adodemo EXCLUSIVE 2. Save and run the program. Note that when the USE adodemo EXCLUSIVE line is encountered, the following error message appears: File access is denied 3. Open the Adodemo.prg file and change the line that opens the ADO connection from myconn.OPEN("DRIVER=Microsoft Visual FoxPro Driver;" + ; "SourceType=DBF;SourceDB="+SYS(5)+SYS(2003) + ";" + ; "Exclusive=NO;BackGroundFetch=NO;NULL=NO;Collate=MACHINE") to: myconn.OPEN("DRIVER=Microsoft Visual FoxPro Driver;" + ; "SourceType=DBF;SourceDB="+SYS(5)+SYS(2003) + ";" + ; "Exclusive=NO;BackGroundFetch=NO;NULL=NO;Collate=MACHINE;" + ; "OLE DB Services = -4") 4. Save and run the program. Note that the error message that appears in the "Symptoms" no longer appears. (c) Microsoft Corporation 2000, All Rights Reserved. Contributions by John Desch, Microsoft Corporation. REFERENCES ========== For additional information on ADO Resource Pooling, please see the following Web site: http://msdn.microsoft.com/library/techart/pooling2.htm For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base: Q245267 DOC: 'OLEDB_SERVICES = -2' Does Not Disable Autoenlistment Q176056 INFO: ADO/ASP Scalability FAQ Additional query words: OLEDB ====================================================================== Keywords : kbActiveX kbADO kbDatabase kbvfp300 kbvfp300b kbvfp500 kbvfp500a kbvfp600 KbDBFDBC kbGrpDSFox kbDSupport kbCodeSnippet Technology : kbVFPsearch kbAudDeveloper kbVFP300 kbVFP300b kbVFP500 kbVFP600 kbVFP500a Version : WINDOWS:3.0,3.0b,5.0,5.0a,6.0 Issue type : kbprb ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2000.