Microsoft Access Passwords, Workgroups and ADO

<< Click to Display Table of Contents >>

Navigation:  Protection and Security >

Microsoft Access Passwords, Workgroups and ADO

Garry Robinson

During the years of programming Access databases, it has always been that if the user could get to the database container, then the user had already passed the security provisions that had been established. Now I find more and more that I am writing code in Excel or VB or other databases and I need to be sure that I can still manipulate secure data. This article shows you how to make an ADO connection to a database that is secured by either a database password or by a workgroup file.
 

The Download Database
 
If you wish to try these examples, you will require a copy of Access 2000 to 2003. The samples include a password secured database, an unsecured database which contains most of the software in this article plus a database that is secured by a workgroup. There is also a workgroup security file, plus a snapshot report that shows the workgroup security setup for this database.
 
dbPwdIshello.mdb
dbNoPwd.mdb
dbWorkgroup.mdb
dbWorkgroup.mdw
dbWorkgroup.snp
 
 
Before starting any of these examples, please return to the Access security file that was distributed with your original version of Microsoft Access. You need to do this to ensure that the samples will work correctly. Those of you who do not understand what I’m talking about this stage, it is very likely that you will be using the standard local Access security workgroup file and you are ready to use the samples anyway. Another way to put this is to make sure that you login into Access without prompts for username or password.
Downloads Are Here

NOTE: Samples have been enhanced to include support for passwords in Access 2007 databases

 

This can be purchased with all the other downloads on this page

 

Looking At Your Connection String
 
Probably the main reason that an Access programmer is unlikely to know much about incorporating passwords into an ADO connection string is the CurrentProject.Connection property. If you have successfully opened your database, you will have entered your database password or your workgroup file details already. As I was researching this topic I found it very useful to refer to the CurrentProject property. The connection property was helpful because the specific names in the string provided fruitful index words for my searching inside Access visual basic help and Google on the internet.
 
So if you could open the database called dbPwdIshello.mdb which is protected by a password of “hello”. Then open a form called Connect 1 where you will find there is one button and a check box to vary how the ADO connection string is displayed (see figure 1). The code under the button is as follows
 
MyConnStr = CurrentProject.Connection

If Me!ShowASText Then

 ' Now split the connection string into individual lines

 i = 0

 While InStr(MyConnStr, ";") And i < 100

   MyConnStr = repChr_FX(MyConnStr, ";", vbCrLf)
   i = i + 1
 Wend

End If
Me!txtConnection = MyConnStr
 
adosec1

Figure 1 - Show the connection string is a readable form plus the first 10 records from the password protected database
 
 
The visual basic above is designed to make the ADO connection string easier to read by replacing the semi colon in the full connection string with carriage returns. Without this, it is hard to understand the connection string . The replacement of the semi colon “;” character is handled through the replace character subroutine that is shown below
 
 
Public Function repChr_FX(in_str As String, findC As String, repC As String) As String
 
Dim iPos As Integer
 
iPos = InStr(1, in_str, findC)
If iPos = 0 Then
 repChr_FX = in_str
Else
 ' Swap one string for the next
 repChr_FX = left$(in_str, iPos - 1) & repC & Mid$(in_str, iPos + 1)
End If
 
End Function
 
Now there is a couple of interesting things to note about the connection string in this database. First is that the database password is not displayed. In later examples where we open this database from another database you will see that it is displayed. The other property that may be very useful is the jet workgroup file property. You may wish to refer to this to test for security breaches if someone is using the incorrect workgroup file when opening your database.
 
 
Connecting To A Password Database Using ADO
 
Now I will demonstrate how to open a database password protected database. To see this and the remaining samples discussed in this article close the dbPwdIsHello database and open the database called dbnopwd.mdb. Please note that all the following samples do not need to reside in an Access database. Alternatively you could put them in a visual basic program, Excel or even Microsoft Powerpoint if you so desired. Now open the ADO 1 form in design mode (shown in figure one) and look at the code under the only button.
 
Before you make the connection string, we first need to establish the directory with the current database. The path of this file is used to locate with the password protected database. This is handled through the GetDBPath function that I seem to use in every article that I write.
 
Once we instantiate the new connection, we open the connection using one of those long connection strings that we saw in figure 1. Note that you do not need to set all of the parameters that we see in figure one. The provider will set all the parameters when you do not specify to their default values. Your code will be much simpler to read as a result of leaving out the parameters that you do not use or in my case do not understand. If you now open the form in normal mode, press the button to display your new connection string in the large text field below the button. The code to open the password protected password is shown below.
 
 
Dim strDbName As String
Dim MyConnStr As String
Dim i As Integer
Dim Cnn As ADODB.Connection
Dim myRec As ADODB.Recordset
 
strDbName = GetDBPath_FX & "dbPwdIsHello.mdb"
 
Set Cnn = New ADODB.Connection
Cnn.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDbName & ";" & _
"Jet OLEDB:Database Password=hello;"
 
MyConnStr = Cnn.ConnectionString
 
 
As a connection is only of use if you do something with it, I have added code to display some information from the orders table that is in the password protected database. For those of you who love SQL, note the use of the "Top 10" rows extension to SQL which is peculiar to the Jet database.
 
 
If showRecordset = True Then
 
' Now make sure that you can read a recordset
 
Set myRec = New ADODB.Recordset
 
 myRec.Open "SELECT top 10 customerID, " & "freight FROM Orders " & _
  "order by freight desc", Cnn, , , adCmdText
 
 ' Display the contents of the recordset.
 MyConnStr = MyConnStr & vbCrLf & vbCrLf & _
  "Top Ten Customers By Freight Costs:" & vbCrLf
 
 Do While Not myRec.EOF
   Me!txtConnection = MyConnStr
 
   MyConnStr = MyConnStr & myRec!CustomerID & " - " & myRec!Freight & vbCrLf
 myRec.MoveNext
Loop
myRec.Close
 
 Set myRec = Nothing
End If
 
Me!txtConnection = MyConnStr
 
 
Setting Up Secure Database
 
 
Now the security that programmers will be most familiar is workgroup file security. For this example I ran the Security Wizard (Access 2000/2002) to create a database that the admin account would not have the permissions for. For those of you have only dabbled in Access security, my advice is to practice with the security wizard, test meticulously and take lots of notes so that you can accurately recreate your steps. In particular be very careful with Access 2000 format databases, even those generated with Access 2002, because it was easy to break into these database with the ADMIN account. For the sample database called dbWorkgroup.mdb, you can check the security and personal identifier settings in the snapshot file called dbWorkgroup.mdb. I have since altered the passwords for the manager account to “manager” and the admin account to “admin”. Also note that you should not join the workgroup file until after you run the example behind the form called ADO 2.
 
Unfortunately for me, the visual basic code for selecting a workgroup file and then adding a username and password took me a long time to find the appropriate syntax. Most of the examples on the net assume that have connected to the workgroup file already. Alternatively the other examples assumed that you were using the admin account with no password. What I found that worked was to define the provider, workgroup and account properties prior to using the open method as follows.
 
Set Cnn = New ADODB.Connection
 
Cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
Cnn.Properties("Jet OLEDB:System database") = _
strDbName & ".mdw"
Cnn.Properties("Password") = "manager"
Cnn.Properties("User Id") = "manager"
 
Cnn.Open "Data Source=" & strDbName & ".mdb;"
MyConnStr = Cnn.ConnectionString
 
 
 
DSN Files
 
As the examples that I’ve given thus far would involve compiling the Software / database when a password or account name is changed, I thought it would be appropriate to show how to use an ODBC data source (called DSN) with a workgroup file. I went looking for the ODBC driver software on a Windows XP and found that the software was hidden in the windows\system32 directory rather than the control panel where it used to be in Windows 98. To find a file yourself, search for ODBCAD32.EXE on the C: Drive on your computer. Now you may wish to set up a file DSN as I have illustrated in figure 2. Once the file DSN (as distinct from the User or System DSN) is setup, its contents would similar to the following
 
DRIVER=Microsoft Access Driver (*.mdb)
UID=manager
pwd=manager
SystemDB=C:\SmartAccess\dbworkgroup.mdw
FIL=MS Access
DriverId=25
DefaultDir=C:\SmartAccess
DBQ=C:\SmartAccess\dbWorkgroup.mdb
 
Note that I have added the password for the manager account to this file to illustrate that the password (pwd) can be kept in the file. The ODBCAD32.exe program does not save the password to the text file. Now the visual basic that will open the database using a workgroup file is as follows.
 
Set Cnn = New ADODB.Connection
Cnn.Provider = "MSDASQL;FileDSN=" & strDbName & ".dsn"
Cnn.Properties("Password") = "manager"
Cnn.Properties("User Id") = "manager"
Cnn.Open
MyConnStr = Cnn.ConnectionString
 
You may have noticed that the visual basic above has no specific references to Jet 4.0. This may appeal to you as it gives you some chance to move your data to different database without changing your software. Beware though that ODBC links may hamper the performance of your software.
 
adosec2

Figure 2 – Setting Up A DSN file or path is one way to secure
 
 
Shortcuts
 
For those of you would simply like to remove the burden of entering a username and password every time you open a database, there’s a simple a technology called shortcuts that has been around since Access version 2. Access allows you to open a database with the following command line extensions
 
/wrkgrp
/user
/pwd
 
I suggest that you search Microsoft Access help for “Startup command-line options” to find the appropriate syntax. Once you’ve set up your own shortcuts you can consider adopting them for your users. Even if you store a password in the shortcut file, I will guarantee that a large percentage of your user’s will not know how to look at the shortcut properties to find the password. Anyway it is likely that they’ll write the password on the front of the computer screen for all to find out even if you leave it out of the shortcut file.
 
 
Summing Up
 
To my mind, Access security is a process of reducing the business risks for our clients. With every additional security option that you add to your database, there is generally a security penalty such as remembering a password or 20 hours additional programming. The examples that I’ve shown for ADO connections have a lot of appeal in that they allow the software to interact with your database without giving away the vital passwords. Alternatively if you are like me, any article or example that shows you how to use ADO in your software is just one step closer to the day when you no longer have to use to process your information.
 
Useful Further Reading and Resources
 
Smart Access magazine Articles Of Relevance
Look in the Index search on the LHS for ADO, DSN or security and that will find you plenty of articles to satisfy your appetite.
 
Whilst writing the article, I collected my most useful links and have published them at this location. Protection and Security
 

If you want to continue on programming in this area, I would suggest the Access Developers Handbook Enterprise Edition as it has 100 pages on programming security.

 

Your download file is called 210Robinson.ZIP in the file SA2002-10down.zip

This is found in the on this page

 

 

Other Pages On This Site You Might Like To Read

 

An Access Project, Part 1: Requirements