Another great question came in by e-mail the other day;

Is there a possibility to REALLY protect Access data in an backend database file with an Access 2003 runtime app?

At present I do not want to switch to Access 2007 as many of my customers are still working with Windows 2000.

Is there a chance to use an Access 2007 database file as backend (which seems to be secure with the 
new database password) with an Access 2003 frontend ?

Thanks for helping

H. Peter Lienhardt

As we all know, Access 2003 uses a single encryption method, which also happens to be very weak.  Unfortunately it's hard coded unlike Access 2007 which uses the much improved CryptoAPI.  

That leaves us very little scope for improving the security of data at the lowest level.  You could of course encrypt the data at VBA level, but that would require making all reading and writing operations to recordsets go through an 'encryption layer' written in VBA.  Not a nice solution, and wouldn't be particularly efficient.

Now the most interesting part of the question was:

Is there a chance to use an Access 2007 database file as backend (which seems to be secure with the 
new database password) with an Access 2003 frontend?

It turns out the answer is yes.  Well, kind of.

Since the release of Access 2007, Microsoft also released the new database engine on its own - best of all as a free redistributable download.  

So programmatically we can take advantage of this, but let's look at the problems first;

1.  You can't use direct link tables from an Access 2003 file to an Access 2007 backend.
2.  You can't use ODBC link tables from an Access 2003 file to an Access 2007 backend.

But wait - the new database engine has it's own implementation of DAO, and an ADO driver, so we can actually open the database directly in VBA.   Not a great solution on it's own, but given that we know you can bind DAO and ADO Recordset objects directly to forms at runtime, we can make a workable solution...

---

Using late-binding forms (ADO) to an Access 2007(.accdb) backend

Unfortunately I couldn't get late binding a form to a DAO recordset from the new database engine to work.  Crash after crash.  
But an ADO connection does work, and reasonably well.

Late binding forms were a feature added back in Access 2000, but they're rarely used.  They work like this;

1.  All forms have the 'Record Source' property set to nothing.  i.e. you're making the forms unbound, for now.
2.  As normal, set up the controls on your form as if they were bound to a local table.
2.  You open the backend database file and set up the recordsets in VBA code.  Usually a global variable is used for the database connection.
3.  In the OnOpen event of each form, you write a VBA function to bind the Recordset property to your global database connection.

Simple!  As usual, I've made this all very simple by providing you with a free sample module that does most of the work... (usage guide follows)

Option Explicit

' Code: ModLateBindingForms
' Desc: Simple helper module for binding forms to ADO recordsets, using a global backend connection
' Code by (c) Wayne Phillips, 26/06/2008
' This code is provided for free from www.everythingaccess.com
' Please leave the copyright notice in place - Thank you.

' REQUIREMENTS: Access 2000 / 2002 / 2003
'               VBA Reference to the Microsoft ActiveX Data Objects Library

Public g_ADOBackEndConn As ADODB.Connection
Public Const g_BackendPath = "C:\Northwind2007.accdb"
Public Const g_BackendPassword = ""

' For Microsoft.ACE.OLEDB.12.0,you need Microsoft Office 12.0 Access Database Engine to be installed.
Private Const g_BackendADOProvider = "Microsoft.ACE.OLEDB.12.0"
Private Const g_BackEndADOConnectStr = "Provider=" & g_BackendADOProvider & ";" & _
                                        "Data Source=" & g_BackendPath & ";" & _
                                        "Jet OLEDB:Database Password=" & g_BackendPassword

Public Sub LateBindFormToBackendRecordset(Frm As Object, strSQL As String, _
                                        Optional LockType As LockTypeEnum = adLockOptimistic, _
                                        Optional CursorType As CursorTypeEnum = adOpenKeyset)

On Error GoTo ErrorHandler

    Dim FormRecordset As ADODB.Recordset

    If EnsureBackendIsConnected() = True Then
    
        Set FormRecordset = New ADODB.Recordset
        With FormRecordset
           Set .ActiveConnection = g_ADOBackEndConn
           .Source = strSQL
           .LockType = LockType
           .CursorType = CursorType
           .Open                        ' If this fails, error handler will kick in
        End With
       
        Set Frm.Recordset = FormRecordset

    Else
   
        MsgBox "LateBindFormToBackendRecordset error: ADO backend connection failed"

    End If

Exit Sub

ErrorHandler:
    Set FormRecordset = Nothing ' not strictly needed
    MsgBox "Error occured in procedure 'LateBindFormToBackendRecordset'" & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Description: " & Err.Description

End Sub

Public Function EnsureBackendIsConnected() As Boolean

On Error GoTo ErrorHandler

    If g_ADOBackEndConn Is Nothing Then
    
        Set g_ADOBackEndConn = New ADODB.Connection
        g_ADOBackEndConn.Open g_BackEndADOConnectStr
    
    End If
    
    EnsureBackendIsConnected = True
    
Exit Function
    
ErrorHandler:
    Set g_ADOBackEndConn = Nothing
    MsgBox "Error occured in procedure 'EnsureBackendIsConnected'" & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Description: " & Err.Description

End Function

Usage

Copy the above code into a new module called 'ModLateBindingForms'.  Change the g_BackendPath and g_BackendPassword constant values as necessary.  Make sure you've got a VBA reference for ADO (Microsoft ActiveX Data Objects Library)

In each forms OnOpen event, write a simple bit of VBA code:

Private Sub Form_Open(Cancel As Integer)
 
    Call LateBindFormToBackendRecordset(Me, "SELECT TOP 5 * FROM Invoices")

End Sub

Note that I've used a simple SQL statement, but just a table or query name will work too.

Don't forget, by default the encryption used in the new Access 2007 is only RC4 with a 40 bit key - you *must* upgrade this to 128-bit key if you want decent security.

References

How to bind Microsoft Access forms to ADO recordsets (Microsoft KB Article)
Changing the encryption type in Access 2007
Access 12 Redistributable Database Engine Download

Wayne Phillips
www.everythingaccess.com