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

Doug,

First thing I would check is the trust centre and check you have the permissions correct.  Access 2010 is very touchy about these details.  I would check it for both Access and Excel and ensure you have full permission access to the folders where the database is saved.

Cheers
Darryl.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Doug Murphy
Sent: Tuesday, 17 April 2012 1:31 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] The microsoft access database engine can not open or write to the file

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


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com