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 datasheet. 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 datasheet. 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
