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]

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.

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]

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.

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]

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...

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]

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

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]

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

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]

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?

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]

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

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.