Programming Microsoft Access 2000 (Microsoft Programming Series)
The first step in sharing an Access application is to make the default open mode shared. You can accomplish this using the Advanced tab of the Options dialog box. This tab also offers other options that facilitate sharing. The SetOption and GetOptions methods of the Access Application object enable your applications to set and read these settings
You place Access multi-user files in shared file directory folders. Since Access with the Jet engine is a file server as opposed to a client/server database, you should minimize the file size that travels over the physical network connection. One way to do this is to split an application into two files. You save the data tables in an Access file on the shared directory folder and distribute another Access file to individual users for use on their local workstations. The distributed file links back to the data tables in the shared folders. This design speeds performance and reduces network traffic. You can generally improve performance by including more content in the distributed file, such as data that changes infrequently. You can use automated procedures to update locally stored data at specific times, such as when an application opens or a user clicks a button.
NOTE
You can use the Database Splitter wizard to divide one Access file into two. (Choose Database Utilities from the Tools menu.) One file contains tables, and the other file holds queries, forms, reports, macros, modules, and shortcuts to data access pages (data access pages are new with Access 2000). You place the first file in a shared folder on the network and link the other file to it. You distribute the second file to individual users for use on their workstations.
You can set user-level security settings that let a user open a file exclusively (so others cannot use it). A user needs exclusive use of a database to make database modifications, such as adding new modules or revising existing ones. The Connection Control property and the User List feature are used to obtain exclusive access to a database in a multi-user environment.
The Connection Control Property
The new Connection Control property facilitates programmatic control of database accessibility. You can set the Connection Control property for an ActiveX Data Objects (ADO) Connection object to make it impossible for new users to open a database and to prohibit existing users from reconnecting to it after they close the file. The Connection Control property allows you to obtain exclusive access to the database in order to store new modules and make other application design changes once the last current user disconnects from the database.
The following two procedures change the setting of the Connection Control property. You use the Properties collection of the Connection object to set the property. A property setting of 1 sets the feature so that it closes down passively when users disconnect from the application file. A value of 2 enables other users to resume connecting to the database file. This feature is handy for developers who need to evaluate their changes after saving them in the file.
| Sub closeDBConnection() Dim cnn1 As ADODB.Connection Set cnn1 = CurrentProject.Connection cnn1.Properties("Jet OLEDB:Connection Control") = 1 End Sub Sub openDBConnection() Dim cnn1 As ADODB.Connection Set cnn1 = CurrentProject.Connection cnn1.Properties("Jet OLEDB:Connection Control") = 2 End Sub |
The User List
The User List, introduced with Jet 4, is a set of user data stored in the lock file for an .mdb file. This information is available exclusively through the Jet provider, but you can only gain access to the information through ADO. (If your database resides on a read-only file share, this feature is not available because Jet does not create lock files for read-only shares.) You invoke the OpenSchema method for a Connection object to return a recordset with four fields of information for each recent user. The method extracts data from the lock file for an .mbd file. The four fields in the recordset are
- computer_name The name of the computer the user connects from.
- login_name The login user-level security name.
- connected Indicates that a user is currently connected. (User IDs sometimes remain in the lock database after a user disconnects from a Jet database.)
- suspected_state Indicates that a connection to the database has terminated abnormally.
The following procedure invokes the User List. You reference the User List using a Globally Unique ID (GUID) that points at the control for returning the recordset of active users. The sample returns computer_name and login_name for all active users. When this list has a count of 1, a developer can save changes to modules in a database. In addition, a developer can use the list to determine who to contact to gain exclusive control of a database.
| Sub listActiveUsers() Dim cnn1 As ADODB.Connection, rst1 As New ADODB.Recordset 'Set the connection for the current project and invoke the 'OpenSchema method to return the current list of users. Set cnn1 = CurrentProject.Connection Set rst1 = cnn1.OpenSchema(adSchemaProviderSpecific, , _ "{947bb102-5d43-11d1-bdbf-00c04fb92675}") ) 'Print a heading for the User List recordset 'and enumerate list members. Debug.Print "Machine Name " & "User Name" Debug.Print "============" & " =========" Do Until rst1.EOF Debug.Print rst1.Fields("computer_name") & _ rst1.Fields("login_name") rst1.MoveNext Loop End Sub |