Thinking about migrating to Access 2002

[Mike Dressel]

I am currently using a progress database for about 25
users. I am finally to the point where I want to migrate
to something else and one of my options would be Access
2002. I have just two questions/reservations about Access:

1.) I have heard that Access does not handle multi-
users reading and writing to the database very well.. Is
this true?
2.) I have also heard that if you lay your Access
database onto a SQL Server that it will take care of my
question #1. Is this true?

 

[John Vinson]

I am currently using a progress database for about 25
users. I am finally to the point where I want to migrate
to something else and one of my options would be Access
2002. I have just two questions/reservations about Access:

Migrate from where? It can make a big difference in both the ease of
conversion and in the amount of mental gearshifting required!

1.) I have heard that Access does not handle multi-
users reading and writing to the database very well.. Is
this true?

Not in my experience. 25 users heavily updating tables at the same
time would be toward the upper end of what would be comfortable - 30
is a good rule of thumb; but 20 read-only users and 5 folks updating
would be no problem; and 25 *occasional* users would be a cakewalk.

2.) I have also heard that if you lay your Access
database onto a SQL Server that it will take care of my
question #1. Is this true?

You can use Access as a frontend to SQL; this is, of course, a good
bit more work and expense than doing the project in native Access. One
option to consider is that Access2002 comes with "MSDE" - a version of
SQL/Server with a limit of five simultaneous open connections. With a
properly designed frontend this is NOT a severe limitation, since each
user will open a "connection" only briefly to send or retrieve data.
It might be worthwhile to implement initially in MSDE with the plan to
move to SQL later.

 

[Joe Fallon]

I agree 99%.
MSDE is the exact same database engine as SQL Server.
There is a 2GB limit on data (just like Access.)

The one nitpick has to do with "a limit of five simultaneous open
connections."
This isn't quite right.
The number of connections can be significantly higher.
There is a limit on the number of concurrent processes. (It is 5.)
As stated, each user will only use a process for a very short time
(typically in milliseconds) so it is possible that a few hundred
simultaneous users could be supported.
If additional processes are needed, MSDE gives them to you but a "governor"
kicks in and slows them down until the number of concurrent processes is 5
or less.
MSDE provides a command to show you how often you exceed the 5 processes so
you can tell if it is time to upgrade to a full version of SQL Server.

--
Joe Fallon
Access MVP