Trouble Copying Report/Query... Help!

[pcb121055]

Hi Experts.
I am working in Access 2000 (9.0.3821 SR-1) and have been given the task of adding to an existing simple database. The addition includes two reports and their underlying queries (no additional modules). They read three linked tables. I copied/pasted/renamed similar ones and did necessary edits per what I found on Office.com. In design mode, my reports and queries work as needed.
Problem is... when I try to open the database, I get an error message: "Error accessing file. Network connections may have been lost.” Any idea what might be wrong? Are there registration or connective procedures I might have neglected by copy/pasting existing objects? Thanks for any help.

[Movian]

It may be that it is trying to connect to the linked tables still. just because you have a copy of the table in the DB now it doesn't mean its not still trying to connect to the old ones.

Make sure you have removed the linked tables for your testing purposes. Then you can add them back in for production when you remove the ones you created.

[X-BRichard-X]

I will give you a conceptual answer to your problem. For me personally, I DESPISE working with linked tables in databases, for exactly the reason that you outlined in your problem statement.

The problem with linked datasets in MS Access is that if the target database moves, or if the database name changes, it can cause problems accessing that data and therefore throw an error which crashes the database. Using MS Access's Link Manager can resolve the problem but I would rather use a much more effective solution.

The solution is to autogenerate all of your tables from the onset based on an event procedure like a button click or some other parameter user response.

Once all of your tables have been autogenerated within your solution, you bypass the link problem altogether. This is managed by setting up an independent table within your database that contains records consisting of your database name, file path, whether the database is password protected, etc. Then, call an ado recordset in memory and iterate through the table of file paths to copy the datasets into MS Access tables.

This solution is also an eloquently scalable solution, in that it's easy to add to or remove datasets from the solution by simply adding or removing records from the table itself.