Microsoft KB Archive/260856

= PRB: File Access Denied When Opening a Table Accessed by Using ADO =

Article ID: 260856

Article Last Modified on 2/15/2007

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition

-



This article was previously published under Q260856



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=&quot;DRIVER=Microsoft Visual FoxPro Driver;&quot; + ; &quot;SourceType=DBF;SourceDB=&quot;+SYS(5)+SYS(2003) + &quot;;&quot; + ; &quot;Exclusive=NO;BackGroundFetch=NO;NULL=NO;Collate=MACHINE;&quot; + ; &quot;OLE DB Services = 0&quot;)

-or-

lcConnectString=&quot;DRIVER=Microsoft Visual FoxPro Driver;&quot; + ; &quot;SourceType=DBF;SourceDB=&quot;+SYS(5)+SYS(2003) + &quot;;&quot; + ; &quot;Exclusive=NO;BackGroundFetch=NO;NULL=NO;Collate=MACHINE;&quot; + ; &quot;OLE DB Services = -2&quot;)

-or-

lcConnectString=&quot;DRIVER=Microsoft Visual FoxPro Driver;&quot; + ; &quot;SourceType=DBF;SourceDB=&quot;+SYS(5)+SYS(2003) + &quot;;&quot; + ; &quot;Exclusive=NO;BackGroundFetch=NO;NULL=NO;Collate=MACHINE;&quot; + ; &quot;OLE DB Services = -4&quot;)

-or-

lcConnectString=&quot;DRIVER=Microsoft Visual FoxPro Driver;&quot; + ; &quot;SourceType=DBF;SourceDB=&quot;+SYS(5)+SYS(2003) + &quot;;&quot; + ; &quot;Exclusive=NO;BackGroundFetch=NO;NULL=NO;Collate=MACHINE;&quot; + ; &quot;OLE DB Services = -6&quot;)



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:

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
  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 myconn=CREATEOBJECT('ADODB.CONNECTION') myconn.cursorlocation=3 && adUseClient myconn.OPEN(&quot;DRIVER=Microsoft Visual FoxPro Driver;&quot; + ;  &quot;SourceType=DBF;SourceDB=&quot;+SYS(5)+SYS(2003) + &quot;;&quot; + ;   &quot;Exclusive=NO;BackGroundFetch=NO;NULL=NO;Collate=MACHINE&quot;) myrs=CREATEOBJECT('ADODB.RECORDSET') myrs.activeconnection=myconn myrs.locktype=3 && adLockOptimistic myrs.OPEN(&quot;SELECT * FROM ADODEMO&quot;,,,,1) FOR i=0 TO myrs.recordcount-1 FOR j=0 TO myrs.FIELDS.COUNT-1 ?? myrs.FIELDS(j).VALUE + &quot; &quot; ENDFOR myrs.movenext ? ENDFOR myrs.CLOSE myconn.CLOSE USE adodemo EXCLUSIVE  Save and run the program. Note that when the USE adodemo EXCLUSIVE line is encountered, the following error message appears:
 * !* Create an ADO Connection to the FoxPro Samples
 * !* Need a Client Side Cursor for this so that we get a recordcount
 * !* Open the data source
 * !* Create an ADO Recordset to be used as the datasource for the DataCombo control
 * !* Set the connection property of the recordset
 * !* The recordset inherits the cursorlocation of the connection

File access is denied

  Open the Adodemo.prg file and change the line that opens the ADO connection from myconn.OPEN(&quot;DRIVER=Microsoft Visual FoxPro Driver;&quot; + ;  &quot;SourceType=DBF;SourceDB=&quot;+SYS(5)+SYS(2003) + &quot;;&quot; + ;   &quot;Exclusive=NO;BackGroundFetch=NO;NULL=NO;Collate=MACHINE&quot;) to: myconn.OPEN(&quot;DRIVER=Microsoft Visual FoxPro Driver;&quot; + ;  &quot;SourceType=DBF;SourceDB=&quot;+SYS(5)+SYS(2003) + &quot;;&quot; + ;   &quot;Exclusive=NO;BackGroundFetch=NO;NULL=NO;Collate=MACHINE;&quot; + ;   &quot;OLE DB Services = -4&quot;)  Save and run the program. Note that the error message that appears in the &quot;Symptoms&quot; no longer appears.

(c) Microsoft Corporation 2000, All Rights Reserved. Contributions by John Desch, Microsoft Corporation.

<div class="references_section">