Need to copy data from Tables

[ashishprem]

Hi,
I have my 2 MDB files. One test version and one production version. Both have the same table structure. Periodically I want to copy the data from production MDB tables and pasting in test version MDB tables manually. Is there any way to do it using macro. I can write macros but i dont know what logic should be implemented here. I mean do i need to open that DB and copy each table one by one.

Any suggestions? Thanks in advance.

Ashish

 

[namliam]

take a look in the help for the TableDefs object that should help you right along...

 

[gemma-the-husky]

why not just delete the old dbs, and copy the new production copy to the same name. wont that work?

mind you, this sounds like you have not split the database between back end and front end - is that the situation?

 

[DCrake]

Simple Software Solutions

If you are copying ALL the tables from Production to Test which I assume are both back end MDB's linked to seperate front ends, then why not simplify the process of compacting production.mdb to Test.mdb

You will then have an identical copy of the tables in the test.mdb.

Reading between the lines you are using this for development purposes so that you can develop the front end without affecting the live datain the production mdb.

Here's a thought to consider. Again, assuming you have a login screen for users - have a login named testing and password of testing. Then when you login under the above the application toggles the table links between production and testing. That way you do not bother about this affecting your live data.

I use this method for on site training of distributed mdb's.

CodeMaster:http://www.icraftlimited.co.uk

 

[ashishprem]

Hi,
The MDB files contains the macro and forms. I keep on modifying the macros in TEST.MDB file. Once I am satisfied with the macro updation I need to run them in production data. This can be achieved either by migrating production table data to TEST.mdb tables or copying the dev macros in production mdb. But i feel copying the table data is logical option.

Thanks for supporting me..

Ashish

 

[namliam]

DCrake is saying that it in general is a good idea to have a seperate " backend " database that holds only the tables. Rather than having both forms/code and data/tables in the same database

And I think for 95% of all cases he is right.

 

[Rabbie]

And I think for 95% of all cases he is right.

Only 95%

 

[ashishprem]

right now if i have to make the back end and front end separate I have to change the macros at many places. Any idea to make it work in my case where the front end and back end are same. for eg: i should sepcify the prod mdb path name and it should be able to copy the data.
One small doubt.

I am running form in MDB1. can i read data from MDB2 tables. if this works then i can get my work done. Thnks guys.

Ashish

 

[Rabbie]

You can open tables in another .mdb by linking to them. Then you can use a query to copy the data across.

Splitting your database into fe/Be is nearly always good because you it makes testing new macros/VBA/forms/reports much easier.

Also if more than 1 user will be accessing your db splitting them is essential to avoid corruptions.

 

[gemma-the-husky]

if you split the database between backend and front end, you shouldnt have to change anything

the table names in the front end still look and work exactly the same (odd differences)

they will appear as the same tablenames in your tables list, but with little black arrows against them, showing they are connected to a backend. Your queries etc will still work with the same table names as before

among the things that dont work with attached tables, is the indexed seek method, but most things are fine

 

[namliam]

take a look in the help for the TableDefs object that should help you right along...

I would re-advise you to look at that... if you are not going to split the FE from the BE.

 

[ashishprem]

Hi guys,

As per the suggestion I am planning to change the front end and back end in two different places. One small doubt. Making FE/BE separate means having two separate mdb's but off course they are linked. am i right? So while transferring the application I need to copy both the MDB's.

Ashish

 

[namliam]

Yes there are 2 seperate databases...

If you want a refresh in development simply copy the BE database to your development environment and done...

If you want to put into production new code, copy your front end from development to production... NOT your BE as that will contain obsoleet data.
Unless offcourse you make changes to your tablestructure and stuff... then it gets more complex.

 

[Ayman Mt]

Mr

you have to create a new DB with a macro named "autoexec" that runs delete queries and join queries (join data from tables in the first DB to tables in the second one) to each table in your DBs, the one you want to copy from and the one you want to copy to. First you have to link tables from both DBs to this new DB, providing that the fields in both tables have the same properties.
Now by clicking on the DB the macro will run Automatically and delete the old information from tables in the DB you want to copy data to, and then join the up to date data. It is a bit complicated, anyway, you can attach the an example I'll do it for you.