Howto Create a Microsoft Access Database from ASP Code

Although you should try to make most of your database design decisions at design-time, it can sometimes be really handy to create a database from your ASP code. This article explains how to create a Microsoft Access database using ASP.

To create a database from ASP, you'll need to use ADOX. ADOX (also know by its full name: "Microsoft ADO Extensions for DDL and Security") is an extension to ADO and allows you to create and modify database schemas, as well as apply security to your database objects. It is available since ADO 2.1, so it will work on most modern Windows machines. To make sure you have the latest version of ADO, visit the Microsoft Data Access Technologies site where you can download a recent version of ADO, that includes ADOX.

The Complete Code

You can take a look at the complete code by following this link. It will open in a new browser window, so it's easy to follow along with the explanation.

Creating the Database

Before you start using this code, you have to make sure that the account that IIS runs under (this is usually the IUSR_MachineName account, where MachineName is the name of your computer) has the right permissions to write to the folder where you want to add your database. This means you have to open the Security dialog for the folder, add the IUSR account to the list with accounts and groups that have access to the folder and make sure that you give the account at least Write permissions. Once this has been settled, you are ready to create the database through ASP code.

To successfully create a database, you'll need to perform two steps: first you'll have to create a new and empty database. Once you have the database, you are able to create a new table that defines one or more columns. What's important with these two steps is that you close the connection to the database after you have created the database. If you leave the connection open, you won't be able to add the tables and the columns. This is the reason that this code is split up in two method calls: CreateAccessDatabase creates the database, and then CreateAccessTable opens a connection to this database and adds a table with a few columns. To make things easier, sDatabaseName is defined at the top of the code. This variable holds the name and location of the database you want to create, and is used in both methods. Now let's look at the code and see how you can create the database:

<!--#include virtual="/Includes/adovbs.inc"-->
<%
  Dim sDatabaseName
  sDatabaseName = "C:\Databases\MyNewDatabase.mdb"
The code starts with including the adovbs.inc file. This is a pretty useful file, as it defines all kinds of constants that are used in ADO and ADOX. You'll find the file in the code download for this article, but you can also find it at your local computer, usually located in the folder: C:\Program Files\Common Files\System\ado. To use this file, simply copy it to a folder beneath your site, and add a reference to it using the #include keyword. Next, it's time for the procedure that creates the database:

  Sub CreateAccessDatabase(sDatabaseToCreate)
    Dim catNewDB ' As ADOX.Catalog
    Set catNewDB = Server.CreateObject("ADOX.Catalog")
    catNewDB.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sDatabaseToCreate & _
        ";Jet OLEDB:Engine Type=5;"
      ' Engine Type=5 = Access 2000 Database
      ' Engine Type=4 = Access 97 Database
    Set catNewDB = Nothing
  End Sub
This bit of code does all the hard work in creating the database. It starts by defining a variable for an ADOX.Catalog, your main entry point to all your database maintenance tasks. Next, the variable is set to an instance of an ADOX catalog. The Create method of the Catalog does all the hard work by creating the database. You can see that you'll need to pass a connection string to this method. The connection string looks remarkably similar to ordinary connection strings you may use to connect to a Microsoft Access database from your ASP pages. You'll need to pass sDatabaseToCreate to indicate the name and location of the database and Engine Type to indicate whether you want to create a Microsoft Access 97 or 2000 database. At the end, the Catalog object is destroyed again by setting the variable catNewDB to Nothing.

Now that you have a mechanism to create an empty database, it's time to do something useful with it. After all, what good is a database without any tables? Here's the code for the CreateAccessTable method:

  Sub CreateAccessTable(sDatabaseToCreate)
    Dim catDB ' As ADOX.Catalog
    Set catDB = Server.CreateObject("ADOX.Catalog")  
    ' Open the catalog
    catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & sDatabaseToCreate
    Dim tblNew ' As ADOX.Table
    Set tblNew = Server.CreateObject("ADOX.Table")
    tblNew.Name = "Contacts"
It starts off with creating the same ADOX.Catalog object as you saw in the CreateAccessDatabase method. Then a new ADOX.Table object is created. You should add at least one Column to this new table, although you can of course add as much columns as you require. The Name of the table is set to Contacts in this example.

    ' First Create an Autonumber column, called ID.
    ' This is just for demonstration purposes.
    ' You could have done this below with all the other columns as well
    Dim col ' As ADOX.Column
    Set col = Server.CreateObject("ADOX.Column")
    With col
      .ParentCatalog = catDB
      .Type = adInteger
      .Name = "ID"
      .Properties("Autoincrement") = True
    End With
    tblNew.Columns.Append col
As you can see, a new Column object is created. You'll need to assign it a ParentCatalog, so it knows to which database it belongs. The code sets its type to a number (adInteger) and also add a property called Autoincrement to the Properties collection of this column. This is the same as setting the type of the column to AutoNumber in Access directly, and causes the database to create a new sequential number for each record that gets added to the table that this column belongs to.
At the end, the new column is added to the Columns collection of the new table you created.

    ' Now add the rest of the columns
    With tblNew
      ' Create fields and append them to the
      ' Columns collection of the new Table object.
      With .Columns
        .Append "NumberColumn", adInteger
        .Append "FirstName", adVarWChar
        .Append "LastName", adVarWChar
        .Append "Phone", adVarWChar
        .Append "Notes", adLongVarWChar
      End With
This code adds a couple of columns to the Columns collection of the table. The NumberColumn is set to hold an number, while the Notes column will be of type Memo (adLongVarWChar). All other columns are set to ordinary Text columns.

Now that the columns have been added, you can change some of their properties, like whether they are required (adColFixed) or not (adColNullable). Both these constants are not defined in the adovbs.inc file, so you'll need to declare them in your code. For easier access, you can add them to the adovbs.inc file if you want.

      Dim adColNullable ' Is not defined in adovbs.inc, 
                        '  so you need to define it here. 
                        ' The other option is adColFixed with a value of 1
      adColNullable = 2
      With .Columns("FirstName")
        .Attributes = adColNullable
      End With
    End With
Once all columns are created with the settings you require, and added to the table, it's time to add the table to the database:

    ' Add the new Table to the Tables collection of the database.
	  catDB.Tables.Append tblNew
	  Set col = Nothing
	  Set tblNew = Nothing
	  Set catDB = Nothing
	End Sub
It's good practice to clean up any objects you no longer need, so all objects created in this code are set to Nothing.
All that's left now, is the code that actually calls these two methods. With all the hard work done, this is now really simple:

  ' First call the Create Database method 
  CreateAccessDatabase sDatabaseName
	
  ' Then add a table and columns to this database
  CreateAccessTable sDatabaseName
%>

First a call is made to CreateAccessDatabase to make sure that the database is created. As the name of the database, sDatabaseName is passed. This variable is declared at the top of the page and holds the full file name of your database. Then CreateAccessTable is called to add the table and the columns to the database. Once the page is finished loading, you can see the new database in the folder you specified in all its glory. The database is now ready to be accessed by other ASP code, or from Microsoft Access directly.

Things to Watch Out For

If you try to run this code multiple times in succession, you'll get an error. The database cannot be created if it already exists. For debugging purposes, you could add some code that uses the FileSystemObject to delete the old database before you create a new one.

The current definition for sDatabaseName uses a folder called Databases at your C drive. The code does not create this folder, so you'll need to make sure it exists before you run this page, or you'll need to modify the path so it points to an existing folder.

Prerequisites

References

Download Files


Where to Next?

Wonder where to go next? You can post a comment on this article.

Doc ID 143
Full URL https://imar.spaanjaars.com/143/howto-create-a-microsoft-access-database-from-asp-code
Short cut https://imar.spaanjaars.com/143/
Written by Imar Spaanjaars
Date Posted 09/25/2003 23:48
Date Last Updated 01/16/2005 18:20
Date Last Reviewed 12/08/2006 14:57
Listened to when writing Build A Bridge by Limp Bizkit (Track 7 from the album: Results May Vary)

Comments

Talk Back! Comment on Imar.Spaanjaars.Com

I am interested in what you have to say about this article. Feel free to post any comments, remarks or questions you may have about this article. The Talk Back feature is not meant for technical questions that are not directly related to this article. So, a post like "Hey, can you tell me how I can upload files to a MySQL database in PHP?" is likely to be removed. Also spam and unrealistic job offers will be deleted immediately.

When you post a comment, you have to provide your name and the comment. Your e-mail address is optional and you only need to provide it if you want me to contact you. It will not be displayed along with your comment. I got sick and tired of the comment spam I was receiving, so I have protected this page with a simple calculation exercise. This means that if you want to leave a comment, you'll need to complete the calculation before you hit the Post Comment button.

If you want to object to a comment made by another visitor, be sure to contact me and I'll look into it ASAP. Don't forget to mention the page link, or the Doc ID of the document.

(Plain text only; no HTML or code that looks like HTML or XML. In other words, don't use < and >. Also no links allowed.