[AccessD] The microsoft access database engine can not open or write to the file

Doug Murphy dw-murphy at cox.net
Mon Apr 16 22:31:27 CDT 2012


Folks,

I have an application that was developed in Access 2002 that has been running as a runtime in that environment for years. One function in the application is an import data from excel files. The method I use is to create a querydef that attaches to the file and allows me to open it. In the routine the user uses a file selection dialog to select the file and then the existing querydef is deleted and the new one is created with the file name and path. I then run code against the query which has always worked.

I opened the application in Access 2010 and the functionality failed. In stepping through the code I delete the old querydef, create the new one and then run a dCount against it to get a record count. The DCount call generates the error message:

'I:\Access Work\AUGSD Presentations\Excel Import\VendorsList3.xls'. It is already
 opened exclusively by another user, or you need permission to view and write its
 data. at Line Number = 0﴿ in procedure FillTable of VBA Document
 Form_frmImportVendorExcel2

While the code is running in debug mode I get a similar message if I try to open the new querydef from the query window. After I step out of the code the query opens fine. Any suggestions on how to release whatever seems to have exclusive control of the query? The code that does the above work follows:

      With CurrentDb
        .QueryDefs.Delete CurrentDb.QueryDefs("qryVendorExcelImportAttachToExcel").Name
        Me.subCtlDisplay.Requery
        sSQLExcel = "SELECT " & sSheetName & ".*,* FROM " & sSheetName & " IN '" & sPath & "'[Excel 8.0;];"
        Set qdfTemp = .CreateQueryDef("qryVendorExcelImportAttachToExcel", sSQLExcel)
        .Close
      End With

Any insight into this problem is appreciated.

Doug




More information about the AccessD mailing list