Virtual tables

[samonwalkabout]

I have a large stock control database. The User want to be able to try out changes in supply and demand in a "virtual database" so they can try things out, if they work they then want to keep them if they dont they want to ditch them and returm to the orginal. Anyone ever attempted this before? I tried to acheive it with make tables but its messy and drops the primary key value.

Any ideas??

 

[namliam]

Copy the entire DB to a Testing Directory (remember to relink) and addapt that.

If it works then "simply" copy the FE over the old one.

If not Chuck it.

Regards

 

[samonwalkabout]

Im not sure if this solution is workable on a day to day basis as a stand alone programe. The users wants to do this often and has not ability to move files, copy Frount ends or link databases. It just need to be a button on a form. Can i automate this somehow?

 

[FloBob]

Are you trying to have two sets of tables OR are you trying to have to sets of forms... I cant quite make out what you are attempting to do. If you want two sets of Tables, just make two BE and have one named test*.mdb then use code to link the tables back and forth I have my security autoupdate itself so here's some of the code to give you a direction.

DoCmd.TransferDatabase acImport, "Microsoft access", _
"l:\measurements\security\security.mdb", acForm, _
main!objname, main!objname

Note: you can use aclink instead. Works great though. Hope this helps.

 

[samonwalkabout]

2 sets of tables is the aim can you explain linking them a bit more.

lets say i have 2 BE files called "realdb1" and "testdb1"

And 1 table called "tbldemo" in both

I need to fristly.. get the data from realdb1.tbldemo to testdb1demo.

and then may need to get the data from testbd1.tbldemo to Overwrite that in realdb1.tbldemo

There may be additonal records now or less but the colums will be the same.

Thanks

 

[FloBob]

welp if you needed the data to be exact copies of each other you could do somthing like this


'Delete the current link to the BE

DoCmd.DeleteObject acTable, "TblDemo"

DoCmd.TransferDatabase acimport, "Microsoft access", _
"l:\path to data\realdb1.mdb", acTable, _
"TblDemo", "TblDemo"


Set this up in a togle button or somthing and have it do the reverse to go back to the live data. This will delete the current link to the database and copy the table down to the local mdb. Also you might want to use the docmd.setwarnings command too otherwise your users will be prompted with add delete comfirmation boxes. Hope this helps

 

[namliam]

Niether is the problem, Copy the DB and Relinking can both be done (easely) from a button.

The issue here is

I have a large

How large is large and on your network, how long does it take to copy....

To test, do they need day to day up to date table(s)?

What is the EXACT perpose?

Regards

 

[FloBob]

This is why I sugjested two mdb... But if he needs the same Real data. He has to sync the two tables and that will probably cause alot more overhead then I personaly would like. Even if deleting/copying the table takes awhile this isnt somthing that has to be maintained. The user could do it on his own.

 

[namliam]

Deleting and importing will be more time consuming i thing, further this inflates the DB considerably everytime you execute it, demanding compacting taking yet more time. Therefor i would prefer Copy....

Again: Both copy and/or relinking can be done from the push of a button....

Regards

 

[samonwalkabout]

The Database is indeed large and in reality im talking about 5 tables contain data on

Stock level 5600 records per year,

Production schedule - 1120 per year,

Orders -well above 8000 per year,

Usage level - How much of x is in product y 2000 rec per year

process/product - whats in product y 2000 rec per year

I only have a years data in there at the moment but they want it to function till 2010.

Which will need to be backed up in some way. The network connection is quite fast in some department but not in others. However since the copy action would only need to be done once in a while when a user wanted to play "what will happen if" and they may not always need to copy the data back over the orginal again.

The purpose is they want to be able to change the outgoing of stock through production use levels and production schedules, the incoming of new stock, and wastage levels of stock to see how it will effect stock levels to the end of the year. They want the "virtual tables" so they can play with diffrent combinations until they get it right without changing the master, and so they can run worst case senario situations i.e "what if supplier x dissapears?, how long can we still make y product?"

I hope this makes it clearer.

What about having a having a virtual FE that links to a VirtualBE

All i need is a way of copying the tables back and forth. ie a button in the real FE that copys the tables into the virtual be and opens the virtual FE.

And a button in the Vir FE that copys the tables into the real one.

I need to keep realtionships intact which is the problem i have been running into a bit.

 

[namliam]

That exactly is the point of a full copy, no messing with PK/relationships that sort of thing...

The size(s) you are giving is not saying much a record can be 1 Kb or 1 Mb (making a small but distinct difference ) Can you give a current size of the BE in Kb/Mb?
Further the time to copy the file vs how fast do they need it is the big question. A copy time of 10 mins is bad if you need it NOW but if you need it tomorrow a copy time of 4 hours isnt to bad....

Having a seperate FE is a must, if you are going this way. In this FE you can have correct links to the copy (virtual) DB allready in place, eliminating the need to relink. Only thing needed then is an Unbound form to enable to copy action. Mind you both target and source DB's must be unused at the moment!
Which might make it necesary to do a table by table delete and import and finaly a compact.

Regards

 

[samonwalkabout]

The records themselfs are small enough, the database is still in construction and i havent split it yet, but i can see it ever talking more than 10 mins to perform the operation. I had 3 make table qrys running before to produce copys they only took a total of 30 secs to run. And append qrys to send data back to the orginal tables where also quick. (problems came when new records created were left out) So i tried Make tables back again to copy over the orginals but they drop the realtionships and the PK indexing.

Can you tell me how i can do this in a few steps.

1. create a 2 copy's of FE and BE in a seperate location

2.?

3?


thanks

 

[namliam]

2. In the "virtual" DB make a button to Cope the BE DB for a full update
3. Create some smart (append/update) query's to send the data back to the operational DB (also a button or something) or if that gets to complex tell the user, Once you figure it out reinput it by hand into the operational DB.

Regards

 

[samonwalkabout]

step 3 is the one thats causing me the head ache's

if the user adds new records do i need to run an append qry and then an update qry?

what if he deletes records?

 

[namliam]

If changes in the Virtual DB can be taken blindly you can do a copy back.

However most crucial is how is the Operational DB, does that change meanwhile if it does then you have a real problem having to consider loads and loads of things.... then i would most definatly go for 3b

if that gets to complex tell the user, Once you figure it out reinput it by hand into the operational DB.

If the operational is stable enough you then need to consider the changes that can be made:
Update
Delete
Append

And running them for every table....

Regards

 

[samonwalkabout]

After tring out lots of variations of qrys and techniques i think on reflection the risks of corrupting or damaging the intergity are too great to leave to automated qrys. Especally as the network itself can be a bit buggy at times. I think the User will just have to put up with having a virtual DB to play in and not have the function of copying this back over the orginal.

Thanks for your Help guys