How to run make table query on an active table used by a form?

[dpatel1682]

I am trying to re-create the existing table once it gets updated via form. However, I am getting error "The database engine could not lock table "tbl_test" because it is already used by another person or process".

Please help me find a way to get around this

Thanks

[NeoPa]

Access has been specifically designed to stop you getting around this, for your own good. Consider what would happen when the operator of the form found that it suddenly crashed due to the record source disappearing.

I suggest you take a step back and consider what you actually want, then look at finding a sensible way to implement that.

[dpatel1682]

@NeoPa
NeoPa,

Once user inputs the data via form, I want to close that form, run the make table query and open the form again but I am getting the "The database engine could not lock table "tbl_test" because it is already used by another person or process" error. Can you please suggest a way around?

Thanks

[dsatino]

Why are you remaking a table that you just added data too? Sounds, in general, like you're trying to an easy task the hard way.

[dpatel1682]

@dsatino
that table is a crosstab query off of the source table which gets updated via form. I am having a groupby/sum issue which takes place in crosstab query. Therefore, I need to re-create that table.

Please advise

Thanks

[dsatino]

Ok then the next question is why are you making a table when you already have a query that will give you the same data without taking up memory?

Sorry for the questions, just pieces of the puzzle...

[dpatel1682]

@dsatino
Because data is not in a crosstab format so I have to create that table first and then have users update the data in a newly created crosstab table via form.

[NeoPa]

dpatel1682: Can you please suggest a way around?

Please refer to my earlier post for an answer to that question.

Luckily for you, it seems that Dsatino is leading you towards something that does make sense. I suggest you grab this opportunity with both hands. What he says makes sense.

[dsatino]

So your oder of ops goes like this?:

1. User inputs to form
2. Form updates some table (non-crosstab)
3. Form closes
4. Make-table query makes a crosstab table based on a crosstab query
5. Form re-opens

[dpatel1682]

@dsatino
I did that and that's when I am getting an error "The database engine could not lock table "tbl_test" because it is already used by another person or process".

Thanks

[dsatino]

@dpatel1682
Ok, I was actually asking you if this is the order you are using. By your response I'm assuming that it is.

The next question is what is the crosstab table being used for?

[dpatel1682]

@dsatino
Crosstab table is being used to sum the data based on the period field. In other words, it display the data in summarized manner

[dsatino]

Yes, but display it where? A form, report...

[dpatel1682]

@dsatino
it display it in the form and row source is that table which is created from crosstab query. User update that form and after update it updates the value in source tables

[NeoPa]

dpatel1682: User update that form and after update it updates the value in source tables

You've seen that happen? I'm surprised. I would not expect any updates to be possible on a form bound to a CrossTab query. The query wouldn't be updatable.

[dsatino]

So the same form takes the user input and displays the crosstab data?

[dpatel1682]

@NeoPa
NeoPa,

Form is bound to a table which is created by that crosstab query which gets updated in the process with the source table which creates that crosstab table. Having said that, I need re-create that crosstab table since the source table was updated via form.

Relationship defined:

1. TableA(SourceTable)
2. QryA(CrosstabQuery) that gets data from TableA
3. TableB(CrosstabTable) that gets data from QryA
4. FormA that has TableB as source
5. Edits to FormA updates TableA - This will require me to re run the QryA which creates TableB

Hope I made myself clear and Thank you all very much for your help

[dpatel1682]

@dsatino
Yes, same form takes the user input and displays the crosstab data.

Here's the steps defined to make this more clear

Relationship defined:

1. TableA(SourceTable)
2. QryA(CrosstabQuery) that gets data from TableA
3. TableB(CrosstabTable) that gets data from QryA
4. FormA that has TableB as source
5. Edits to FormA updates TableA - This will require me to re run the QryA which creates TableB


Thank you all very much for your help

[dsatino]

Is there any way you can zip the file and attach it?

[dpatel1682]

I am sorry I can't send the file. Is there any other way that I can explain myself better

Thanks a lot for your help

[dpatel1682]

@dsatino
I am sorry I can't send the file. Is there any other way that I can explain myself better

Thanks a lot for your help

[dsatino]

How about a screen shot of 'FormA' that might help me see the gist of what you're doing

[NeoPa]

dpatel1682:
4. FormA that has TableB as source
5. Edits to FormA updates TableA - This will require me to re run the QryA which creates TableB

How can step 5 talk about updating TableA when step 4 indicates FormA is bound to TableB?

[dpatel1682]

@NeoPa
NeoPa,

I am running vba afterupdate event when FormA gets updated, based on the account # it updates TableA.

Hope this makes sense.

Thanks

[dpatel1682]

@dsatino
FormA is a datasheet which filters the databased on the user selection from the listboxes above

[dpatel1682]

@dpatel1682
Screenshot attached in the main post

[dpatel1682]

@dsatino
Here you go

[dsatino]

Is this a form with a subform?

[dsatino]

I'm guessing at what's going behind this form which I think is either:
a main form bound to tableA and subform bound to tableB
or
the form is bound to tableB and edits via the form are in unbound controls that update tableA

Either way, constantly remaking the table is not good form. I can't remember the last time I bound a form to a crosstab, but I think the following is generally what you need to do:

The form can be bound to the crosstab query in the same manner that it can be bound to the table. Instead of remaking a table you can simply bind your form to the query and use the .Requery method. For a crosstab, the best way to do this is to go into the query properties and set the column names, but since you're pivoting on dates I'd assume they are variable. In this case you'll need some VBA in which you open a recordset based on the crosstab and use the field index to name the columns before you requery.


There may be some rust in there, but hopefully it will point you in the right direction. As NeoPa first stated, you don't want to remove the forms recordsource.

[dpatel1682]

Subform/Datasheet is bound to tableB (which is created by a crosstab query) and TableA is being updated using VBA sqlstring. Is there any way I can close the form once I close it, I recreate the table? I tried doing that using docmd.close and docmd.open method but when I try to run the macro which recreates the TableB, it gives me the error mentioned before. Any way to clear the MS Access memoro / unlock that table?

Thanks

[dpatel1682]

Subform/Datasheet is bound to tableB (which is created by a crosstab query) and TableA is being updated using VBA sqlstring. Is there any way I can close the form once I close it, I recreate the table? I tried doing that using docmd.close and docmd.open method but when I try to run the macro which recreates the TableB, it gives me the error mentioned before. Any way to clear the MS Access memoro / unlock that table?

Thanks a lot for your help

[dpatel1682]

Subform/Datasheet is bound to tableB (which is created by a crosstab query) and TableA is being updated using VBA sqlstring. Is there any way I can close the form once I close it, I recreate the table? I tried doing that using docmd.close and docmd.open method but when I try to run the macro which recreates the TableB, it gives me the error mentioned before. Any way to clear the MS Access memoro / unlock that table?

Thanks a lot for your help.

[dsatino]

Memory really doesn't have anything to do with it. More than likely the error is caused because your code is in the form module and if you think about, the form really isn't closed if you're running code out of it. And if the form is n't closed, then anything bound to it will be locked.

Mainly I think you need to realize that the error is caused by the fact that you're trying to operate outside of a 'best-practice'. I'm sure there are ways around it, but if this is a sizeable table then you're bloating you're database everytime you do this and causing the DB to creep ever closer to it's size limit for the sake of adding one record. This affect will grow with every record. You'll also preclude this form from ever being used in a multi-user environment.

There are usually several ways to code something, some better than others and it's ok if you get it working with a less desirable method. But sometimes there's a case where the method is just the wrong way, even if you can get it to work. This is one of those cases.

[dpatel1682]

My table is really small ~4000 records and I am updating everytime user click refresh button. Any other way to do this by staying in the same form? I know I can have another form pop up for the update and once it gets updated I can display the result in the 1st form via query as a source of that datasheet. But I do want to avoid going into another form for the updates

[dsatino]

Without seeing it directly, I can't really say what to do. Attached is a DB that has what I think you are trying to do.

Open the form called "TestForm_wSubform". The main form is bound to a table in which you can add new records. The subform is based on a crosstab QUERY that will update when you click on the button.

They're linked on the Branch field so you'll have to use a preexisting branch number to see it work

[dpatel1682]

Here's the db that I have with truncated data. Please let me know if I can explain it to you futher

[dsatino]

Sorry, I only have Access 2003 so I can't view this. You may be able to convert it though on your end and resend.

[dpatel1682]

Here you go. Thank you so much for your help

[dsatino]

What parts of the form is the user updating specifically

[dpatel1682]

last subform with 2 lines

[dsatino]

Oh boy. I'm not sure you fully understand what you're getting yourself into here.

From the looks of it, you're trying to build an accounting database from scratch. That would beg the question as to why? You can just buy those in the box and many of them can be accessed via a program like access if you need deeper analysis.

All that aside, your underlying tables are not normalized nor do they appear to be referential. You have no primary keys.

Now for the form. It appears that you didn't really plan it out, but just started building and now you've finally gotten to a point where you can't get something to work and rather than stepping back and reassessing what you've got, you're just trying jam it through. Basically you want to allow the user to directly update the summary data which fires a process that edits the detail which requires the users view to be deleted and remade. Your allowing the user to edit the results directly but making the program run in circles.

Believe me, my programming knowledge is all self-taught so I've been down the road you're trying to follow. Do yourself a huge favor and do some research on the fundamentals of databases, vba, and SQL.

Your SQL, as it is, will update every record that matches the account number criteria. So if change the Name of PSTest, your SQL is also going to change the name of TES. Now you've got two identical accounts in your account table and all your results will double. Be careful with SQL. You can write a lot of things that will return unintended results.

Also, you'll need a decent amount of VBA if you're column headings are going to change every six months. Otherwise you'll never acheive full automation and you'll be constantly updating anything that references the current dates that you're using.

[dpatel1682]

I agree with you comments regarding doing everything against the standard ways. I had PK and referentials before I received the data and when I looked at the data, I was forced to kill all the PK and Referentials. After reviewing the DB, is there any way to achive what I am trying to do on the same form?

Please advise

Thanks

[dsatino]

Anything is possible, but in your case it's going to be difficult.

Your original problem/error is caused by the fact that your form is bound to the table you're trying to delete. You've closed the form visually, but since your code is in this form, the form is still open and hence the table is locked by the form.

What makes your problem difficult to solve is the fact that your process is circular and has no end point.

Table A
Feeds xTab Query
Makes Table B
Bound to Form
Edits to Form change Table B
Which calls process to update
Table A
...

Start by getting your update procedure out of Form and into it's own module. Once you do that, find a way to call the process without the form being open and it'll probably work.

[dpatel1682]

I am kind of unsure in what do you mean by "Start by getting your update procedure out of Form and into it's own module. Once you do that, find a way to call the process without the form being open and it'll probably work"

Thanks a lot for your help

[dsatino]

All of your code is behind forms(CBF). There's nothing inherently wrong with that, but in this case it needs to be outside the form in it's own module.

More specifically:

User updates data in your form
This fires the controls After_Update procedure which tries to run the code you wrote.
The code is failing because the table you're trying to update is bound to the form that is running the code.

If you put this update procedure in it's own module, you can close the form and call the procedure. This will probably allow you to do what you want.

[dpatel1682]

Can you please provide an example? I am still bit lost as to how do I do this. What I tried to do after the update is click on the "Refresh" button which will close the form, run the make table qry, and re open the form. Should I put the above steps in module and run that module on click ?

[dsatino]

Yes, move all the code from the click event(except the form close command) into a module.

On_Click close the form
In the forms on_close event, call the procedure that you moved to the new module.

I think this will work, but I've never tried it

[dpatel1682]

ok I tried that but it didn't work. I created a module with a function called mktbl which had maketable qry followed by open form command. It gave me the same error when it tries to execute maketable qry

Any other idea?

Thanks for your help

[dsatino]

Clear the on click event from the button and paste this into it:

Me.tbl_Raw_Data_by_Account_subform.Form.RecordSource = ""
DoCmd.RunMacro "mcr_mktbl_Raw_Data_by_Account"
Me.tbl_Raw_Data_by_Account_subform.Form.RecordSource = "tbl_Raw_Data_by_Account"
Me.Refresh
Me.Requery

It will get you buy the error, but this is really frowned upon.

[dpatel1682]

It worked. I used the following Code

tbl_Raw_Data_by_Account_subform.Form.RecordSource = ""
 
DoCmd.RunMacro "mcr_mktbl_Raw_Data_by_Account"
 
tbl_Raw_Data_by_Account_subform.Form.RecordSource = "tbl_Raw_Data_by_Account"
 
tbl_Raw_Data_by_Account_subform.Form.Refresh
 
tbl_Raw_Data_by_Account_subform.Form.Refresh