Access Data Projects

Access Data Projects (ADP) are a new incarnation of Access databases that have been designed to work with SQL Server or MSDE databases, a cut-down version of SQL Server. Because they are not the most popular choice for Access users, even after being out for three years , I have restricted the material in this book to a summary of views expressed in Access help, the MSDN Web site, and newsgroups on ADP protection and security, as follows :

• Use the startup options on the Tools menu to protect your database, just as you would an Access MDB database. The instructions are also available in VBA code if you search for "startup options" in the VBE.

• You can turn off the AllowByPass option by using VBA code, as follows:

   CurrentProject.Properties.Add "AllowBypassKey", False 

• Compile the Access Data Project into ADE format just as if you were creating an MDE-format database from an MDB database. This action will provide gold standard security for your forms, reports , and modules.

• Establish security within SQL Server to protect database diagrams, tables, views, and stored procedures.

Of course, as for its older brother the MDB format, you'll need to ponder some protection issues for Access Data Projects, such as:

• You cannot protect the startup options by using workgroup security. The lack of workgroup security makes the ADP startup options more vulnerable than the MDB startup options because programmers can change the options by using VBA code.

• No workgroup security is built into ADP, so you will have no way of protecting different parts of your application, as you would in an MDB database.

• Do not assume because your data is in SQL Server format that it is safe, because it isn't. You will need to invest resources in establishing and maintaining the SQL Server security.

• A number of Internet group postings indicated that ADP projects suffer from vulnerabilities from importing from another ADP project.

My recommendations for ADPs are that you

• Use SQL Server security for the data.

• Compile the ADP database into ADE format to protect the forms, reports, and code.

• Set up the operating system-protected folders by using the concepts that I outline in Chapter 12.

Third-Party Access Data Project Protection Solution

Though I haven't made a point of discussing third-party solutions to security issues in the book, a prominent Access development company called Database Creations sells a tool for managing security at the user level in an ADP. You will find a link to this company's Web site in the "Further Reading" section of this chapter.