PRB: Error Opening an ADO Recordset from a File (294424)

SYMPTOMS

When you attempt to open an ActiveX Data Objects (ADO) Recordset object using a file which is already open for writing as the Recordset source, the following error may occur:

Run-time error '-2147024864 (80070020)':

The process cannot access the file because it is being used by another process.

This error does not occur in Microsoft Data Access Component (MDAC) version 2.5 or earlier.

CAUSE

With MDAC 2.5 Service Pack 1 (SP1) and later, the Open method of the ADO Recordset object does not allow you to open a file that is already open for writing. This change was made to prevent problems with concurrent access.

RESOLUTION

You can work around this problem in the following ways:

Make sure that the file is not opened for writing before you attempt to access it through ADO.
Make a copy of the file and open the copy through ADO.
If you are using ADO to access an XML file, you can also use the XML Document Object Model (DOM) to load and parse the XML data from the file.

MORE INFORMATION

The following Microsoft Visual Basic code attempts to open an ADO Recordset using an already open XML file as the source. (This file was previously saved using the Save method of the Recordset.)

Dim xmlFilePath As String
xmlFilePath = "d:\databases\authorsADO.xml"

'Open a file for writing. Note that if the file is opened for
'input only, or if the file is closed before the Recordset.Open
'call below, the problem does not occur.
Open xmlFilePath For Append As 1

'Open an ADO Recordset based on this same file.
Dim rs As New ADODB.Recordset
rs.Open xmlFilePath, , , , adCmdFile

'If successful, display the first field value.
MsgBox "Success! First field value is : " & rs.Fields(0)

'Close the file.
Close 1