UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Table Locked After Export, Access 2013    
 
   
bfras914
post May 18 2017, 09:22 AM
Post#1



Posts: 23
Joined: 22-January 09
From: Connecticut


I have a button on a report that exports the report's source table to Excel. The code for that button is below. The button itself works just fine. The problem I'm getting is that after exporting, if I close out of the report and then try to do something to that table, I get an error saying the table is in use by another process.

Specifically, after exporting the table to Excel, I close the report with the export button on it, and that brings me back to another form with a series of text boxes and combo boxes to enter selections and run a new version of the report. There's a button on this form, with VBA to read the user input, feed it into the SQL of a Make-Table query, and that Make-Table query replaces the data in TBL_CustList and opens the report mentioned above. But if I have already previously exported TBL_CustList via the button on the Report, I get Runtime Error 3211 - "The database engine could not lock table 'TBL_CustList' because it is already in use by another person or process.

This is a local database on my computer's hard drive, so there are no other users that could be touching it, which I assume means the table is somehow in use by another process. Like somehow I haven't "released" it properly after exporting it to Excel.

My trouble-shooting so far has determined that if I run the code from the user input form to view the Report, then close that Report WITHOUT exporting the data to Excel, I am able to enter new criteria on the form and run it again with no problem. So whatever the lock issue is, it seems to happen as a result of exporting to Excel using the below code. The code seems simple enough to me, so I'm officially stumped on what's locking my table here. Any advice would be greatly appreciated!

CODE
Private Sub btnExcel_Click()
    On Error Resume Next
    DoCmd.OutputTo acOutputTable, "TBL_CustList", acFormatXLSX, , -1
    If Err.Number <> 0 Then
        If Err.Number = 2501 Then
            Exit Sub
        Else
            MsgBox Error
        End If
    End If
    
End Sub
Go to the top of the page
 
GroverParkGeorge
post May 18 2017, 09:30 AM
Post#2


UA Admin
Posts: 29,499
Joined: 20-June 02
From: Newcastle, WA


When this happens, open Task Manager and look for one or more hanging instances of Excel. I'll bet you find them.

When you automate Excel, you have to be careful to close what you open.

It looks like what you might be doing there is auto starting Excel: DoCmd.OutputTo acOutputTable, "TBL_CustList", acFormatXLSX, , -1

And that would be the reason.
This post has been edited by GroverParkGeorge: May 18 2017, 09:32 AM

--------------------
Go to the top of the page
 
bfras914
post May 18 2017, 09:38 AM
Post#3



Posts: 23
Joined: 22-January 09
From: Connecticut


That doesn't seem to be the case. To make sure, I closed all open Excel windows, checked Task Manager to confirm there were no more EXCEL.exe processes open, and then tried my Access process again. I checked Task Manager after running my output code the first time, and did not see any Excel instances there. Then I tried to re-run the make-Table code and still go the same error.
Go to the top of the page
 
GroverParkGeorge
post May 18 2017, 09:42 AM
Post#4


UA Admin
Posts: 29,499
Joined: 20-June 02
From: Newcastle, WA


"...make-Table code ..."

I don't see that bit. I see only the outputto Excel.

Apparently this is happening prior to the export to Excel part. Are you opening this table in design view somewhere in code?

--------------------
Go to the top of the page
 
bfras914
post May 18 2017, 09:50 AM
Post#5



Posts: 23
Joined: 22-January 09
From: Connecticut


Sorry, I didn't actually include that other bit of code because I'm fairly certain it's not the "cause" of the problem, although it is relevant since it's part of the overall process. Let me take a step back and give you some more details.

Step 1: I enter data into a series of text/combo boxes on an unbound form. There is a button on that form that uses the data I input to update the SQL of a Pass-Thru Query. There's a ton of code there, as I have several different options for filtering on about a dozen fields, so I won't post all the code that updates the SQL (as that part works fine every time). After taking all of that input, the next bit of code on the button is:

CODE
strSQL = strSELECT & " " & strWHERE
    db.QueryDefs("PT_ShipToDetail").SQL = strSQL
    DoCmd.OpenQuery ("MAKE_CustList")
        DoCmd.OpenReport "RPT_CustList", acViewReport


MAKE_CustList is a Make-Table Query in my database that simply says "SELECT PT_ShipToDetail.* INTO TBL_CustList FROM PT_ShipToDetail;" I honestly don't remember why I initially designed this to dump everything from my Pass-Thru query into a Table and use the table as the record source for my Report later. That seems like an unnecessary step in hindsight, but I'm not sure it's relevant...

Step 2: After that code completes, I'm now looking at the Report "RPT_CustList." There's a button on this report that contains the code from my original post (With the DoCmd.OutputTo acOutputTable, "TBL_CustList", acFormatXLSX, , -1). I click this button and am able to successfully export the data from TBL_CustList.

Step 3: I close the RPT_CustList, which brings me back to the same form I was on in Step 1. I change the values in some of the text/combo boxes to run the same report on a different set of customers based on this new input. I click the button to update the SQL and run my new report. This is where I get the error about the table being in use by another process. If I try to debug, it's hung up on the DoCmd.OpenQuery ("MAKE_CustList") line.
This post has been edited by bfras914: May 18 2017, 09:54 AM
Go to the top of the page
 
GroverParkGeorge
post May 18 2017, 09:54 AM
Post#6


UA Admin
Posts: 29,499
Joined: 20-June 02
From: Newcastle, WA


Hm, That should replace the table each time it runs. I would do this a bit differently, perhaps.

Try a "flush and refill" approach instead. Use a delete query to delete all records in the table, then an append query to insert the new set of records from the Passthru. See if that makes a difference.

You're probably also experiencing some database bloat from replacing that table over and over.

--------------------
Go to the top of the page
 
RAZMaddaz
post May 18 2017, 09:59 AM
Post#7


UtterAccess VIP
Posts: 9,369
Joined: 23-May 05
From: Bethesda, MD USA


QUOTE
You're probably also experiencing some database bloat from replacing that table over and over.


Then might I suggest a "Compress on Close" in the Access - Options, "Current Database" area?

RAZMaddaz
Go to the top of the page
 
GroverParkGeorge
post May 18 2017, 10:11 AM
Post#8


UA Admin
Posts: 29,499
Joined: 20-June 02
From: Newcastle, WA


Assuming this is a single-user database, that's a viable option.

--------------------
Go to the top of the page
 
bfras914
post May 18 2017, 12:04 PM
Post#9



Posts: 23
Joined: 22-January 09
From: Connecticut


Question on the "Compress on Close" option... Does that mean it will compress when the database (not individual objects) is closed? If so, I don't think that will solve my problem. If I close and re-open the database, everything works fine. I have this issue only when I run the code for the Make-Table query, then run the code to export the table to Excel, then try to run the Make-table code again. My only real work-around so far has been to close and re-open the database before trying to run it for a second time.

I thought about the "flush and refill" method instead of a make-table query. I will give that a try, but I still feel like the "locking" issue is a result of the export to Excel process, NOT the table creation process. I may be wrong, but the reason I'm thinking that way is... I can run the code on the form - which updates the SQL for the make-table query, runs that query, and then opens the report that uses the table as its record source - and then if I simply close the report (without exporting to Excel) to go back to my form, I'm able to change my query criteria and run it again with no problem. I get the locking issue ONLY if I export the table to Excel and then try to run the code that does the make-table query a second time.
Go to the top of the page
 
bfras914
post May 18 2017, 12:13 PM
Post#10



Posts: 23
Joined: 22-January 09
From: Connecticut


Well, I'm not totally sure WHY it worked, but switching over to the "flush and refill" method did the trick. Thanks for the suggestion, GroverParkGeorge!! This had been driving me nuts, having to close and re-open the database all the time for what seemed like no reason at all. Now I can move on and find something else to go crazy over.
Go to the top of the page
 
RAZMaddaz
post May 18 2017, 12:30 PM
Post#11


UtterAccess VIP
Posts: 9,369
Joined: 23-May 05
From: Bethesda, MD USA


"Question on the "Compress on Close" option... Does that mean it will compress when the database (not individual objects) is closed?"

It sounded like you are constantly deleting and adding a lot of data to your database. If that is the case, the size of the database/file is just going to get much larger all the time and might not run as fast too. So, a good thing to do is simply change the setting of the file, so that when the database closes it makes the database smaller by removing all the empty spaces between the remaining data and will make the database run better, stronger, faster too. ohyeah.gif

Note, you can also do this manually and not change the setting. Either way, you will notice the size of the file will get much smaller.

RAZMaddaz
Go to the top of the page
 
GroverParkGeorge
post May 18 2017, 02:53 PM
Post#12


UA Admin
Posts: 29,499
Joined: 20-June 02
From: Newcastle, WA


My theory is that the Make Table action leaves the table in a state that indicates one or more records are locked. Why, I can't say. I've never encountered that particular problem, and haven't seen it reported.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    27th May 2017 - 02:55 AM