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

[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