Multiple Users can't do Make Table to update data

[Guest]

I have a multiple user database. I'm extracting data via ODBC connection
from our business system. I want the users to update the data a few times a
day and because our Make Table (from the data update) is used on query's and
reports if anyone has a report open that has that data in it, the user gets a
"Could not lock table ..." error.

Is there a way in the update Macro I can close reports on all computers or
get around this?

Thanks

 

[Guest]

1. Don't use a temporary table to create reports.

2. Use one 'work' table that appends and deletes records as needed. Put in
an extra user field and base the reports off of the user creating it.

3. Split the database into front end and back end mdb files and give each
user a copy of the FE. Have the make table query create the table in the FE.

I like option 3 best for a number of reasons. Splitting an Access database
in a multi-user environment is almost always a great idea.

 

[Guest]

Jerry,
Thanks for the help. I've never split a database before. Since all users
will be using the new data that is updated, but only 1 of the 8 users updates
the data, how would I get the data to be on every users FE database. From
what I've read about FE vs. BE it is correct to set them up so every user has
their own FE copy.

The problem is that all 8 use the reports and typically someone leaves the
report open which means our new data can't be refreshed from our business
system and write over the existing table.

Thanks