One database, 97 and 2003 users

[Martin]

Where I work there is a database that is currently in 97 format and contains
both front end and data. One set of users (the data-inputters) use the many
forms (with built-in code) to add/edit records. Another set of users (the
accountants) only report on the data, creating and editing queries and some
code to process the data every month. It's cumbersome but the latter group
make a new copy of the database every month and save it for historical
purposes.

We're now going to upgrade to 2003, department by department (and no one
knows how long that will take!). Clearly once everyone's there, the database
will be converted. But what happens meanwhile? My feeling is to leave it in
97 format for the moment. That way the data-inputters will continue being
able to use their forms, even when they've upgraded. The problems will be
with the accountants who work on a new copy of the file every month and need
to be able to make/redesign queries and code - something they won't be
allowed to do with a 97 file opened in 2003. One solution would be a
redesigned back end/front end structure but then there's the problem with
them wanting a new copy every month. Another would be for them to convert
the file every time (incidentally, is there a way to make this the default
rather than the user always being asked whether they want to open or convert?
And is there any reason to use 2000 format over 20032-3 format at this
stage?).

Apologies for the rambling nature of this post: I can see no solution is
going to be ideal but does anyone have experience of this situation and/or
any ideas?

Thanks,
Martin

 

[Jeff Boyce]

Martin

One approach would be to leave the backend data in A'97 format, keep your
A'97 front-end, and create a front-end in A2003 (and link to the A'97
back-end). When someone needs to copy the database, they can copy the
back-end file without converting it. Even if they use A2003, they'll still
be able to link to the (copied) back-end.

 

[Sirocco]

The fact that you're upgading your system shouldn't interfere with making a
backup copy of the tables, that is, whatever tables are in use at the time.
In fact, our tables were made in Access 97, even though we use Access 2003.
In general, any version mdb file can be converted to the next version of
Access, but everybody has to have a copy of the next version to work with
(read/write) the "upsized" front end file. For example, once your 97 mdb
file is converted to Access 2000, everybody will need Access 2000 installed
on their PC's (since an Access 2000 mdb file can't be read in Access 97).
Going from Access 2000 to 2003 is easier: Access 2003 will open an Access
2000 mdb file without any conversion. See some reference material for
assistance in going from Access 97 to 2003.

 

[Sirocco]

Oh, I saw this on another thread:
See:
Converting from Access 97
at:
http://members.iinet.net.au/~allenbrowne/ser-48.html

The article contains suggestions on setting up, converting, and usability
issues. It does not relate specifically to Terminal Services.

 

[Martin]

Thanks both Jeff and Sirocco for your advice.

Does anyone know a way to bypass the open-or-convert dialog box when a user
opens an old database? I'd like them to go straight to convert.

The reason for this is, unfortunately, the FE/BE solution isn't going to
work because of the multiple copies of this database flying around the system
(bad practice I know but the accountants insist). They will be getting 2003
before the data-inputters which means I'm going to have to leave the file in
97 format for them and the accountants will have to convert their copy each
month (because they need to be able to add new queries and modify a module
for a monthly batch-job they run).

Needless to say, I'm having to fix quite a lot of the code for this old
database to run in both versions: 97 seems to be a lot more patient with
sloppy DAO and, amongst other oddballs, a password field's text property is
now read as asterisks rather than the actual password (I'm having to replace
all the old .Texts with .Values).

Happy days!

 

[Jeff Boyce]

Martin

Not sure I understand your situation well enough, then. When we converted
to XP at my office, it was in a progressive manner, too. I created new XP
front-ends and had them ready for deployment to the newly added XP
installations. These XP front-ends pointed at the '97 data, as did the old
'97 front-ends. When all folks were installed with XP, I created an XP
back-end, repointed the front-ends, and walked away from '97.

I still don't get why someone with '03 would need to convert anything each
month. If they use an '03 versioned front-end, they can see all the data.
If they need an archive copy of the data, they can copy the file via Windows
Explorer. If they need to add a new query, they can do so from within their
'03 versioned front-end. What am I missing?

Regards

Jeff Boyce
<Access MVP>

 

[Douglas J. Steele]

Check http://support.microsoft.com/?id=275164