I have a macro that stopped working after I split a database, would love to know how to get it going again.

I am working on a medical office database, the doctor prescribes the medicine, then a pharmacy tech puts it in a bottle. Once it is in the bottle, the pharmacy tech clicks a box in the program saying "fulfilled". There is a button on the form to run a macro that then updates all the drug totals in the system.


Originally, the macro would create a new table with a query(technically overwriting the previous one). This query then populates itself with all the new balances since the last time the script was ran. (timestamp verification). It throws this data into a temporary table. Then a second script runs with the code "tmp_NEW_BALANCE!NewBalance" that takes the new balance from the temp table and puts it in the full table.

But all info is now in the BE and it is not finding the tables appropriately. How do i tell the queries to find the correct table and do their magic. Do i hard code the location in? (\\servername\folder\accessfile) or is there a button i can tell it to update the link table and not run the query locally?

thank you

Hi,



What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

How exactly did you "split" your database? Also, creating temporary tables may not be necessary. Take a look at Allen Browne's website for this article:

Inventory Control: Quantity On Hand

Just my 2 cents...

access 2010. Used the split database feature in Access 2010.

Also I am reading the inventory control article on the dsum() function and it seems like it might cause problems. The doctors prescribe medicine based on the quantity on hand. So there is a drop down box that lists all the meds in the different dosages and then it lists the quantity in the drop down box. wouldnt dsum() be calculated endlessly at that point?

I'd prefer the old solution since i know that works, but if there is a better way I am not opposed.

My guess is that the database split process did not identify all the BE tables that need to be linked in the FE. If all the tables in the BE are linked, it doesn't matter where they actually reside. There should never be a need to make an explicit reference to a BE database. All that should be managed through the Linked Table Manager.