How to create an Access database with ASP code

Although we can create a database from the designer, we can also create a database in the ASP's code, and let's take a look at how to create a database in ASP.

To create a database in ASP, we need to use ADOX (Microsoft ADO Extensions for DDL and security), the extension of this ADO can help us to create and modify database structure information, also includes the database object's secure policy. It appears with ADO 2.1, so it can work on most Windows platforms. You can go to MS's official website to get the latest ADO version, of course, including ADOX.

Creating a Database

Before we start coding, determine which account IIS corresponds to Iuser_[machinename] (machinename: Your computer name) has write access to the directory where you want to create the database. You can also open the Properties dialog box for the directory where you want to save the database files, and find the security option to add write access to the above users.

To successfully create a database, we first need to create an empty database object before we can create a new table and define the columns of the table. Here's an important point that means that when we create a table, we must close the data connection after we create the database. Otherwise we will have no way to create a database and define a data column. That's why, I'm going to create two methods next: Createaccessdb (Create a database), CREATEACCESSTB (Create a datasheet), variable dbname to define the name of the database to add, Phypath to define the path that holds the database file. Let's look at the code below:

This code contains a Adovbs.inc file, which is a very useful file that defines all the numeric variables used in ADO and ADOX, which you can find in your code or on your own computer: C:Program FilesCommon Found under Filessystemado. If you need to refer to the middle of your page, you need to copy it to the site's own directory below.

Below is the code to create the database:

The database has been created, and the table is next, otherwise it would be meaningless for us to have a database without a table. Below is the code to create the table:

1 Sub createaccesstb (dbtocreate)

2 Dim catdb ' as ADOX. Catalog

3 Set catdb = Server.CreateObject ("ADOX"). Catalog ")

4 ' Open the Catalog

5 catdb.activeconnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

6 "Data source=" & Server.mapath (Dbtocreate)

7 Dim tblnew ' as ADOX. Table

8 Set tblnew = Server.CreateObject ("ADOX"). Table ")

9 Tblnew.name = Tbname

Ten ' a Autonumber column, called ID.

One ' This is just for demonstration purposes.

' You could have do this below and all columns as

Dim Col ' as ADOX. Column

Set col = Server.CreateObject ("ADOX. Column ")

With Col

ParentCatalog = CatDB

17. Type = Adinteger

18. Name = "ID"

19. Properties ("autoincrement") = True

With

' 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

26. Append "Numbercolumn", adinteger

27. Append "FirstName", adVarWChar

28. Append "LastName", adVarWChar

29. Append "Phone", adVarWChar

30. Append "Notes", adLongVarWChar

With

32

The Dim adcolnullable ' isn't defined in Adovbs.inc,

Need to define it here.

The option is adcolfixed with a value of 1

Adcolnullable = 2

Panax Notoginseng with. Columns ("FirstName")

38. Attributes = adcolnullable

With

With

The "Add" the new Table to the Tables collection of the database.

CatDB.Tables.Append tblnew

Set col = Nothing

Set tblnew = Nothing

Set CatDB = Nothing

The End Sub

Then, you can call it where you want:

1 ' The Create Database method

2 Createaccessdb dbname

3

4 ' Then add a table and columns to this database

5 Createaccesstb dbname