Access 2000 - Realtime Development - Should i stick with Access97

  • Thread starter Thread starter Jado
  • Start date Start date
J

Jado

Hi

I'm upgrading my clients to XP Pro and would like to upgrade to Office 2000
so I can take advantage of the 'Custom Installation Wizard' and Microsoft
support.

I develop internal databases, (lots of them), and constantly need to add new
code and objects to these db's whilst there still in use by the users.

I currently do this by making all my changes, then ask the users to logout
for a few moments whilst I save all my new and updated object, close my db,
then get the users to login and see all the updated stuff.

-------------------------------------------

I remember reading, quite a while ago, that Access 2000 is an absolute
nightmare to work with whilst users are still using the database!

as I remember, the problem talked about was something to do with bypassing
the autoexec by holding SHIFT.

if the db was still in use elsewhere, more often then not, an error would
occur if trying to open the db using SHIFT.
---------------------------------------------

and obviously with Access, when you upgrade, it's pretty much 'All or
Nothing'

If anyone knows of any inherent problems (like, and including this one) with
Access 2000 in terms of real-time development, I would appreciate any
advice, comments or workarounds .

Thanks

Jado
 
Hi Jado

The problem you refer to is the monolithic save. Access 2000/2 is incapable
of saving just the object you are changing like A97 does. That means
concurrency problems (2 people cannot work in different objects in the same
mdb at once), and it takes longer to save if the mdb has many objects.

In reality, you should have a split database, so each user has a local copy
of the application, and you can therefore modify your own local development
copy without worring about other users being in the copy you are trying to
save. Tony Toews has a Front End Updater utility to help keep everyone up to
date:
http://www.granite.ab.ca/accsmstr.htm

Other issues you may face in making the version jump:

1. Consider keeping your A97 as well as A2000. You can do that with a custom
install of Office 2000. If you have any difficulty getting them to co-exist,
see michka's article at:
http://www.trigeminal.com/usenet/usenet019.asp?1033

2. If a database will not convert, use A97 to decompile it first. While
Access is not running, enter this at the prompt. It's one line, and include
the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\My Documents\MyDatabase.mdb"

3. If you use ActiveX controls (such as calendar), the version will probably
break when converted. Fix it through References on the Tools menu (from a
code window).

4. By default, A2000 and 2002 do not include a reference the DAO library, so
you need to set up a reference to Microsoft DAO 3.6. More info. on
references:
http://allenbrowne.com/ser-38.html

5. A2000/2002 do include a reference to the ADO library, which duplicates
objects such as Recordset, Field, Property. Either remove the ADO reference,
or explicitly disambiguate everywhere in your application, e.g.:
Dim rs As DAO.Recordset

6. The "Name AutoCorrect" has myriads of bugs and performance problems. Turn
it off immediately you create any A2000 database. More info:
http://allenbrowne.com/bug-03.html

7. There are new properties that give performance problems (such as
SubDataSheetName for tables), and some existing properties have had their
defaults changed in a way that could compromise your validation code (e.g.
AllowZeroLength for code). More info:
http://allenbrowne.com/bug-09.html

8. There are serious problems with some of the new features in JET 4, such
as the Decimal data type. More info:
http://allenbrowne.com/bug-08.html

9. Instead of the integrated development environment in A97, A2000 and later
have a version of Visual Basic cobbled together with the main Access window.
That's handy if you with with dual monitors, but does cause other problems
from minor annoyances with way the windows behave through to Access getting
completely confused about what goes with what if you have different versions
of Access open at the same time, thereby corrupting the database.

10. It is absolutely essential to have the service packs for A2000/2. The
bugs are so many and so drastic that simply importing code from A97 and
closing your database is enough to corrupt it if you do not have the service
packs!!! Details:
http://support.microsoft.com/?id=304548

11. Many of the known bugs in A97 have not been fixed. Hundreds more were
introduced in A2000, and many of those remain unfixed in A2003. In our
experience, you can expect to waste time with a couple of new bugs you have
not come across before in every mdb you develop. Some of these are listed
here:
http://members.iinet.net.au/~allenbrowne/tips.html#flaws

HTH
 
Hi Allen

Thanks for your detailed response.

After reading your comments, I'm almost convinced that upgrading to A2000 is
probably not worth the trouble.
The problem you refer to is the monolithic save. Access 2000/2 is incapable
of saving just the object you are changing like A97 does. That means
concurrency problems (2 people cannot work in different objects in the same
mdb at once), and it takes longer to save if the mdb has many objects.
Click to expand...

do you mean 2 developers working in design view?? (I would be the only one
needing to access objects in design view)?

if you mean users, we have upto 8 users working in 1 db, using a possible
8-15 forms!

As I mentioned, the main reason for the upgrade was to take advantage of the
installation wizard that comes with Office 2000. As it happens, all the
default settings for Access are the ones I use anyway. I think I'll
continue with the upgrade but continue to use Access 97 as creating split
db's for all users would be hard work considering how many db's we build
here.

out of interest, after reading about all those possible problems, would you
mind telling me, in your opinion, the main benefits an upgrade from Access
97 to 2000 would bring.

I've read about the new features of A2000, but as I've never dabbled, I
wouldn't know which features have proved to be well worth while, and which
were a waste of time!

Thanks again

Jado
 
Comments interspersed.

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

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

Jado said:
The problem you refer to is the monolithic save. [snip]
Click to expand...

do you mean 2 developers working in design view?? (I would be the only one
needing to access objects in design view)?
Click to expand...

If any other user is *using* (not only if they are designing) any form or
report in the database, you will have problems saving in A2000 and later.
out of interest, after reading about all those possible problems, would you
mind telling me, in your opinion, the main benefits an upgrade from Access
97 to 2000 would bring.

I've read about the new features of A2000, but as I've never dabbled, I
wouldn't know which features have proved to be well worth while, and which
were a waste of time!
Click to expand...

If you store your data in Access tables, the only new, useful feature that
works without problems is that Forms have an undo event.

AutoCorrect causes more problems than it solves.
Conditional Formatting is a nice idea, but buggy.
The new field types do not work correctly.
The new relationship types are not supported in the interface.
You have to reset problem properties every time you create a table, text
field, for form.
Many of the bugs have been present for 4 years, and are not being fixed.

So why upgrade? If you are working with JET/Access tables, they only reason
is because you have support clients who are using later versions.
 
Thanks Allen

I'll be sticking with Access 97

just one last thing though...

if I needed to start using SQL Server to store Tables of an Access 97 front
end, would this force the need to upgrade.

if not, are there many major benefits of using A2000 appose to A97 in
conjunction with SQL Server 2000

I'll let you go now :)

Your comments have proved invaluable in helping me make the right decision

Many Thanks

Jado

Allen Browne said:
Comments interspersed.

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

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

Jado said:
The problem you refer to is the monolithic save. [snip]
Click to expand...

do you mean 2 developers working in design view?? (I would be the only one
needing to access objects in design view)?
Click to expand...

If any other user is *using* (not only if they are designing) any form or
report in the database, you will have problems saving in A2000 and later.
out of interest, after reading about all those possible problems, would you
mind telling me, in your opinion, the main benefits an upgrade from Access
97 to 2000 would bring.

I've read about the new features of A2000, but as I've never dabbled, I
wouldn't know which features have proved to be well worth while, and which
were a waste of time!
Click to expand...

If you store your data in Access tables, the only new, useful feature that
works without problems is that Forms have an undo event.

AutoCorrect causes more problems than it solves.
Conditional Formatting is a nice idea, but buggy.
The new field types do not work correctly.
The new relationship types are not supported in the interface.
You have to reset problem properties every time you create a table, text
field, for form.
Many of the bugs have been present for 4 years, and are not being fixed.

So why upgrade? If you are working with JET/Access tables, they only reason
is because you have support clients who are using later versions.
Click to expand...
 
A2000 and later support the ADP, to facilate linking to SQL Server, with the
ADO library.

No reason why you can't just link the SQL Server tables in A97 though, and
continue to use DAO as you have done.
 
No reason why you can't just link the SQL
Server tables in A97 though, and
continue to use DAO as you have done.
Click to expand...

I understand that knowledgeable Microsoft insiders have conceded that
MDB-DAO-Jet-ODBC-Server is, generally, preferrable to ADP-ADODB-Server. And
I have used Access 97 databases via ODBC with other server databases besides
Microsoft SQL Server, which you can't do with an ADP, last I heard.

I'm doing paying work right now on an ADP, though, and it's not an unusable
technology. :-)

When someone says "ADP" now, I just say "Oh." I used to spit. :-)

Larry Linson
Microsoft Access MVP
 
Larry Linson said:
I understand that knowledgeable Microsoft insiders have conceded that
MDB-DAO-Jet-ODBC-Server is, generally, preferrable to ADP-ADODB-Server. And
I have used Access 97 databases via ODBC with other server databases besides
Microsoft SQL Server, which you can't do with an ADP, last I heard.

I'm doing paying work right now on an ADP, though, and it's not an unusable
technology. :-)

When someone says "ADP" now, I just say "Oh." I used to spit. :-)
Click to expand...

When they provide the ability to connect to multiple databases (from any
vendor that provides an OLEDB driver) within the same project *then* I can
get excited about them.
 
When they provide the ability to connect
to multiple databases (from any vendor
that provides an OLEDB driver) within
the same project *then* I can
get excited about them.
Click to expand...

Most of the Access clients that I have worked on as "paying work" over the
last ten years used databases other than MS SQL Server, all, of course, ODBC
compliant. In most cases, the DBA's used InterSolv ODBC drivers (I believe
InterSolv has been acquired by another firm, now) which they thought were
usually better than the server DB manufacturer's own.

The "big deal" about ADP-ADODB-MS SQL Server was supposed to be "superior
performance" but I see no performance advantage in the ADP that I am working
on now, over the MDB-ODBC arrangements I've worked on in the past. On the
other hand, design and implementation are major factors in performance, and
this one could clearly use some redesign.

In the fulness of time, however, given that Jet is now "in maintenance
mode", we'll have to find _some_ other solution. "Classic ADO" is a
dead-end... ADO.NET is already available, and quite different. I am hoping,
and have expressed my hope to Microsoft, that they will come up with
something that is as seamless and as capable as Jet and DAO (and that I
wouldn't mind a great deal if that were a "resurrected" Jet and DAO, but
that is "hope beyond hope", I am sure).

BTW, I still spit whenever I think about the ADO "Find" that only allows you
to specify criteria for one, single, little, lonely field.

Larry Linson
 
Back
Top