Update linked Excel file using VBA

[jbdevon]

Hello Team,
I am new here. I have one access db with few linked files ( excel and text ). The text file has over 100K rows ( detailed data ) , so I am using interim access table/query to group it and dump into Excel file, which is then again linked to a form in the same DB. ( Reason for this is to easily debug data in excel vs text, so ple. ignore the processing here ).

dumpData.txt
processedExcelFile.xls

I want to give user an ability to do on demand processing, just in case if they have more than one updates in a day. So, I added button on form and in macro I am doing following steps.

1. Close the form ( so linked Excel file is not actively locked )
2. Empty out temporary processing tables in Access.
3. Import text file into that temp table.
4. Drop the linked excel file ( using DoCmd.Deleteobject acTable, "processedExcelFile.xls" )
5. Dump query output (grouped for some criteria ) into Excel file using
DoCmd.TransferSpreadsheet acExport, excel....
6. Relink Excel file into Access as table using DoCmd.TransferSpreadsheet acLink , ...

Problem I run into is step 5, when I am trying to export the query output into excel file, it gives me

"The database engine could not lock table <name> because it is already in use by another person or process. (Error 3211)"

I cannot figure out where is the excel file being locked..
Can anyone please help me ?
Regards,
JB

 

[Trevor G]

Jb welcome to the Forum,

Would you be able to consider deleting the excel file completely and then rebuild it with code.

What I do is check to see if the workbook exist if so then delete it then rebuild so I am using the kill command.

See sample below:

If Dir(CurrentProject.Path & "\Report.xls") <> "" Then Kill CurrentProject.Path & "\Report.xls"

 

[jbdevon]

Sure, I can delete the file, but what I am not getting is how/what is locking the file, even if i have deleted the temp table and refreshed the tabledef collection.
this is really affecting the solution, because i have to implement this in 3 other similar processes.

Thanks,
JB

 

[ghudson]

Instead of working with a linked excel file, I would import the excel data into an access table.

 

[jbdevon]

That is possible, how ever I have not tried to import excel into yet another table. The reason for having excel file is if there is any data issue, it becomes easier to fix there, vs in Text file ( cumbersom to find trouble record ) or in Access where user may not have necessary skills.

So having this intermediate excel approach is better which is a linked file, so once the issue is fixed, there is no impact on Access process nor on raw text file.
thank you,
JB