Multiple Users locking DB when running DB2 Queries.

[Bre035]

I have created an Access DB using 2003 version, split the DB with Front End and Back End. I placed the Front End on Network Shared folder also. However, the users of the DB are using versions 2003, 2007 and 2010, everyday someone locks the database when running the queiries that access DB2 Table to perform the Access Make Table queries.

[Rabbit]

If you're just reading the data and not making changes to the data, then you can either set the currentdata option to no or use a read only view.

If however you are modifying the DB2 data or need the most current data, then you have no choice. The table must be locked to read the correct data.

[Bre035]

The DB2 queries are retrieving data from DB2 Tables, but populating a Access table. The queries are Make table queries.

[jimatqsi]

Everyone should have their own copy of the front-end. Do not share it like that for running over the network. Have everyone copy the front-end to their own machine.

[Rabbit]

The DB2 queries are retrieving data from DB2 Tables, but populating a Access table. The queries are Make table queries.

Then you can use the options I laid out in my first paragraph.

[jimatqsi]

If the queries are populating Access tables that need to be shared with other users, then you have to make the tables in either the DB2 database or another Access database that is accessible to everyone on the network - just like the DB2 is. The properties of a query allow you to specify what database the make table should be in. They output of a Make table query does not have to be the local Access database.

In the query editor, click on the top part of the screen and look at the properties for that query. You'll see where you can specify the target database.

The front-end should have programs (forms, queries, modules) and local tables only. Trying to have everybody in the same copy of the front-end will give you exactly the problem you are reporting.

Jim

[jimatqsi]

Maybe those output tables don't have to be shared. Why would multiple people need to create the same table. They would obviously overwrite each other's work.

If you're not going to use local tables for that, maybe you should be using an append query instead of a make table query.

Jim