Migrating Data from one version to another version

[KK]

I have an application (Developed in MS access with two MDB's one for FE
and another for BE)
distributed to user group. We have new requirements that needs to
modify the tables structures including adding and deleting some of the
table feilds.

What is the best way to migrating the data from previous DB MDB to the
new DB MDB..
If we use import utility we have to do that that for each and every
table which takes a lot of time to complete.

Is there a easy way to do this by developing any functions in VBA..

Any tips or available to code to complete the task...

[Larry Linson]

I'm sorry, but I don't know what you are talking about in regard to "do that
for each and every table". On the database window, File | Get External Data
will allow you to check the objects you want and import them all at once.
It's possible to automate importing tables, but no easier than File | Get
External Data.

Larry Linson
Microsoft Access MVP

"KK" <kr******@gmail.comwrote in message
news:11*********************@73g2000cwn.googlegrou ps.com...

>I have an application (Developed in MS access with two MDB's one for FE
and another for BE)
distributed to user group. We have new requirements that needs to
modify the tables structures including adding and deleting some of the
table feilds.

What is the best way to migrating the data from previous DB MDB to the
new DB MDB..
If we use import utility we have to do that that for each and every
table which takes a lot of time to complete.

Is there a easy way to do this by developing any functions in VBA..

Any tips or available to code to complete the task...

[KK]

Thanks for the response.
The structure of most of the tables got changed(As part of
normalization) either by adding or deleting feilds to most of the
tables.This is the reason I cannot dump all the data directly into the
new DB

Larry Linson wrote:

I'm sorry, but I don't know what you are talking about in regard to "do that
for each and every table". On the database window, File | Get External Data
will allow you to check the objects you want and import them all at once.
It's possible to automate importing tables, but no easier than File | Get
External Data.

Larry Linson
Microsoft Access MVP

[KK]

To be more precise, as part of normalization new feilds are added to
some tables, some feields are deleted in some tables and few feilds are
moved to other tables.

On a whole there are changes to around 15 out of 60 tables.

KK wrote:

Thanks for the response.
The structure of most of the tables got changed(As part of
normalization) either by adding or deleting feilds to most of the
tables.This is the reason I cannot dump all the data directly into the
new DB

Larry Linson wrote:

I'm sorry, but I don't know what you are talking about in regard to "do that
for each and every table". On the database window, File | Get External Data
will allow you to check the objects you want and import them all at once.
It's possible to automate importing tables, but no easier than File | Get
External Data.

Larry Linson
Microsoft Access MVP

[pietlinden]

KK wrote:

To be more precise, as part of normalization new feilds are added to
some tables, some feields are deleted in some tables and few feilds are
moved to other tables.

On a whole there are changes to around 15 out of 60 tables.

I would probably create new front and back ends. Import the stuff you
can use from your old DB, then modify the front and back ends and then
redistribute. Otherwise, this is going to be a royal PITA. You can
use Tony Toews' AutoFE Updater.

[KK]

The plan is to create new FE and BE..
The question is,how Am I going to dump the existing data to the new DB
The customer wants to retain the existing production data in the new
system.

pi********@hotmail.com wrote:

I would probably create new front and back ends. Import the stuff you
can use from your old DB, then modify the front and back ends and then
redistribute. Otherwise, this is going to be a royal PITA. You can
use Tony Toews' AutoFE Updater.

[pietlinden]

KK wrote:

The plan is to create new FE and BE..
The question is,how Am I going to dump the existing data to the new DB
The customer wants to retain the existing production data in the new
system.

pi********@hotmail.com wrote:

I would probably create new front and back ends. Import the stuff you
can use from your old DB, then modify the front and back ends and then
redistribute. Otherwise, this is going to be a royal PITA. You can
use Tony Toews' AutoFE Updater.

Depends on how complex your modifications are. You could set up your
tables without constraints (referential integrity etc) and then use
queries to update/modify your tables as you normalize them (or whatever
you're doing), and when you are done, you can link to the new DB and
run append queries. Once your data is all in and normalized, you can
reset the not null and referential integrity constraints. Basically,
you can do it all with queries. But I would take a *very* small subset
of the data (like 3-5 records per table) and then test to make sure you
can get the answers out that you need. Then just delete your test data
and run a series of append queries to populate your DB with real data.

[KK]

As I need to run these series of queries few times (In different
environments and later on to the production environment) do I need to
create any functions or GUI to execute the scripts.. I am basically not
a hardcore VBA developer..

Is there any code available to pass the series of queries as parameters
pi********@hotmail.com wrote:

>
Depends on how complex your modifications are. You could set up your
tables without constraints (referential integrity etc) and then use
queries to update/modify your tables as you normalize them (or whatever
you're doing), and when you are done, you can link to the new DB and
run append queries. Once your data is all in and normalized, you can
reset the not null and referential integrity constraints. Basically,
you can do it all with queries. But I would take a *very* small subset
of the data (like 3-5 records per table) and then test to make sure you
can get the answers out that you need. Then just delete your test data
and run a series of append queries to populate your DB with real data.

[KK]

As I need to run these series of queries few times (In different
environments and later on to the production environment) do I need to
create any functions or GUI to execute the scripts.. I am basically not
a hardcore VBA developer..

Is there any code available to pass the series of queries as parameters
pi********@hotmail.com wrote:

>
Depends on how complex your modifications are. You could set up your
tables without constraints (referential integrity etc) and then use
queries to update/modify your tables as you normalize them (or whatever
you're doing), and when you are done, you can link to the new DB and
run append queries. Once your data is all in and normalized, you can
reset the not null and referential integrity constraints. Basically,
you can do it all with queries. But I would take a *very* small subset
of the data (like 3-5 records per table) and then test to make sure you
can get the answers out that you need. Then just delete your test data
and run a series of append queries to populate your DB with real data.

[KK]

As I need to run these series of queries few times (In different
environments and later on to the production environment) do I need to
create any functions or GUI to execute the scripts.. I am basically not

a hardcore VBA developer..

Is there any code available to pass the series of queries as parameters

[pietlinden]

KK wrote:

As I need to run these series of queries few times (In different
environments and later on to the production environment) do I need to
create any functions or GUI to execute the scripts.. I am basically not
a hardcore VBA developer..

Is there any code available to pass the series of queries as parameters

Why do you need to pass parameters?

*One* (and certainly not the only) way to do it:
1. create an unbound form with a button and a label.
2. in the button's click event, do something like
- set the label's text to "Processing <queryname>..."
- run the query (DoCmd.OpenQuery "queryname")
- do this for each query and use the DoEvents and Me.Repaint to
repaint the form (so you see the updated messages).

- SHow the done message. Then hide the label.

you might want to use DoCmd.Execute "SQL Statement..." and then you
won't get lots of annoying "are you sure?" garbage, but you do want
error messages to get returned so you know what's going on.... (I guess
you could create a function to run the query and return the error # to
the function result... If it's zero, then everything's fine.

Have a look around the NG for RunSQL, OpenQuery, and DoCmd.Execute {SQL
Statement]

That should give you a place to start. But I'd make backups before
doing lots of data manipulation. You may not get it right the first
few tries. so just leave yourself an escape route (yes, you could use a
transaction, but get the basics going first...)

Hope this helps a little.
Pieter

[KK]

Thanks a lot .
Now I have a better idea how to deal with this data migration.I have
tried by creating test MDB files and it give me some confidence to
start with actual application.

I will try to search NG's for error handling techniques that needs to
be implemented during executing these queries from the code. If you
know any such please let me know.
Once again thank you so much for your prompt response.
pi********@hotmail.com wrote:

Why do you need to pass parameters?

*One* (and certainly not the only) way to do it:
1. create an unbound form with a button and a label.
2. in the button's click event, do something like
- set the label's text to "Processing <queryname>..."
- run the query (DoCmd.OpenQuery "queryname")
- do this for each query and use the DoEvents and Me.Repaint to
repaint the form (so you see the updated messages).

- SHow the done message. Then hide the label.

you might want to use DoCmd.Execute "SQL Statement..." and then you
won't get lots of annoying "are you sure?" garbage, but you do want
error messages to get returned so you know what's going on.... (I guess
you could create a function to run the query and return the error # to
the function result... If it's zero, then everything's fine.

Have a look around the NG for RunSQL, OpenQuery, and DoCmd.Execute {SQL
Statement]

That should give you a place to start. But I'd make backups before
doing lots of data manipulation. You may not get it right the first
few tries. so just leave yourself an escape route (yes, you could use a
transaction, but get the basics going first...)

Hope this helps a little.
Pieter