RE: Using Access with concurrent users

From: Jeff C (JeffC_at_discussions.microsoft.com)
Date: 01/24/05 

Date: Mon, 24 Jan 2005 12:07:06 -0800

Michael,

Thanks for the detailed reply. I'll give all these things a shot.

Thanks again!

Jeff

"Michael Miller" wrote:

> Hi Jeff,
>
> You can always find stuff on "multiuser application", starting with help.
> Assuming Access 2003:
>
> I had to cut this off to about 30000 characters, the complete help is 74000
> characters.
>
> See way below, at end of this stuff, for getting a user's network login
> name...
>
> Here's a section from Help-
> Improve performance of an Access database
>
> If you are optimizing the performance of an Access database, the best place
> to start is with the Performance Analyzer. You can use the Performance
> Analyzer to analyze a whole database or just selected objects in a database.
> The Performance Analyzer can also make some proposed changes for you if you
> want.
>
> Run the Performance Analyzer
>
> You can use the Performance Analyzer to optimize the performance of an
> Access database. The Performance Analyzer is not available in an Access
> project (Microsoft Access project: An Access file that connects to a
> Microsoft SQL Server database and is used to create client/server
> applications. A project file doesn't contain any data or
> data-definition-based objects such as tables and views.).
>
> Open the Access database you want to optimize.
> On the Tools menu, point to Analyze, and then click Performance.
> Click the tab for the type of database object (database objects: An Access
> database contains objects such as tables, queries, forms, reports, pages,
> macros, and modules. An Access project contains objects such as forms,
> reports, pages, macros, and modules.) that you want to optimize. Click the
> All Object Types tab to view a list of all database objects at once.
> Select the names of the database objects that you want to optimize. Click
> Select All to select all database objects in the list.
> Repeat steps 3 and 4 until you've selected all the objects you want to
> optimize, and then click OK.
> The Performance Analyzer lists three kinds of analysis results:
> Recommendation, Suggestion, and Idea. When you click an item in the Analysis
> Results list, information about the proposed optimization is displayed in the
> Analysis Notes box below the list. Suggestion optimizations have potential
> tradeoffs that you should consider before performing them. To view a
> description of the trade-offs, click a Suggestion in the list and then read
> the information in the Analysis Notes box. Access can perform Recommendation
> and Suggestion optimizations for you. You must perform Idea optimizations
> yourself.
>
> Click one or more of the Recommendation or Suggestion optimizations you want
> performed, and then click Optimize. The Performance Analyzer will perform the
> optimizations and then mark them as Fixed. Continue this process until the
> Performance Analyzer has completed all Recommendations and Suggestions that
> you want it to perform.
> To perform all Recommendation and Suggestion optimizations, click Select
> All, and then click Optimize.
>
> To perform an Idea optimization, click the optimization, and then follow the
> instructions displayed in the Analysis Notes box.
>
> Note The Performance Analyzer doesn't provide suggestions on how to improve
> the performance of Access itself or the system it is running on.
>
> Improve the performance of Access and your system
>
> The following guidelines can help you optimize the performance of Access on
> your computer:
>
> If you're using databases that other users don't need to share, install
> Access and all your databases on your hard disk drive rather than on a
> network server.
> If you are the only person using a database, open the database for exclusive
> use; in the Open dialog box, click the arrow next to Open button, and then
> click Open Exclusive.
> Make more memory available by closing applications that you aren't using.
> Increase RAM on your computer. 40 MB of memory is recommended — 32 MB of
> memory plus an additional 8 MB of memory for Access.
> Don't use any of your RAM for a RAM disk.
> Periodically delete unnecessary files and empty your Recycle Bin, compact
> your databases, and then defragment your hard disk with the Microsoft Windows
> Disk Defragmenter. To run the Disk Defragmenter, click the Windows Start
> button, point to Programs, point to Accessories, point to System Tools, and
> then click Disk Defragmenter.
> In most cases, the default virtual memory setting used by Windows should
> perform optimally. However, in some situations, adjusting virtual memory
> parameters can improve performance. If you've already tried deleting
> unnecessary files and you still have a performance problem, try changing the
> default virtual memory setting in the following cases:
> You don't have much disk space available on the drive that is currently
> being used for virtual memory, and another local drive with space is
> available.
> Another local drive is available that is faster than the current drive
> (unless that disk is heavily used).
> In these cases, you might get better performance by specifying a different
> drive for virtual memory.
>
> You also might get better performance by specifying that the disk space
> available for virtual memory be at least 25 MB minus available RAM. For
> example, if your computer has 12 MB of RAM, you should specify at least 13 MB
> of virtual memory. You may want to specify more if you are running several
> large applications.
>
> To change Windows virtual memory parameters, in Windows Control Panel,
> double-click the System icon, click Performance Options under the Advanced
> tab, click the Change button in the Virtual Memory section, and then specify
> a different hard disk, or enter a value in the Initial size box that is at
> least 25 MB minus your available RAM.
>
> If you have a wallpaper (full-screen background) bitmap on your Windows
> desktop, replace it with a solid color or pattern bitmap, or no bitmap at
> all.
> If you use a screen saver, use a blank screen saver or consider not using
> one at all.
> Eliminate disk compression software, or consider moving your databases to an
> uncompressed drive.
> To ensure optimal performance, use substitution fonts only if you need
> dual-font support to display all of your data.
> Improve table performance
>
> Use the Performance Analyzer to analyze specific tables in your database.
>
> Design tables without redundant data. A well-designed database is a
> prerequisite for fast data retrieval and updates. If existing tables contain
> redundant data, you can use the Table Analyzer Wizard to split your tables
> into related tables to store your data more efficiently.
>
> Choose appropriate data types (data type: The characteristic of a field that
> determines what type of data it can hold. Data types include Boolean,
> Integer, Long, Currency, Single, Double, Date, String, and Variant
> (default).) for fields; this can save space in your database and improve join
> (join: An association between a field in one table or query and a field of
> the same data type in another table or query. Joins tell the program how data
> is related. Records that don't match may be included or excluded, depending
> on the type of join.) operations. When defining a field, choose the smallest
> data type or field size that's appropriate for the data in the field.
>
> Create indexes (index: A feature that speeds up searching and sorting in a
> table based on key values and can enforce uniqueness on the rows in a table.
> The primary key of a table is automatically indexed. Some fields can't be
> indexed because of their data type.) for fields you sort, join, or set
> criteria for. You can make dramatic improvements in the speed of queries by
> indexing fields on both sides of joins, or by creating a relationship
> (relationship: An association that is established between common fields
> (columns) in two tables. A relationship can be one-to-one, one-to-many, or
> many-to-many.) between those fields and indexing any field used to set
> criteria for the query. Finding records through the Find dialog box is also
> much faster when searching an indexed field.
>
> Indexes aren't appropriate in all cases, however. Indexes add to the size of
> the .mdb file, reduce concurrency (the ability of more than one user to
> modify a page at the same time) in multiuser applications, and decrease
> performance when you update data in fields that are indexed or when you add
> or delete records. It's a good idea to experiment to determine which fields
> should be indexed. Adding an index may speed up a query by one second, but
> slow down adding a row of data by two seconds and cause locking problems. Or,
> it may add negligible gains, depending on which other fields are indexed. For
> example, adding an index to a PostalCode field may provide very little
> performance gain if a CompanyName field and LastName field in the table are
> already indexed. Regardless of the types of queries you create, you should
> only index fields that have mostly unique values.
>
> In a multiple-field index, use only as many fields in the index as necessary.
>
> Improve performance of linked tables
>
> Note You can link (link (tables): An action that establishes a connection
> to data from another application so that you can view and edit the data in
> both the original application and in Access.) a table only in an Access
> database, not an Access project (Microsoft Access project: An Access file
> that connects to a Microsoft SQL Server database and is used to create
> client/server applications. A project file doesn't contain any data or
> data-definition-based objects such as tables and views.).
>
> Although you can use linked tables in other files or in an SQL database as
> if they were tables in your Access database, it's important to keep in mind
> that they aren't physically in your Access database. Each time you view data
> in a linked table, Access has to retrieve records from another file. This can
> take time, especially if the linked table is on a network or in an SQL
> database (SQL database: A database that is based on Structured Query Language
> (SQL).).
>
> If you're using a linked table on a network or in an SQL database, follow
> these guidelines for best results:
>
> You can greatly enhance performance, when opening the main database and
> opening tables and forms, by forcing the linked database to remain open. To
> do this, create an empty table in the linked database, and link the table in
> the main database. Then, use the OpenRecordset method to open the linked
> table. This prevents the Microsoft Jet database engine from repeatedly
> opening and closing the linked database and from creating and deleting the
> associated .ldb file.
> View only the data that you need. Don't move up and down the page
> unnecessarily in the data***. Avoid jumping to the last record in a large
> table. If you want to add new records to a large table, use the Data Entry
> command on the Records menu to avoid loading existing records into memory.
> Use filters or queries to limit the number of records that you view in a
> form or data***. This way, Access can transfer less data over the network.
> In queries that involve linked tables, avoid using functions in query
> criteria. In particular, avoid using domain aggregate functions (domain
> aggregate function: A function, such as DAvg or DMax, that is used to
> calculate statistics over a set of records (a domain).), such as Dsum,
> anywhere in your queries. When you use a domain aggregate function, Access
> retrieves all of the data in the linked table to execute the query.
> If you often add records to a linked table, create a form for adding records
> that has the DataEntry property set to Yes. When you open the form to enter
> new data, Access won't display any existing records. This property setting
> saves time, because Access doesn't have to retrieve the records in the linked
> table.
> Remember that other users might be trying to use an external table at the
> same time you are. When an Access database is on a network, avoid locking
> records longer than necessary.
> Improve performance of tables linked to tables in a SQL Server database
>
> If the data in your front-end/back-end application (front-end/back-end
> application: An application consisting of a "back-end" database file that
> contains tables, and copies of a "front-end" database file that contain all
> other database objects with links to the "back-end" tables.) consists only of
> Microsoft SQL Server tables, you can use either an Access project or an
> Access database as the front end for your application. However, by using an
> Access project, you will benefit from the advantages of using projects, such
> as allowing a larger number of tables and a larger database size. If you want
> the data in your front-end/back-end application to consist of both Access
> tables and Microsoft SQL Server tables, you must use an Access database as
> the front end.
>
> If you're connecting to an external SQL database table, you can achieve the
> best performance results by linking (link (tables): An action that
> establishes a connection to data from another application so that you can
> view and edit the data in both the original application and in Access.) to
> the SQL tables, instead of opening the tables directly. You can open external
> SQL tables directly only by using Microsoft Visual Basic for Applications
> (VBA) code. Linked tables are considerably faster, more powerful, and more
> efficient than directly-opened tables.
>
> Additional performance tips
>
> Retrieve only the data you need. Design your queries to limit the number of
> records that you retrieve, and select only the fields you need, so that
> Access can transfer as little data as possible over the network.
> Don't use updatable result sets (dynasets) if you're not retrieving many
> records, are not updating the data, and don't need to see changes made by
> other users. To prevent result sets from being updated in a form, set the
> form's RecordSetType property to Snapshot. Snapshots are faster to open and
> scroll through than dynasets.
> If you need to retrieve a large number of records, using a dynaset is faster
> and more efficient than a snapshot. For example, moving to the end of a
> snapshot requires the entire result set to be downloaded to the local
> computer, but with a dynaset, only the last screen of data is downloaded to
> the local computer. In addition, the fastest way to add new records to a
> table, form, or query is to click Data Entry on the Records menu. (Data Entry
> isn't available if the RecordsetType property is set to Snapshot.)
> Use cache memory. If you will reuse the most recent data from the server
> while the application is running, it's faster to retrieve a single large
> chunk of data (many rows) and store it in a cache than it is to retrieve many
> individual rows. Access forms and datasheets automatically use a cache. If
> you are retrieving data by using a Recordset object created in Microsoft
> Visual Basic for Applications (VBA) code, you can use the CacheSize property
> to specify how many records to retrieve at one time into local memory.
> For bulk update queries against ODBC data sources, optimize performance on
> the server by setting the FailOnError property to Yes.
> Avoid using queries that cause processing to be done on a local client
> computer. When accessing external data, the Jet database engine processes
> data locally only when the operation can't be performed by the external
> database server. Query operations performed locally (as defined by the SQL
> commands used to implement them) include:
> JOIN operations between tables from different remote data sources. (For
> example, assume that the join involves a local table or query with few
> records and a remote table with many more records, and the remote table's
> join field is indexed. In this case, Access returns only the records that
> match the local table or query, thus greatly improving query performance.)
> JOIN operations based on a query with the DISTINCT predicate or a GROUP BY
> clause.
> Outer joins (outer join: A join in which each matching record from two
> tables is combined into one record in the query's results, and one table
> contributes all of its records, even if the values in the joined field don't
> match those in the other table.) containing syntax not supported by the
> server.
> DISTINCT predicates containing operations that can't be processed remotely.
> The LIKE operator used with Text or Memo fields (may not be supported by
> some servers).
> Multiple-level GROUP BY arguments and totals, such as those used in reports
> with multiple grouping levels.
> GROUP BY arguments based on a query with a DISTINCT predicate or a GROUP BY
> clause.
> Crosstab queries that have more than one aggregate, that have field, row, or
> column headings that contain aggregates, or that have a user-defined ORDER BY
> clause.
> TOP n or TOP n PERCENT predicates.
> User-defined functions, or operators or functions that aren't supported by
> the server.
> Complex combinations of INNER JOIN (inner join: A join where records in two
> tables are combined in a query's results only if values in the joined fields
> meet a specified condition. In a query, the default join is an inner join
> that selects records only if values in the joined fields match.), LEFT JOIN
> (left outer join: An outer join in which all the records from the left side
> of the LEFT JOIN operation in the query's SQL statement are added to the
> query's results, even if there are no matching values in the joined field
> from the table on the right.), or RIGHT JOIN (right outer join: An outer join
> in which all the records from the right side of the RIGHT JOIN operation in
> the query's SQL statement are added to the query's results, even if there are
> no matching values in the joined field from the table on the left.)
> operations in nested queries.
> Improve performance in a multiuser environment
>
> The following guidelines can help you optimize the performance of Access
> databases that are used in a multiuser environment.
>
> Put only the tables on a network server and keep other database objects on
> users' computers. The database's performance will be faster because only data
> is sent across the network. You can separate the tables from other database
> objects by using the Database Splitter Wizard.
> Choose a suitable record-locking strategy. If you use optimistic locking (No
> Locks), Access doesn't lock a record or page until a user saves changes to
> it, and data might be more readily available as a result. If you use
> pessimistic locking (Edited Records), Access locks the record as soon as a
> user begins to edit it — records might be locked for longer periods of time,
> but users don't have to decide whose changes to save if two users edit the
> same record simultaneously.
> Choose the appropriate locking level strategy (page-level or record-level
> locking).
> Avoid locking conflicts by adjusting Refresh Interval, Update Retry
> Interval, Number Of Update Retries, and ODBC Refresh Interval settings (if
> applicable).
> Optimize the performance of tables (including tables linked to other data
> sources, such as Microsoft SQL Server).
> Convert the workgroup information file (workgroup information file: A file
> that Access reads at startup that contains information about the users in a
> workgroup. This information includes users' account names, their passwords,
> and the groups of which they are members.) from Access 97 or earlier to the
> current version of Access.
> Improve query performance
>
> Databases, tables, and indexes
>
> Compact your database. Compacting can speed up queries because it
> reorganizes a table's records so that they reside in adjacent database pages
> ordered by the table's primary key (primary key: One or more fields (columns)
> whose values uniquely identify each record in a table. A primary key cannot
> allow Null values and must always have a unique index. A primary key is used
> to relate a table to foreign keys in other tables.). This will improve the
> performance of sequential scans of a table's records because the minimum
> number of database pages will have to be read to retrieve all of the records.
> After compacting the database, run each query to compile it using the updated
> table statistics.
> Index (index: A feature that speeds up searching and sorting in a table
> based on key values and can enforce uniqueness on the rows in a table. The
> primary key of a table is automatically indexed. Some fields can't be indexed
> because of their data type.) any field used to set criteria (criteria:
> Conditions you specify to limit which records are included in the result set
> of a query or filter.) for the query and index fields on both sides of a join
> (join: An association between a field in one table or query and a field of
> the same data type in another table or query. Joins tell the program how data
> is related. Records that don't match may be included or excluded, depending
> on the type of join.), or create a relationship (relationship: An association
> that is established between common fields (columns) in two tables. A
> relationship can be one-to-one, one-to-many, or many-to-many.) between these
> fields. When you create relationships, the Microsoft Jet database engine
> (Microsoft Jet database engine: The part of the Access database system that
> retrieves and stores data in user and system databases. It can be thought of
> as a data manager upon which database systems, such as Access, are built.)
> creates an index on the foreign key (foreign key: One or more table fields
> (columns) that refer to the primary key field or fields in another table. A
> foreign key indicates how the tables are related.) if one does not already
> exist; otherwise, it uses the existing index.
> The Jet database engine automatically optimizes a query that joins an Access
> table on your hard drive and an ODBC (Open Database Connectivity (ODBC): A
> standard method of sharing data between databases and programs. ODBC drivers
> use the standard Structured Query Language (SQL) to gain access to external
> data.) server table if the Access table is small and the joined fields are
> indexed. In this case, Access improves performance by requesting only the
> necessary records from the server. Make sure tables you join from different
> sources are indexed on the join fields.
>
> Index the fields you use for sorting.
> Use the Between...And, the In, and the = operators on indexed fields.
> Fields, calculations, and criteria
>
> When defining a field in a table, choose the smallest data type (data type:
> The characteristic of a field that determines what type of data it can hold.
> Data types include Boolean, Integer, Long, Currency, Single, Double, Date,
> String, and Variant (default).) appropriate for the data in the field. Also,
> give fields you'll use in joins (join: An association between a field in one
> table or query and a field of the same data type in another table or query.
> Joins tell the program how data is related. Records that don't match may be
> included or excluded, depending on the type of join.) the same or compatible
> data types, such as AutoNumber and Number (if the FieldSize property is set
> to Long Integer).
> When creating a query, add only the fields you need. In fields used to set
> criteria (criteria: Conditions you specify to limit which records are
> included in the result set of a query or filter.), clear the Show check box
> if you don't want to display those fields.
> If you use criteria to restrict the values in a field used in a join between
> tables with a one-to-many relationship (one-to-many relationship: An
> association between two tables in which the primary key value of each record
> in the primary table corresponds to the value in the matching field or fields
> of many records in the related table.), test whether the query runs faster
> with the criteria placed on the "one" side or the "many" side of the join. In
> some queries, you get faster performance by adding the criteria to the field
> on the "one" side of the join instead of the "many" side.
>
> THERE IS 50% MORE INFO IN THE HELP, THAN I SHOWED HERE.
>
> Regarding users in the db and getting them out.
>
> There is an API function that will tell you the machine names of the people
> in the mdb, but without a list of those names matched to your users, it
> doesn't help.
>
> There is also an API to find the login name, like "mmiller", of the user
> when they open your main or another form. What you have to do then, is to
> insert that login name into a shared table, with the datetime of when they
> went in, and then since you have an Exit button on your main form for them to
> get out, you can put that datetime in when they leave. You do need to match
> up the login names to their names, phone nos, depts, etc. in your table. You
> may have a user table already, which is a good table to hook up to or expand.
>
> I am not aware of a way to kick a user out of the program, but if you wanted
> to get fancy, you could generate an email to the user involved, if you also
> put their email address in your user table. I have not done this yet.
>
> To get the user's network login name, I used:
>
> Option Compare Database
> Option Explicit
>
> Private Declare Function apiGetUserName Lib "C:\WINNT\system32\advapi32.dll"
> Alias _
> "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
>
> 'Global variables to pass from form to form.
> Public gvarInterParentProjectID As Variant
> Public gvarInterProjectID As Variant
> Public gvarInterItem As Variant
>
> ' These represent the possible returns errors from API.
> Public Const ERROR_BAD_DEVICE = 1200&
> Public Const ERROR_CONNECTION_UNAVAIL = 1201&
> Public Const ERROR_EXTENDED_ERROR = 1208&
> Public Const ERROR_MORE_DATA = 234
> Public Const ERROR_NOT_SUPPORTED = 50&
> Public Const ERROR_NO_NET_OR_BAD_PATH = 1203&
> Public Const ERROR_NO_NETWORK = 1222&
> Public Const ERROR_NOT_CONNECTED = 2250&
> Public Const NO_ERROR = 0
>
> then the actual function is:
> Public Function fOSUserName() As String
> 'returns the netwrok login name
>
> Dim lngLen As Long
> Dim lngX As Long
> Dim strUserName As String
>
> strUserName = String$(254, 0)
> lngLen = 255
> lngX = apiGetUserName(strUserName, lngLen - 1)
> If (lngX > 0) Then
> fOSUserName = Left$(strUserName, lngLen)
> Else
> fOSUserName = vbNullString
> End If
>
> End Function
>
> Then I grab it in an Open method of my main form:
>
> Private Sub Form_Open(Cancel As Integer)
> Dim strUserName As String
>
> ' strUserName = Environ$("UserName")
> ' Select Case LCase(strUserName)
>
> Select Case LCase(fOSUserName())
>
> Case "superman"
> cmdDeveloperClose.Visible = True
> lblHello.Visible = True
> lblHello.Caption = lblHello.Caption & " Michael"
>
> and from here, I intend to post it to my user tracking table, since I know
> my login name is "superman".
>
> MichaelM
>
>
> "Jeff C" wrote:
>
> > Hi,
> >
> > I know this is a common topic, but I have some questions relating to Access
> > in a concurrent user environment that I haven't seen before. As mentioned
> > many times, Access doesn't exactly have the best performance when multiple
> > users are accessing the database at the same time. Can someone send a good
> > link of a webset that recommends how to set the database up for optimal
> > (relatively) performance? I'm aware of splitting the db up into front end
> > and back end, etc.
> >
> > Two other questions I had were relating to user management. Can I track
> > which users are accessing the database at any given point in time? Even if I
> > tracked which users were accessing the network share in which the db resides.
> > Also, I heard of opening the database exclusively, which I think means I
> > have exclusive access to the database at that time. This is what I want. I
> > want to be able to boot other users off and have complete access to the
> > database to back it up.
> >
> > If anyone has any other tips on doing this, they would be appreciated.
> >
> > Thanks,
> >
> > Jeff