How to create custom data migration query?

[deko]

If I release a new version of my mbd (in mde format) and users want to
upgrade - how do they migrate their data?

For example, if the original was released as data1.mde and then I release
data2.mde (same table/data structures - just revised VBA code with perhaps a
new/updated form or two), what's the best way for users to get their data
into the new data2.mde?

I'm thinking I could use a query - something like:

"INSERT [everything] FROM [data1] INTO [data2] EXCEPT WHERE [data1_column] =
AutoNumber"

This means I would have to iterate through each of the 50 tables in the
database somehow. Is this the best way? Other options?

Thanks in advance.

[rkc]

"deko" <de**@hotmail.c om> wrote in message
news:Sw******** ***********@new ssvr27.news.pro digy.com...

If I release a new version of my mbd (in mde format) and users want to
upgrade - how do they migrate their data?

For example, if the original was released as data1.mde and then I release
data2.mde (same table/data structures - just revised VBA code with perhaps a new/updated form or two), what's the best way for users to get their data
into the new data2.mde?

I'm thinking I could use a query - something like:

"INSERT [everything] FROM [data1] INTO [data2] EXCEPT WHERE [data1_column] = AutoNumber"

This means I would have to iterate through each of the 50 tables in the
database somehow. Is this the best way? Other options?

Your data and front end objects should reside in separate files whether
you have a single user or 30 users. Your current problem disappears.

[deko]

> Your data and front end objects should reside in separate files whether

you have a single user or 30 users. Your current problem disappears.

What do you mean? How do I separate front end objects form data?

The other thing I tried was this:

After the new release (data2) is ready to go, delete all the tables and
relationships, and then make it into an mde. Then, import all the tables
from data1 into data2. The only problem with this is I can't update or add
any new tables in the new release, which is very limiting.

[Rick Brandt]

"deko" <de**@hotmail.c om> wrote in message
news:9W******** ***********@new ssvr25.news.pro digy.com...

Your data and front end objects should reside in separate files whether
you have a single user or 30 users. Your current problem disappears.

What do you mean? How do I separate front end objects form data?

Access can link to tables in other files. You carry this to its extreme and set
up ALL of your tables as links to another file and presto, one file for data and
one file for everything else.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[deko]

> Access can link to tables in other files. You carry this to its extreme
and set

up ALL of your tables as links to another file and presto, one file for data and one file for everything else.

So then do I distribute two mde files? One with tables and queries only,
and the other with all the forms, modules, reports, etc?

Still, if I want to update the data structure in a new release, don't I
still have the same problem?

[Rick Brandt]

"deko" <de**@hotmail.c om> wrote in message
news:W3******** ***********@new ssvr25.news.pro digy.com...

Access can link to tables in other files. You carry this to its extreme and set

up ALL of your tables as links to another file and presto, one file for

data and

one file for everything else.

So then do I distribute two mde files? One with tables and queries only,
and the other with all the forms, modules, reports, etc?

There is no benefit to making an MDE of the data file as it does nothing to
tables or queries.
Still, if I want to update the data structure in a new release, don't I
still have the same problem?

Yes. In those cases I usually include code in the new front end that
programmaticall y applies the table structure changes to the existing back end.
Still, the vast majority of updates don't affect the tables.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[deko]

> > So then do I distribute two mde files? One with tables and queries
only,

and the other with all the forms, modules, reports, etc?
There is no benefit to making an MDE of the data file as it does nothing

to tables or queries.

Agreed, but the idea is to distrubute 2 files: one (mde) that contains front
end (forms, modules, reports) and another (mdb) that contains the back end
(tables, relationships, queries) - is this correct?

But how to set up the links to the back end? Don't I need dummy tables in
the mde which link to the real tables in the mdb?

When all is said and done, it appears there's really not much advantage in
doing this. As I mentioned in an earlier post, I can just distribute the
new data2.mde without any tables and have the users import all the tables
from the old data1.mde. The only advantage of having separate front and
back end files is avoiding the need to do the import. When this is weighed
against the ease of maintaining only one file, I'm not sure if it's worth
it. The problem of distributing an updated version that requires changes to
the existing data structure remains an issue in either case. As you say,
that must be addressed with code in the new release.

[deko]

> > Still, if I want to update the data structure in a new release, don't I

still have the same problem?
Yes. In those cases I usually include code in the new front end that
programmaticall y applies the table structure changes to the existing back

end. Still, the vast majority of updates don't affect the tables.

So, if a new release (mde) includes changes to the table/data structure -
regardless of whether or not the front and back ends are split - I'll need
code to massage the data from the old structures into the new structures.
If this is the case, then why bother splitting into front and back end?

As for getting existing data into a new mde version WITHOUT data structure
changes, why not simply gin up some code to do this as well? I could
distribute the updated mde with code that runs once the first time it's
opened that prompts for the path to the old mde, and then imports all the
tables automaticcally with a few DoCmd.TransferD atabase statements. This
seems like a better way to go than having to deal with two files.

[deko]

From: http://support.microsoft.com/?kbid=304932
Reasons Why You May Want to Split Your Database
The following are typical reasons to split a database:

You are sharing your database with multiple users on a network.
You have several people developing in the database and you do not have
Microsoft Visual Source Safe installed.
You do not want your users to be able to make design changes to tables.

The most common reason to split a database is that you are sharing the
database with multiple users on a network. If you simply store the database
on a network share, when your users open a form, query, macro, module, or
report, these objects have to be sent across the network to each individual
who uses the database. If you split the database, each user has their own
copy of the forms, queries, macros, modules, and reports. Therefore, the
only data that must be sent across the network is the data in the tables.

Bottom line: if you want to follow best practices in a multi-user
environment, split the database; in a single-user environment, don't split.

[David]

I have an app which I have sold to a number of single users.
It is split. All tables are in the back end. Every thing else is in the front
end.
The back end is unique to each user. The front ends are all identical.

Upgrades are simply a matter of distributing a new front end.
Include some code with the front end which relinks the tables
No problems !!
HTH
David B

deko <de**@hotmail.c om> wrote in message
news:7k******** **********@news svr29.news.prod igy.com...

From: http://support.microsoft.com/?kbid=304932
Reasons Why You May Want to Split Your Database
The following are typical reasons to split a database:

You are sharing your database with multiple users on a network.
You have several people developing in the database and you do not have
Microsoft Visual Source Safe installed.
You do not want your users to be able to make design changes to tables.

The most common reason to split a database is that you are sharing the
database with multiple users on a network. If you simply store the database
on a network share, when your users open a form, query, macro, module, or
report, these objects have to be sent across the network to each individual
who uses the database. If you split the database, each user has their own
copy of the forms, queries, macros, modules, and reports. Therefore, the
only data that must be sent across the network is the data in the tables.

Bottom line: if you want to follow best practices in a multi-user
environment, split the database; in a single-user environment, don't split.