I am using the attached code to link to an Excel Spreadsheet from Access 2007.
The user selects the Excel file to look at and review its contents. He can then clear the display and select another. He keeps selecting and reviewing the contents of Excel files until he finds the one he wants, and then clicks a button to proceed.
The problem occurs when, after reviewing an Excel file, he clears the display, but then goes back and selects the same file again. The "File_Is_Locked" function cannot access the file a second time because "it is open by another process". Somehow, even though I have deleted the TableDef, the Excel file is still marked as opened.
Even if I just try to open the Excel file from the desktop, I cannot because "it is open by another process".
How can I mark the Excel file as closed after I am done with it?
This is the progression:
1. First, I use "File_Is_Locked" to make sure the file is not already open by Excel or some other process.
2. "Delete_Temp_Whatever" deletes the TableDef in case some other process happened to not delete it when it was done using it.
3. "Set tbl = db.CreateTableDef..." successfully establishes the link to the Excel file.
4. "Delete_Temp_Whatever" deletes the TableDef because we are done using it.
Private Sub MyHandler()
g_strExcelTabName = "Rules$A1:C1"
g_strLinkedTableName = "Temp_Whatever"
Set db = CurrentDb()
Delete_Temp_Whatever 'In case some other process happened to not delete it
Set tbl = db.CreateTableDef(g_strLinkedTableName, _
dbAttachSavePWD, g_strExcelTabName, "Excel 12.0 Xml;HDR=NO;IMEX=1;ACCDB=YES;Database=" & g_strFullPathExcelFileKT & _
Delete_Temp_Whatever 'Get rid of the temporary table
Public Sub Delete_Temp_Whatever()
On Error GoTo Delete_Temp_Whatever_Error_Recovery
DoCmd.DeleteObject acTable, "Temp_Whatever"
Function File_Is_Locked(strFileName As String) As Boolean
On Error Resume Next
' If the file is already opened by another process,
' and the specified type of access is not allowed,
' the Open operation fails and an error occurs.
Open strFileName For Binary Access Read Write Lock Read Write As #1
' If an error occurs, the document is currently open.
If Err.Number <> 0 Then
File_Is_Locked = True
File_Is_Locked = False