Jim Lawrence
accessd at shaw.ca
Mon Dec 10 11:53:30 CST 2007
I confer with Drew on this... one more point is to avoid the 'ORDER BY' when ever possible, if the order required is simple you can SORT your recordset or leave the ORDER BY clause to the last outer-most statement. A good point Drew, about not editing the data. Using tags and flags can control the display. Once data has been saved it should never be removed from the system. It can be flagged as deleted or archived. If ever forensic auditing is needed a complete is crucial. Can not improve on Drew's assessment, otherwise. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka Sent: Monday, December 10, 2007 8:59 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Max quantity of simultaneous users Depends on your database design. Access will physically allow up to 255 users, though I'm sure you knew that. That many people using one file, however, can get problematic. Where I work, we have several live Access based applications. And their user count varies. The ISFE is our Help Desk package. It has an Access 2000 format .mdb as the backend, with a VB 6 front end. Number of concurrent users...right now, ~140. Another big database is Glovia Reports, basically a report engine for our Oracle based ERP package. Glovia Reports can get brought down to it's knees if more then a dozen people are in it. Not to gloat, but I designed and built the ISFE. Glovia Reports, on the other hand, was built long before I got here, and I rarely do anything but provide emergency support for it. What's different between the two? First BIG block for GR (Glovia Reports) is that some 'reports' are actually macro based. These macros run various queries of the data in Oracle, and build 'temp' tables in the database. It does this using make table queries. After these queries are run, the macro then opens a report based off of these temp tables. In Access 97, this ran just fine. In the Access 2000 format, if someone has a report open, using these tables, it prevent anyone from running the make table queries. (and it gives them a goofy message...says they don't have permission, instead of saying the tables are locked). Query upon Query. The folks that write these reports are not expert query writers. They know the data, but are confused with subqueries and pass through queries. Thus, this database has tons of queries, which are usually 5 or 6 levels deep. One complete database, GR is one database, including both local tables, linked Oracle tables, forms, queries, reports, macros. All in one .mdb. In conclusion, here's some tips on how to maximize user connections: 1 - Split the database. Make a Backend with just the data, and a Front end with the interface. 1a. Using a Non-Access Front End can increase performance and max users too. An unbound approach is also helpful, but hands down, to get the absolute max number of users out of an Access .mdb, put it behind a web based interface. In theory, you will max out your IIS server before you'll ever max out the number of users in the .mdb. 2 - Use Unbound thinking. The theoretical difference between bound and unbound, is that bound is a solid 'always connected' state, where as unbound is 'catch and release'. If User A opens a data entry form, that is bound to it's data source, you are using up a connection to the database (and locking parts of that table) the entire time User A has that form open. If User B opens a data entry form that is unbound, the connection and locking is reduced to the times that User B is actually reading or writing data. 3 - Normalize your data and INDEX it. A properly normalized and indexed data structure will provide data to the users much faster. Faster data retrieval means less connection and locking time. 4 - Avoid Editing data whenever possible. For instance, in an inventory system, instead of editing a 'On Hand Count' field, create a transaction table. Positive numbers for putting something into inventory, negative for taking it out. Sum that table for the On Hand Count. Sometimes editing data is the best route, but trust me, in a good database design, you will find very few cases where data is actually edited. Those are the biggies. IMHO. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rudi Adriaenssens (De Beukelaer) Sent: Monday, December 10, 2007 9:34 AM To: Access Developers discussion and problem solving Subject: [AccessD] Max quantity of simultaneous users Hi, Has anyone an idea of the reasonable maximum simultaneous users in an access database? We have a setup of a back end database containing all data into one mdb (about 60 tables) and a separate front end mde for every user (11) with no actual data. The front end is into a separate directory for each user on the same server. All works fine, and from time to time some users ask specific extra functions who are made if possibly. Since a few weeks we have some problems, usually some error like "can not write data". Thx in front for your reply. Rudi Adriaenssens De Beukelaer Boomsesteenweg 77 B-2630 Aartselaar Belgium ######################################################################## ################ De Beukelaer mail server message: This e-mail message is privileged and confidential and property of De Beukelaer company. If you are not the intended recipient please delete the message and notify the sender. This e-mail message has been scanned for Viruses and Content. ######################################################################## ################ -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com