Howto Create a Microsoft Access Database from ASP Code

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"

  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

  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"

    ' 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

    ' 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

      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

    ' 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

  ' 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

• Microsoft ADO 2.1 or higher with ADOX

References

• ADOX API Reference

Download Files

• Source Code for This Article