Building an upgrade routine

[ARC]

I'm almost ready to build an upgrade routine for a commercial app that will
need to import data from an existing Access 97 back-end database to an
Access 2007 back-end blank database. Ideally, it would be nice to have the
program detect, on first launch, whether the back end database is blank, and
ask the user if they want to begin a new database (putting them into the
blank one), or import data from the previous version (the access 97
database).

However, the above has the drawback of needing all the extra code, queries,
and linking tables to be placed in the main program file .mde (and it's
plenty large enough already).

At one point I had a UK version and a US / Global version, both in Access
97. When I did away with the UK version (long story), I had some experience
in building a utility app that would import the data from one access 97 db
to a blank one. What I did in that case was build a small app that had 2
sets of linked tables. One set of tables linked to the blank database, and
one linked to the uk database. Then I built append queries that appended the
data to the blank db attached tables (I had tested prior, and nicely enough,
the append queries kept all the ID's straight, even for auto-number fields).
Having the conversion app was necessary because the 2 programs evolved on
different paths, and ended up with many different fields.

With that in the back of my mind, I now have the need to build an upgrade
routine to import from access 97 to 2007. It seems I have a number of ways
to go. In the ideal situation I mentioned in the 1st paragraph, I would need
to have a number of additional objects, and figure a way to temporarily link
to both db's (the blank 2007, and the full access 97 db). Unless there is an
easier way. I suppose one could store all the update queries as sql in the
form that does the upgrading, so they won't take up memory space unless the
form is called. But not sure what I'd do about the linking. I would not want
2 sets of tables permanently in my main / production db.

At this point, I'm thinking the upgrade will have to be in the form of a
small upgrade app. This app would have double the tables, one linked to the
access 97 back end database, and one set linking to the new 2007 back end
blank database. Then a series of update queries would run that would append
data from the 97 linked tables to the 2007 linked tables.

I can't do a straight convert, because the tables have changed, as have
relationships and referential integrity, which I would have to check /
adjust for in my update queries.

Any thoughts on either approach, and / or alternate approaches, and if it
the upgrade routine, tables, and objects should be in the main program, or a
separate app? Any help and thoughts would be most appreciated.

Many thanks,

Andy

[purpleflash]

On 29 Aug, 00:39, "ARC" <[e-mail]:

I'm almost ready to build an upgrade routine for a commercial app that will
need to import data from an existing Access 97 back-end database to an
Access 2007 back-end blank database. Ideally, it would be nice to have the
program detect, on first launch, whether the back end database is blank, and
ask the user if they want to begin a new database (putting them into the
blank one), or import data from the previous version (the access 97
database).

However, the above has the drawback of needing all the extra code, queries,
and linking tables to be placed in the main program file .mde (and it's
plenty large enough already).

At one point I had a UK version and a US / Global version, both in Access
97. When I did away with the UK version (long story), I had some experience
in building a utility app that would import the data from one access 97 db
to a blank one. What I did in that case was build a small app that had 2
sets of linked tables. One set of tables linked to the blank database, and
one linked to the uk database. Then I built append queries that appended the
data to the blank db attached tables (I had tested prior, and nicely enough,
the append queries kept all the ID's straight, even for auto-number fields).
Having the conversion app was necessary because the 2 programs evolved on
different paths, and ended up with many different fields.

With that in the back of my mind, I now have the need to build an upgrade
routine to import from access 97 to 2007. It seems I have a number of ways
to go. In the ideal situation I mentioned in the 1st paragraph, I would need
to have a number of additional objects, and figure a way to temporarily link
to both db's (the blank 2007, and the full access 97 db). Unless there is an
easier way. I suppose one could store all the update queries as sql in the
form that does the upgrading, so they won't take up memory space unless the
form is called. But not sure what I'd do about the linking. I would not want
2 sets of tables permanently in my main / production db.

At this point, I'm thinking the upgrade will have to be in the form of a
small upgrade app. This app would have double the tables, one linked to the
access 97 back end database, and one set linking to the new 2007 back end
blank database. Then a series of update queries would run that would append
data from the 97 linked tables to the 2007 linked tables.

I can't do a straight convert, because the tables have changed, as have
relationships and referential integrity, which I would have to check /
adjust for in my update queries.

Any thoughts on either approach, and / or alternate approaches, and if it
the upgrade routine, tables, and objects should be in the main program, or a
separate app? Any help and thoughts would be most appreciated.

Many thanks,

Andy

Hi Andy

The only way I know of to do this is to have, as you suggest, a
separate application to do the update with links to both sets of
tables. Remember that as far as I know you can't 'relative path'
linked tables so don't move the linked databases after linking!

This method does have the advantage of control though, you can control
all aspects of the data transfer including data type conversion and as
you say with append queries all links and autonumber values can be
maintained!

Good luck

purpleflash

[ARC]

Thanks, purpleflash! That's pretty much as I expected. It would be very
slick to have the program detect upon launch that the back-end is blank, and
ask if you want to start anew, or import from an older db, but I don't think
it's meant to be.

[ARC]

I guess I could still put the logic into the main program, and rather than
actually doing the work of upgrading, I put up a message that tells them to
exit, and what to do to upgrade, etc.

[Larry Linson]

"ARC" <an**@andyc.comwrote

Thanks, purpleflash! That's pretty much as I expected.
It would be very slick to have the program detect upon
launch that the back-end is blank, and ask if you want
to start anew, or import from an older db, but I don't think
it's meant to be.

Without knowing how many tables, and what changes, it would be difficult to
say how much this would clutter your production application. As linked
tables take up little space, unless you have substantial changes, I would
think the append queries themselves would be rather small, so you might well
be able to put it in your production application.

Purpleflash indicated that it was problematical if you moved the database to
which you are linking, but if you are using full Access, not the runtime,
it's easy to use the Linked Table Manager to relink the tables all at once.
If you are using the runtime, there's code you can use in the Access 2000
Developer Solutions Example. They move it around the Microsoft website with
some frequency, but as of the posting of this message, they seem to have
moved it once again, but didn't include the actual download link on the
page. <SIGH I'll report that, and you can visit
http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx to see if
they have corrected the page.

If you have downloaded the bapp2000.exe or bapp2000.mdb file in the past,
use it, because I don't think it's been updated.

Still, there shouldn't be a problem with having the database upgrade as a
separate database application, and you could, as you suggested, check in the
production application to determine if some crucial table were empty, tell
the user to run the upgrade/converter, and then Quit. One way I have
handled "new tables" is to create a "template" empty table in the database
and use Create Database to create a new DB to contain them and then use
CopyObject to copy the template tables to the new DB.

Larry Linson
Microsoft Access MVP

[ARC]

Hi Larry,

I've used that same trick. When I add a new table, I would have a local one
with the same name, but followed by "Structure", then do the copy.

I appreciate your tips! Basically in my last upgrade, I had the user browse
to the location of the old db, and the new one (and tried to auto-fill them
for the user where possible). Then I would manually do some snooping into
each to ensure that these were the right type of database, and that the
target they selected was in fact a blank db. Then when I had the source and
the target db saved to variables, I would relink the tables to both db's,
such as:

For i = 0 To mydb.TableDefs.Count - 1
Set MyTable = mydb.TableDefs(i)
If MyTable.Connect <"" Then
Call SetMessage(procmsg & Chr(13) & Chr(10) & MyTable.Name)
If Right(MyTable.Name, 1) = "1" Then 'IQ2000 UK database
MyTable.Connect = ";DATABASE=" & datapathUK
MyTable.RefreshLink
Else
MyTable.Connect = ";DATABASE=" & datapathIQ
MyTable.RefreshLink
End If
'Err = 0
End If
retval = SysCmd(acSysCmdUpdateMeter, i)

So for all the tables in the source db, I gave them a '1' at the end in the
linked tables tab, and for the 2nd copy of tables, I left them alone. So if
the table ended in 1, I'd relink to the source, and if not, I'd relink to
the blank db. The tedious part will be building all the update queries.
Yikes!

"Larry Linson" <bo*****@localhost.notwrote in message
news:3s5Bi.142$Xg.27@trnddc06...

"ARC" <an**@andyc.comwrote

Thanks, purpleflash! That's pretty much as I expected.
It would be very slick to have the program detect upon
launch that the back-end is blank, and ask if you want
to start anew, or import from an older db, but I don't think
it's meant to be.

Without knowing how many tables, and what changes, it would be difficult
to say how much this would clutter your production application. As linked
tables take up little space, unless you have substantial changes, I would
think the append queries themselves would be rather small, so you might
well be able to put it in your production application.

Purpleflash indicated that it was problematical if you moved the database
to which you are linking, but if you are using full Access, not the
runtime, it's easy to use the Linked Table Manager to relink the tables
all at once. If you are using the runtime, there's code you can use in the
Access 2000 Developer Solutions Example. They move it around the
Microsoft website with some frequency, but as of the posting of this
message, they seem to have moved it once again, but didn't include the
actual download link on the page. <SIGH I'll report that, and you can
visit http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx to
see if they have corrected the page.

If you have downloaded the bapp2000.exe or bapp2000.mdb file in the past,
use it, because I don't think it's been updated.

Still, there shouldn't be a problem with having the database upgrade as a
separate database application, and you could, as you suggested, check in
the production application to determine if some crucial table were empty,
tell the user to run the upgrade/converter, and then Quit. One way I have
handled "new tables" is to create a "template" empty table in the database
and use Create Database to create a new DB to contain them and then use
CopyObject to copy the template tables to the new DB.

Larry Linson
Microsoft Access MVP

[ARC]

With RE, I suppose if there is any orphaned data, it will just go into paste
errors, but the append will get all the valid records that pass RE? I'm
going to have a bunch of checks for various changes, but hopefully in the
event that I miss one or 2, they simply won't import in, which is desirable.