Upgrading your Access Application for a Multi-user Environment - Connection Object
(Page 2 of 5 )
Connection Object
Let’s start by discussing the connection object and what properties it should have. For a discussion of concurrency the most important feature is the “Mode” in which a database is opened. While Access defaults to opening a database in adModeShareDenyNone – a non-exclusive mode that will allow the user to both read from and write to a database, it does not hurt to make this explicit when opening a connection. (Of course you need not open the database in this mode if it doesn’t make sense for the current user). Find below the code for opening a connection.
Dim strpassword as String
Dim struser as String
Dim strConnect as String
Dim cnDemo as ADODB.Connection
‘assume code to retrieve username and password
‘from a dialog box
Set cnDemo = New ADODB.Connection
strConnect = “Data Source=” & App.Path & “\dbname.mdb;User ID=” & _
struser & “;Password=” & strpassword & “;Provider=MSDASQL.1”
With cnDemo
.Mode = adModeShareDenyNone
.ConnectionString = strConnect
.Properties("JetOLEDB:SystemDatabase")=App.Path & "\Secured.mdw"
.CursorLocation = adUseServer
.Open
End With
You cannot open a recordset that allows multiple users to change data in the database unless it is opened against a connection that is shared and read/write. Cursor location is also important when opening your connection. When using a server-side cursor changes made by other users are visible. Since the ConnectionString and Properties relate to security, they will be discussed later.
To summarize, the connection type is most important in the context of concurrency because no issues will arise if the mode is exclusive or if users are not allowed to change data in the database. Additionally, if a recordset is opened against a connection that is opened in the adModeRead mode, then no matter how you open your recordset you will not be able to change data and hence issues of concurrency will not arise.
Next: Recordset Object >>
More Microsoft Access Articles
More By Peter Lavin
/ 31 
