Overwrite a Table while it is in use

  • Thread starter Thread starter Author
  • Start date Start date
A

Author

Several users need to view a report. Originally the report ran a five-minute
query each time it was opened, so to save time I designed a Make-table query
to write data to a table. The query filters data, overwrites the table, and
repeats itself. The problem is, when someone has a report open and the query
attempts to overwrite the table, I get:
Runtime Error 3009
You tried to lock table 'NewData' while opening it, but the table cannot be
locked because it is currently in use. Wait a moment, and then try the
operation again.
And the VBA code halts. This is a problem, as each user who runs the report
will be viewing data from a common table. If I coded the report itself to
run the Make-Table query, it would work. But only if one user at a time had
the report open. I'm stuck.. Help?
Using Access 2003
 
You really, really need to split this database, so each user has a local
copy of the front end. You can then do what you want, e.g. create a table in
the front end, populate it with the data the user needs, and run the report
from there.

If that's a new concept, see:
Split your MDB file into data and application
at:
http://allenbrowne.com/ser-01.html
 
I'm approaching the seventh level of confusion.
As a concept, I get the database split (after reading the link you provided
and other websites)
My confusion stems from not understanding something about linking tables.
My Front End will run a query which refreshes table data (Query is an
application so should be in the FE, right?). That data should be stored in
the Back End (Data in the BE). Then Users should run a report from the FE
that reads the data stored in the BE.
If above statement is correct then point me in the right direction of how to
proceed with linking.
Else call me a ninny and wish me a good day.
Thanks!
 
The back end contains your shared tables only.

The front end contains the linked tables, and all other objects (queries,
forms, ...) You can use the Linked Table Manager if you the back end is
moved somewhere else.

The front end can also contain the temporary tables that are relevant to the
current user only. So you can copy the relevant records into the temporary
table in the front end, and this is independent of any other user (who has
their own temporary tables in their front end.)
 
I mostly get it. After reading and tinkering, I've gotten my split database
to behave. Now, is there VBA code or other logic by which I can have the
linked tables refresh either continually or on a schedule? Currently I'm
clicking
Tools>Database Utilities>Linked Table Manager>Select Table> click OK
 
Solutions.mdb contains a module that illustrates how to do the relinking.
You can grab it from here:
http://www.mvps.org/access/downloads/bapp2000.exe
http://support.microsoft.com/kb/248674

Relinking is just a matter of looping through the TableDefs that have
something in the Connect property, and assigning it followed by the refresh
link. Most of the code in the above is the API call to open the FileOpen
dialog if the back end is not found, so the user can identify it.
 
Back
Top