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 read existing comments below or you can post a comment yourself on this article .


Consider making a donation
Please consider making a donation using PayPal. Your donation helps me to pay the bills so I can keep running Imar.Spaanjaars.Com, providing fresh content as often as possible.



Feedback by Other Visitors of Imar.Spaanjaars.Com

On Monday, January 10, 2005 5:54:19 PM fang said:
thank you for your advice but i have problem about how to compact .mdb file in code of vb6 can it do by code? thank you
On Monday, June 13, 2005 5:51:40 PM Craig said:
Is there anyway to apply users to that database? With certain priviledges?
On Monday, June 13, 2005 5:58:52 PM Imar Spaanjaars said:
Yes, you can. Check out the following link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscgroupsusersappendchangepasswordmethodsexamplex.asp which is a part of the ADOX code examples: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscadoxcodeexamplesvb.asp?frame=true

Cheers,

Imar
On Friday, December 02, 2005 1:20:45 AM ern said:
How do you Close the file without leaving the function? I currently have a loop where I need to close the file and then reopen it without leaving the loop.  I can see from the explorer window that the file NewMDB.mdb remains open because I see the NewMDB.ldb file.......How can you close the file right after creating it?  I am currently getting sharing violations.....when I try to reopen it in the loop

That is what I need!!!
On Friday, December 02, 2005 9:47:25 PM Imar Spaanjaars said:
Would  catDB.ActiveConnection.Close() do the trick??

Imar
On Saturday, December 03, 2005 12:19:49 AM ern said:
I tried catDB.AvtiveConnection.Close() but the file was still open....Keep in mind that I am using Visual Basic .Net......Using the above command does close the connection but the file *.ldb still exists.....and still results in sharing violation.  

Here is a bit of code...

Dim catDB as New ADOX.Catalog

dBPath = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=" & "C:\example.MDB"

cDB.Create(dBPath)

catDB = nothing   ' also tried    catDB.ActiveConnection.Close()


Either why does not seem to delete the example.ldb file. ARGGGGG!!!!

It is driving me nuts!!!
On Saturday, December 03, 2005 3:38:00 PM Ern said:
Well....I found a bug in my program...Turns out I was missing up an index...For some reason that caused the close feature to not properly close the file....DUH!!! see how much trouple one line of code causes!!! wasted 4 days before I figured it out....ARGGGGGGGGG!!!!
On Monday, January 09, 2006 8:45:47 PM Raghavendra.J said:
Hey Imar,

I tried your solutions
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

...snip.... (Rest of the post removed by Imar)
On Monday, January 09, 2006 9:36:37 PM Imar Spaanjaars said:
Hi Raghavendra,

I removed the rest of your post as it wasn't on-topic for this article. Search my site for 0x80004005 and you'll end up in this FAQ:

http://Imar.Spaanjaars.Com/QuickDocId.aspx?QUICKDOC=263

Could you please use a correct e-mail address when you sent me messages? I just sent you a reply to your personal message only to find out it was bounced by the Gmail web server. Grrrr...

The FAQ I pointed you to will hopefully answer your questions.

Imar
On Friday, January 13, 2006 7:27:38 AM Mohamed said:
Hai,
            i need to change allow zero length property of msaccess database  which is protected by a password.using adodb the password protected database properties should be change. please help me to modify the allow zero length property of password protected database through any method of VB coding..........
Mohamed
On Friday, January 13, 2006 7:31:51 PM Imar Spaanjaars said:
Hi Mohamed,

You can embed the password in the connection string to the database. See http://www.connectionstrings.com for more details.

After that, there shouldn't be a difference....

Imar
On Wednesday, May 03, 2006 9:10:40 AM Jeroen said:
Thank you for the walkthrough :) Saved me some time figuring it out myself. Now write my class.... I need it... or doesnt it work that way ;)
On Wednesday, May 03, 2006 6:23:50 PM Imar Spaanjaars said:
Write your class???

Imar
On Saturday, August 19, 2006 6:27:28 PM David said:
hi I need some help, believe I am quite desperate, I am trying to connect to a sql 2005 express with asp 3.0, Ive tried like 100 different connection strings and got nowhere, if anyone can help me i would be most gratefull. Thank you
On Saturday, August 19, 2006 10:40:27 PM Imar Spaanjaars said:
Hi David,

How is this related to the original topic - creating an Access database through ASP code??

Imar
On Tuesday, February 20, 2007 6:23:03 PM Antony said:
I was wandering if it was possible to only add new table to an existing database?

Is it an obligation to create a new db??

Thanks for helping.
On Tuesday, February 20, 2007 9:41:32 PM Imar Spaanjaars said:
Hi Antony,

Absolutely not. As you can see, the code is split in two parts: one that creates the database and the other that creates a new table in the new (but at that point already existing) database.

So, skip the first part and pass the connection string to an existing database and you're good to go.

Cheers,

Imar
On Tuesday, June 03, 2008 7:56:20 PM Pete said:
Great piece of code .. having some problem with autoincrement for the ID but other then that works .. Thx !
On Tuesday, October 07, 2008 12:42:01 PM abhimanyu said:
you are sent Graphical version of SQL project.you are responce in a short time.this project in visual basic 6.0.
On Tuesday, October 07, 2008 6:25:37 PM Imar Spaanjaars said:
Hi abhimanyu,

Error parsing query on line 1, character 9. Cannot understand "sent Graphical version of SQL project"

In other words: I have no idea what you are talking about.

Imar
On Wednesday, May 13, 2009 3:45:07 PM Srinivas Mantha said:
Execellent article
On Monday, October 12, 2009 8:31:26 AM Dileep said:
This article is a good one. If you provide password security it will be better.
On Monday, September 20, 2010 8:16:41 AM Yukari said:
i confused...
On Monday, September 20, 2010 5:09:03 PM Imar Spaanjaars said:
Hi Yukari,

Me too.... ;-)

Imar
On Tuesday, July 12, 2011 5:38:01 AM anish said:
i want create a database for the fields-company name,adress,phno,fax,descriptins....pls help
On Tuesday, July 12, 2011 5:40:03 AM Imar Spaanjaars said:
And? Isn't this exactly what this article is about? (Provided you want to do it from code in an ancient technology?)

Imar
On Tuesday, November 22, 2011 5:29:50 AM ronen said:
first i want to thank you for this article.
is there a way to make the database name dynamically?
lets say i have a form on 1.asp with a textbox name.
and i want the user that submit the form to go to 2.asp page and there i want that the database name to have the valu that was enter at 1.asp textbox.
i hop you can help my
good day
ronen
On Tuesday, November 22, 2011 1:56:20 PM Imar Spaanjaars said:
Did you try it out? That's exactly what the sDatabaseToCreate parameter does....

Cheers,

Imar
On Wednesday, February 01, 2012 1:51:14 PM abdiaziz said:
please i am thanking to you and i am requesting you to help learning of access programming! thankkkkkkkkkkkkkkks
On Wednesday, February 01, 2012 1:52:10 PM Imar Spaanjaars said:
Heuh?

Imar
On Friday, March 08, 2013 2:04:59 PM Andrew said:
Great article. I went from nowhere to almost a full implementation quickly, so thanks.

However I have one (and only one) problem with the code: I get the error message:

"ADODB.Properties error '800a0cc1' Item cannot be found in the collection corresponding to the requested name or ordinal."

The line causing it is
.Properties("Autoincrement") = True

An internet search shows I'm not the only one with problems with the autoincrement line and the same error is apparent with others. But solutions are so far not obvious.

Any help *really* appreciated. My guess is that you are indeed too busy - as per you "contact me" -  in which case thanks for a great article. If I solve this I'll post back here.
On Friday, March 08, 2013 2:12:45 PM Imar Spaanjaars said:
Hi Andrew,

How are you executing this code and against what version of Access? The Autoincrement property has always worked for me, so I don't know what to recommend.

Cheers,

Imar
On Friday, March 08, 2013 2:20:52 PM Andrew said:
Wow that was quick! :)

I'm running against Access 2007 on a Win7 64 bit machine.

Not sure I understand the fist part of your question; I'm (still!) running with ASP classic and have copied your code line by line. Commenting out the autoincremenet line means all the columns apart from the ID are created.

Does that get us any closer?

On Friday, March 08, 2013 2:25:01 PM Andrew said:
I guess I could use pure SQL statements instead e.g.

CREATE TABLE tablename (id AUTOINCREMENT)

I'm not sure of the + / - of this; I'm familiar with a little of ADO but  not at all with ADOX.
On Friday, March 08, 2013 2:26:55 PM Imar Spaanjaars said:
Hi Andrew,

Yeah, classic ASP answers the first part of my question. You can run this code (with some modifications) from a variety of environments (VBA, VB Script, asp) so I was just checking.

Have you tried running the IIS Application Pool in 32 bit? I know there are some issues with the 64 bits driver. Did you install that driver specifically to make this code work?

Imar
On Friday, March 08, 2013 2:51:44 PM Imar Spaanjaars said:
If pure SQL statements work for you, then you can indeed use that. There should be no difference in the final database / table.

Imar
On Friday, March 08, 2013 3:11:17 PM Andrew said:
Hmmm digging into the nuts and bolts of IIS exposes me as an amateur! O well I think we're after enable32BitAppOnWin64 and that was set to True, which I think is what we want.

I think I'm going to abandon the primary key ID altogether and hope the sky doesn't fall in on me. Thnaks very much for your help.

Andrew
On Friday, March 08, 2013 4:12:52 PM Imar Spaanjaars said:
Hi Andrew,

Yes, that was indeed the setting I was referring to. Sorry for not making that very clear.

I would not "abandon" the primary key altogether, but find another way to set it such as using a SQL statement.

Cheers,

Imar
On Sunday, May 26, 2013 10:17:11 PM peter striegel said:
Just great , well done  !!

peter

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 QuickDocId of the document.

For more information about the Talk Back feature, check out this news item.