Creating a Microsoft Access Provider for IIS Database Manager

Following up on my last blog post about the API set for the IIS Database Manager, I have something of a secret to let you in on - you can use the code samples in several of those API documents to create a fully functional provider for Microsoft Access databases. I would never use an Access database in a production environment, but having an Access provider has had some great benefits for me from a test perspective. I often use Access databases for test projects, and using the IIS Database Manager to manage the Access databases on my test systems means that I don't need to install Microsoft Access on any of my test servers.

That being said, as I was writing the API documentation I needed to create something of value to test my code samples. Since the Database Manager feature team was already creating database providers for SQL Server and MySQL, it seemed to me like Microsoft Access was the only other readily-accessible database that I could use for my samples. I mentioned in my last post that Saad Ladki was the Program Manager for Database Manager; Saad had started work on an Access provider at one point, but he abandoned the provider as his schedule grew tighter, so I took over that project so I could use it for the API samples.

With that in mind, here's what you need to do to create a Microsoft Access provider for the IIS Database Manager:

• Create a new class project named "AccessDatabase" in Visual Studio

• Add project references for:

  • System.Configuration
  • Microsoft.Web.Management.DatabaseManager
    Note: This may require adding a reference path for your project, such as "C:\Windows\assembly\GAC_MSIL\Microsoft.Web.Management.DatabaseManager\1.0.1.0__31bf3856ad364e35", where "C:" is your operating system drive.

• Open the class in the editor, remove the existing code, and insert the following empty class:

   using System;
  using System.Collections;
  using System.Collections.Generic;
  using System.Configuration.Provider;
  using System.Data;
  using System.Data.Common;
  using System.Diagnostics;
  using System.IO;
  using System.Linq;
  using System.Text;
  using System.Reflection;
  using System.Data.OleDb;
  using Microsoft.Web.Management.DatabaseManager;
  
  namespace AccessDatabase
  {
      public class AccessProvider :
          DatabaseProvider,
          IDbTableManager,
          IDbTableDataManager,
          IDbViewManager,
          IDbBackupManager,
          IDbRestoreManager
      {
  
      }
  }
  

• Copy the code samples from the following MSDN topics into the class; note that some of the utility methods in the code samples will be duplicated so you'll have to remove the duplicate methods:

  • DatabaseProvider
    • ConnectionStringArguments Property
    • CalculateConnectionString Method
    • ExecuteQuery Method
    • TestConnection Method
    • GetDatabaseHostName Method
    • GetServerTypes Method
    • GetDatabaseInfo Method
    • VerifyDependencies Method
  • IDbTableManager
    • CreateTable Method
    • DropTable Method
    • EditTable Method
    • GetPrimaryKey Method
    • GetTableInfo Method
    • GetTables Method
  • IDbTableDataManager
    • DeleteRow Method
    • EditRow Method
    • GetData Method
    • InsertRow Method
  • IDbViewManager
    • CreateView Method
    • DropView Method
    • EditView Method
    • GetViews Method
  • IDbBackupManager
    • CreateBackup Method
  • IDbRestoreManager
    • RestoreBackup Method

• Save, compile, sign, and GAC the provider. For more information about using the Gacutil.exe tool, see the following topic on Microsoft the MSDN Web site:

  Global Assembly Cache Tool (Gacutil.exe)

• Follow the instructions in the Microsoft.Web.Management.DatabaseManager Namespace topic to add the following entry to your administration.config file, which will register the provider for IIS Manager:

   <provider name="Access Provider"
     providerName="System.Data.OleDb"
     type="AccessDatabase.AccessProvider,AccessDatabase,Version=1.0.0.0,Culture=neutral,PublicKeyToken=426f62526f636b73" >
     <databaseBackup
        backupEnabled="true"
        restoreEnabled="true"
        backupPath="C:\backups" />
  </provider>
  

  Note: You will need to update the PublicKeyToken with the public key token from your assembly.

• Add an OLEDB connection string for your Access database; the following web.config sample shows what that might look like:

   <configuration>
    <connectionStrings>
      <add name="Northwind"
        providerName="System.Data.OleDb"
        connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\inetpub\wwwroot\App_Data\Northwind.mdb;" />
    </connectionStrings>
  </configuration>
  

  Note: Since the code samples were all written using OLEDB, not ODBC, you will need to make sure that you use an OLEDB connection string like my example.

• Open the IIS Manager and open the Database Manager for the site where you added the connection string. You should now be able to manage the tables and views for your Access database, as well as backing up and restoring your database.

Additional Notes

Creating Database Backups

Even though I wrote my provider to implement backing up and restoring databases, this functionality is purely optional - you could easily remove the IDbBackupManager and IDbRestoreManager implementations from the class and remove the <databaseBackup> element from administration.config.

OLEDB Errors When Saving JOIN/ORDER BY Views (Queries)

Unfortunately, there is one unexpected OLEDB problem with Microsoft Access databases that I ran into that I could not work around. When you attempt to save a view (query) that contains both JOIN statements and ORDER BY statements, you will receive an error that states "Only simple SELECT queries are allowed in VIEWS."

As long as the SQL code is syntactically correct you can execute the query, but you cannot save it. If a query that contains both JOIN statements and ORDER BY statements has already been created in Microsoft Access, you can open it and make changes, but you will not be able to save those changes back to the database.

For example, the following SQL code will cause the error to occur:

 SELECT Categories.CategoryName, Products.ProductName
FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
ORDER BY Categories.CategoryName, Products.ProductName;

While the following code will not cause the error:

 SELECT Categories.CategoryName, Products.ProductName
FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;

Even though the SQL code is syntactically correct and will execute without any problems, the error occurs at the OLEDB layer when the provider executes the CREATE VIEW statement to save the view to the database. This appears to be an unfortunate and irresolvable OLEDB limitation when you are using an Access database. When you are faced with such a situation, you will need to open the database in Microsoft Access to save the query.