Question Complicated Database Copying Question *Holds Breath*

[Bill Bisco]

Hello All,

My users have requested a complicated feature that I'm not exactly sure how to deliever. They would like to have the ability to make a copy of the current state of the database (i.e. all the values of the backend). And have a copy of the front end whose tables are linked to the copied backend.

Is there a current example of someone who has already done this?

Sincerely,
Bill

 

[Banana]

Can I ask you what is the reasoning behind this? I'm genuinely curious.

But to answer your question, one way I can see doing is to copy the backend, then relink to the new copy backend. This can be done via VBA without users' intervention as well, though if your backend is quite big there may be locking issues (maybe not but I don't know for certain that copying the file will lock the entire file)

 

[gemma-the-husky]

not quite what you want but either

a) just do a filecopy of the backend with vba or
b) do a maketable query of each linked table in the current database, to take a "snapshot" of the database

 

[Banana]

Now, a maketable query (or at least an append query) would even be better in terms of concurrency than copying a file. Good idea, Gemma.

 

[Bill Bisco]

Can I ask you what is the reasoning behind this? I'm genuinely curious.

But to answer your question, one way I can see doing is to copy the backend, then relink to the new copy backend. This can be done via VBA without users' intervention as well, though if your backend is quite big there may be locking issues (maybe not but I don't know for certain that copying the file will lock the entire file)

Well, basically, the database that I'm developing is not a standard Access Database. It's a database of the current State of the Manufacturing System. For instance, a process in one state of the manufacturing system may be done at Station 1, while the same process at another state may be done at Station 10.

Because the Lines and Linespeeds change so much, the Managers I'm making this for would like to be able to easily see a former Manufacturing State without taking the current state, and doing a lot of manipulation to get it back to where it used to be.

Does that make sense?

The MakeTable Query Idea sounds very good. It seems that the would basically be turning their front end into a standalone Database Application. Thank you so much!

It seems that I'll have to make a copy of one of the current linked tables. Then do a make table query from the copy table, which will delete the linked table, and create a new Table of the same name that is not linked. Then Delete the Copy Table.

This looks like it will work great. There's not a Delete Table Query is there?

Sincerely,
Bill

 

[Banana]

Instead of going to trouble of creating a new link and deleting it after, you can just do a connection string directly in the query:

SELECT * INTO MyNewMakeTable
FROM [C:\SomeOtherDatabase.mdb].TheirTable;

That way you don't have to clean up the links when you've imported in the data you need. Of course, this assumes that the other database is somewhere accessible to everyone somehow.