Access 2000 as multiuser back-end DB

[hirenJ]

Hi all, im currently developing a marketing app that is to be used by up to 4-5 users in a multi-user scenario.

The traffic would not be particularly intensive, and concurrent record access shouldnt be a common problem.

If I use access 2000 as a back-end DB, will it survive these requirements? There is a lot of controversy as to how many users access can handle concurrently - the stories say it can run from 10 to 150 users successfully -- whats true?

What do I need to do / what options do i need to set to ensure that my app can handle these 4 users concurrently?

Cheers

Hiren

)

 

[scking]

Access is built to be a multi-user database. You don't need to do anything but you can select locking parameters in the tools/options/advanced tab.

No one, and I mean, no one can tell you as a fact what is an opinion without criteria. If you read the specification and it say it will handle 150 simultaneous connections I do expect it to be able to handle 5. 4/5 users is a very small number of connections so unless you have a very large database that does a lot of number or data crunching you should have no trouble.

I assume you are using the Microsoft Jet 4.0 engine and not MSDE or SQL Server.

Default Open Mode
Default Record Locking

Steve King Growth follows a healthy professional curiosity

 

[UncleJack]

Access can handle 4-5 concurrent users, but it can be slow on occasions. Put 10 on it and it really bogs down. It's kind of like those cars that are billed as five passenger. Yes, you can put five people in them provided they're all children or midgets. You can use Access with 150 users provided 145 of them stay home on any given day.

There are a number of tricks to speed your network such as the proper record locking scheme. One trick I use is to open a DAO connection to the back end every time a user logs on and then not close the connection until log off. This way Access doesn't have to establish a backend connection every time there's a network call for data.

There are a lot of similar tips in these pages if you'll just hunt for them.

BTW if you're using Jet with DAO in A2K, there's no advantage over A97 since the only differences between A97's Jet 3.6 and A2K's Jet 4.0 are merely cosmetic. A97 is quite a bit more stable than A2K and VBA programming is a trifle easier in A97.

Uncle Jack

 

[demoman]

You did not say what you are using for a front-end. I have many applications using VB6, with Access as the back-end data source. They work very well, for up to 10 concurrent users, in a LAN environment. They do NOT perform well across a T1 WAN, regardless of the number of concurrent connections.

SQL Server, on the other hand, performs very well in the same WAN environment.

If you use VB6, try to stay away from the ADODC control. Use ADO connection, command, and recordset objects. Access will work much more efficiently.

I have also created some workgroup apps entirely with Access. They perform quite satisfactory. Still, I would defer to others in this forum, who have much more experience working within this type of environment.

Cheers

 

[hirenJ]

Thanks for all your tips!!

I forgot to mention that im using an acc 2000 front end...
not the ideal -- but this shouldnt make much difference should it?


Hj

 

[ghubbell]

Geeze...I'm sorry but I have to meddle here and say a few words regarding your question and some of the replies:
Access 2000 is as different from 97 as good is to bad. 97 being the bad. I've seen more corrupt 97 databases than 2000's and the corruption occured 'from within'. Using 2000 you are able to manipulate your visual basic code as a completely seperate 'animal'. This and this alone is worth every penny. In 2000 you may work through a main form right in to a subform. In 2000 you do have the very desireable conditional formatting, and another half dozen bonuses that would speed your development time and 'user friendliness'. Users? The big key is your network and how you're requesting the data that's behind the queries, forms or reports. A properly designed Access database can handle over a hundred users with no speed issues, that's a fact... Trick is to not try to jam too much info down the pipe, and the pipe should be 100 base T capable. Make certain record sources or row sources are kind of 'user defined', and that you're not "spooling up the entire table behind the form" - unless the table is small.
Yes to the correct record locking setup for your needs. Consider using local 'Temp' tables that'll pump the data over to the server in batches, then empty. Watch for dodos that lock up a record or records, then leave for lunch = timed auto-save functions?!
97 versus 2000? My vote is 2000 hands down. 2002 versus 2000? That's another rant.

Gord
gord@ghubbell.com