Database Engine Could Not Lock Table...

[rede96]

Hi, I am having a problem running a make table query which is based on the same tables that I am using in a sub form.

Basically, the user selects certain records in the subform (which is in datasheet mode) using a check box, then once selected he hits a command button where there is some code that will run the make table query, which I use to filter other tables in a related reports.

However I keep getting this silly "database engine could not lock table" message relating to the sub form name. The subform is not linked to any records in the main form as the main form is unbound.

I have searched for similar probmes here, but the solutions offered by putting DoEvents or Me.refresh/Me.requrey in the code do not work.

Can anyone help please?

 

[plog]

Hi, I am having a problem running a make table query which is based on the same tables that I am using in a sub form.

Sounds like a bad idea from the start. Make table queries are red flags. Why do you need to make a table?

 

[jdraw]

I agree with plog that your design sounds suspect.
Could you please tell us more about

....some code that will run the make table query, which I use to filter other tables in a related reports.

Usually, developers design their tables based on requirements. The need for additional tables (via Make Table query) is very rare. But you may have unusual requirements.

 

[rede96]

Sounds like a bad idea from the start. Make table queries are red flags. Why do you need to make a table?

I agree with plog that your design sounds suspect.
Could you please tell us more about your...

Yes, you are probably right but I couldn't think of a different way to do it.

The main form is selectiong various critera to run reports. So there are some sub forms where the usere can filter and select things like Customer, Order Number, Machine etc.

So in the sub form the user has to be able to update a check box against the items they wish to select.

Once the user selects say a Customer, then I use a make table query that will automatically filter the other sub forms so they only contain the data relvent to the chosen customer.

Rather than apply the code to filter the other sub forms, as there could be 100's of orders for example, I use the newly made table and link it to the underlying table in the sub forms record source on the table's ID.

E.g. I Select a Customer from the customer sub form and it makes a table with all the Machine ID's relvelnt just for that customer. In the record source for the sub form for Machines, I link temporary made table to the Machine table on 'Machine ID'.

This then updates in the form so the user can now see just what Machines are relevent to that order, and select further any machine's they wish to use in the report.

By the way I have just figured out a work around. I set each sub form's record source to "" then run the make table queries, then re-populate the record source.

So that seems to work but be great to see if there was a better way to do it.

Thanks.

 

[jdraw]

It would be most helpful if you could describe/tell readers WHAT you are trying to do in plain English. You are describing HOW you have done something -and that how isn't working the way you want- and we don't know what the something is yet.
You do not have to repeatedly create Make Table queries. As plog said -that's a red flag.
You need to describe WHAT, so readers can advise on options for accomplishing HOW.

Hopefully, you realize that readers do not have any experience with your specific issue in your specific environment and are not following/understanding/guessing your business issue/opportunity.

 

[rede96]

Well basically I am trying to design a way for a user to multi-select data from different tables which will then be used to generate a report.

So imagine a simple manufacturing process where 'things' are made for different customers, made on different machines and can have a different order number for each batch made.

The manufacturing data (like times, quantity, dates etc as well as MachineID, CustomerID and OrderNumber) are stored in the Production table. There are three primary tables called Customers, Orders and Machines that have a one to many relationship with the respect ID’s in the Production table.

Before running a selected report the user needs to be able to filter / select which orders, machines and customers they want to appear in the report. But when a user selects say Customer A and Customer D, then I want only the machines and orders that have been produced for Customer A and Customer D to appear for further selection.

It is also similar with Orders and Machines, e.g. if a user selects Machine 1 and Machine 5 then I want only the Orders and Customers that were used on those machines.

All three fields (Customer, Orders and Machines) are first filtered by the date range the user enters. E.g. date of production.

Does that help?

 

[jdraw]

It helps us better understand your environment.
Can you post a copy of your database? ---remove anything confidential ( only need enough records to show some sample tables/queries/forms/reports) ---zip and post.

Can you show us a copy (jpg) of your relationships window?

More questions re your database:

Is this a new development? Are you the only developer?
Is this database in production with production/operational data?
How many users are involved?

Below are links to some info from DatabaseAnswers.org. This may give some ideas of tables and relationships. Because it is generic, it may not meet all of your needs. It is intended to be a starting piece to assist developers - use or change as necessary for your business.

Generic manufacturing data model
and related facts/requirements.

 

[rede96]

It helps us better understand your environment.
Can you post a copy of your database? ---remove anything confidential ( only need enough records to show some sample tables/queries/forms/reports) ---zip and post.

Can you show us a copy (jpg) of your relationships window?

Ok, sure. I am going to be travelling for a few days, so will be next week though if that is ok?

Is this a new development? Are you the only developer?

Yes, I am the only developer but I am not a developer I have designed a number of production type databases over the years, but I am completely self-taught and database development is not my primary role. But where plants have a need for better data capture / analysis I tend to step in as there isn't anyone else.

Is this database in production with production/operational data?

No, the data is populated through an ODBC link to the main shop floor data capture system. However this is an old system and there is no one to develop reports / structure. So this database I am making is just a better way for people get daily reports and analysis.

How many users are involved?

Just a couple of people for this version. But once some standard reports are agreed I will make another version, probably with less functionality for shop floor users of around 20.

Below are links to some info from DatabaseAnswers.org. This may give some ideas of tables and relationships. Because it is generic, it may not meet all of your needs. It is intended to be a starting piece to assist developers - use or change as necessary for your business.

Generic manufacturing data model
and related facts/requirements.

Great, thanks for that. Any help is much appreciated.