Contemplating A97 to A2003

[Leslie Isaacs]

Hello All

I have an A97 mdb that is split split f/e b/e, with a single b/e on the
'server' (really just another PC, but not used as a workstation) and 4 f/es
on 4 workstations. The 4 workstations and the 'server' are all running
windows2k: the 'server' does not have Office installed.

I would like to update to Office2003, but before I do so I was wondering
whether there are any tips, warnings or suggestions that I should think
about before proceeding. I'm a great believer in the "fore-warned is
fore-armed" phyilosphy, and would prefer to avoid problems with the
conversion if possible.

I cannot afford more downtime that the conversion itself will take - the
application is 'mission critical'. Given this, would it be a good idea to
install Office2003 alongside the existing Office97, so that if necessary the
users can continue with the Access97 application until the 2003 version is
up and running?

Grateful for any ideas.

Many thanks
Leslie Isaacs.

 

[Allen Browne]

Leslie, you should be able to update with a minimum of fuss, but if
down-time is critical, copy a backup copy of the back end onto another
machine along with the front end, and do the conversion there. If everything
goes smoothly, you can then convert the real back end, and then copy the
converted front end to each workstation.

If you want to read up on what to expect, see:
Converting from Access 97 to 2000, 2002 or 2003
at:
http://members.iinet.net.au/~allenbrowne/ser-48.html
The article addresses setup issues, conversion issues, and usability issues,
warning of several new bugs and performance issues you may not be aware of.

 

[Leslie Isaacs]

Allen

Many thanks for your reply, and for the link to your comprehensive
information on this subject.

Having read it all - and understood about half of it! - I'm somewhat put off
converting at all.
Mainly it was the bit about "Learning Access is more difficult" - interface,
DAO and ADOX mean nothing to me, and as I'm not a professional developer I'm
unlikely to get to grips with it all.

My reasons for contemplating conversion are (were!):

1) my assumptions that the later version would be more stable, less buggy
and faster (call me naive?)
2) my assumptions that the new features would be useful in establishing a
web interface between my payroll agency (which uses the mdb) and our clients
3) a developer whose services I would like to use for some small projects no
longer works with A97
4) the XML file format would be useful for Inland Revenue end-of-year
returns
5) a general, ill-defined but definite feeling that upgrade = progress =
good

The mbd is fairly complex ~70 tables, ~300 queries (admitedly ~half of which
are redundant), ~80 forms (--ditto--), ~400 reports (--ditto--), ~80 macros
(--ditto--) and a pretty heavy module (~1400 lines, none of which are
redundant)! I know it has many design faults (the main one being that it was
never really 'designed' in the first place - it's been cobbled together by
me over the last 4 years!!), but in fact it works very well (if a little
slow) ... so why change?

I realise that only I can answer that question, but in order to do so
obviously I need to understand the pros and cons of it, so I am very
grateful for your information. If I could prevail on you a bit further, I
would be very grateful for any further advice you may have in light of the
above information. Your initial reply (that I should be able to update with
a minimum of fuss) was very encouraging, but the information in the link had
the opposite effect.

Many thanks
Leslie Isaacs

 

[Allen Browne]

Replies embedded.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Having read it all - and understood about half of it! - I'm somewhat put
off
converting at all.
Mainly it was the bit about "Learning Access is more difficult" -
interface,
DAO and ADOX mean nothing to me, and as I'm not a professional developer
I'm
unlikely to get to grips with it all.

You probably don't need to learn it all. If you are already coding in DAO,
you can convert your database, and just continue to use DAO code. IMHO,
that's still the best way to do it anyway if your data is in Access tables.

My reasons for contemplating conversion are (were!):

1) my assumptions that the later version would be more stable, less buggy
and faster (call me naive?)

Correctly set up, A2003 is more stable than A97 to develop in. During heavy
development, I expect a corruption every few days in A97, and every few
weeks in A2003.

Once the development is complete (not adding or modifying forms, code,
reports, etc), both versions are extremely stable. On reliable hardware with
reliable power and a reliable network and reliable users, you could go for
years and not see a corruption.

Performance-wise, most new versions are slower than older versions, and
A2003 will be a little slower than A97, e.g. because of the Unicode support.
Some things are much slower in A2003, e.g. VBA functions called in a query.

2) my assumptions that the new features would be useful in establishing a
web interface between my payroll agency (which uses the mdb) and our
clients

Although A2000 and later have Data Access Pages (DAPs), they are not very
useful IME, certainly not beyond a local intranet. Better to use another
technology such as PHP (or ASP) to read/write the Access database. Of
course, none of these including DAPs give you the rich environment that
makes Access such as joy to work with.

3) a developer whose services I would like to use for some small projects
no
longer works with A97

Good people you can trust and work with - yes, that's a very important
consideration.

4) the XML file format would be useful for Inland Revenue end-of-year
returns

If you need XML support, then that's an important aspect as well.

5) a general, ill-defined but definite feeling that upgrade = progress =
good


... Your initial reply (that I should be able to update with
a minimum of fuss) was very encouraging, but the information in the link
had
the opposite effect.

Leslie, I think there are 2 questions here:
1. How hard is it to learn to use A2003 so that it's usable?
Expect a bit of a learning curve, so you know how to:
- turn off the stuff that corrupts the database (e.g. Name AutoCorrect),
- get rid of the stuff that slows it to a crawl (e.g. Subdatasheets),
- work around the new bugs (e.g. an AccessField in the LinkChildFields),
- get used to the new frustrations (e.g. having to turn AllowZeroLength off
every time you create a Text field),
- solve the interface problems (e.g. controls that flicker on tab controls),
and so on. These obstacles are not huge: we have come a long way since the
days when A2000 was first released and we suggested not to use it.

2. How hard is it to convert an A97 database?
The answer is that once you have #1 down, this one is usually pretty simple.
(There are exceptions: e.g if you database was originally written in Access
2 and uses the 2.5/3/5 compatibility layer, you have work to do.)

Hope that clarifies it for you.

 

[Leslie Isaacs]

Allen

Thank you again for your further reply.

A couple of (in fact 3!) minor questions:

1. I use the standard access interface to create/edit queries, forms, etc.,
and I type code directly into a module for the functions that I have. Does
this mean that I already code in DAO?
2. I read on your website that many of the new features of the newer
versions are not available, etc., if the data is being stored in access
tables. My data is stored in access tables: what are the alternatives? I
have read somewhere about using MSDE for the b/e - is this a good idea (and
easy to do)?
3. You say "Some things are much slower in A2003, e.g. VBA functions called
in a query": does this also apply to functions called as the on-click event
of a button? If so I would have grave reservations about converting, because
the 'bread and butter' of the application involves running a complex
function (in the 1400-line module) every 20 minutes or so. Currently the
function (together with a few subsequent queries) takes 10-20 seconds to run
on average, and I would not want this to get "much slower".

Many thanks for your continued help.
Les.

 

[Allen Browne]

Embedded.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

1. I use the standard access interface to create/edit queries, forms,
etc.,
and I type code directly into a module for the functions that I have. Does
this mean that I already code in DAO?

Examples of using DAO:
- Opening a Recordset;
- Executing an action query, e.g. dbEngine(0)(0).Execute "INSERT INTO ...
- Examining a TableDef, e.g.:
http://members.iinet.net.au/~allenbrowne/func-06.html
- Setting the Connect property of your linked tables.

To see more, press F2 when you are in a code window.
Access opens the Object Browser
It will show you which objects belong to Access, VBA, and DAO.

2. I read on your website that many of the new features of the newer
versions are not available, etc., if the data is being stored in access
tables. My data is stored in access tables: what are the alternatives? I
have read somewhere about using MSDE for the b/e - is this a good idea
(and
easy to do)?

In the new versions, MS spent lots of time trying to make Access a good
front end for SQL Server, not so much time improving Access for its native
data engine (JET). JET is still simpler than SQL Server (MSDE or whatever
its called this month). My suggestion would be to stay with JET unless it
will not handle your data needs. That happens if:
- for foresee many millions of records in some tables;
- the database must run 24x7 (cannot come off line long enough to make a
backup);
- security is crucial (JET security is a padlock, not a vault);
- you will have many dozens or hundreds of simultaneous users.

3. You say "Some things are much slower in A2003, e.g. VBA functions
called
in a query": does this also apply to functions called as the on-click
event
of a button? If so I would have grave reservations about converting,
because
the 'bread and butter' of the application involves running a complex
function (in the 1400-line module) every 20 minutes or so. Currently the
function (together with a few subsequent queries) takes 10-20 seconds to
run
on average, and I would not want this to get "much slower".

No. VBA functions run from VBA (the event procedures) are fine. The
particular issue is where a query calls one of your functions. Of course, if
your VBA function executes a query statement or opens a recordset that is
based on a SQL statement that contains a VBA function, then you have struck
this issue.

HTH