By Garry Robinson

Introduction

During the years of programming Access databases, it has always been that if the user could get to the command button, 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 or Access 2002. 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

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

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.

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

Whilst writing the article, I collected my most useful links and have published them at this location.
..\links\access_security.htm

Other Pages On This Site You Might Like To Read

Consolidate your data with queries
Remote Queries In Microsoft Access
Uncorrupt your Access database - plenty of options
Samples of Developer Workgroup Security Manipulation
Samples of Data Security and Database Passwords

  Your Sample Database Is Called   "ADOPwd.zip"

Sample database is suited to Access 2000, 2002, 2003 and when you use mdb files in 2007

If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.

 

Click on the following button to jump to the next page in the document loop.