# VBForums CodeBank > CodeBank - Visual Basic .NET >  VB.NET - Create new MS Access Database using ADOX

## Pirate

VB Code:
Private Sub Command1_Click()
'Shows how to create an Access 2000 database and append tables, fields, indexes using ADOX. Don't forget
'a reference to ADOX (Microsoft ADO Ext. 2.x for DDL and Security)
  Dim ADOXcatalog As New ADOX.Catalog
  Dim ADOXtable As New Table
  Dim ADOXindex As New ADOX.Index
  
   On Error GoTo errhandler
  ADOXcatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "c:\newdata.mdb"
   On Error Resume Next
   ADOXcatalog.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& "c:\newdata.mdb"
  
  'name table, append fields to table
  ADOXtable.Name = "Employees"
  ADOXtable.Columns.Append "LastName", adVarWChar, 40
  ADOXtable.Columns.Append "ID", adInteger
  ADOXtable.Columns.Append "Department", adVarWChar, 20
   'append tables to database
  ADOXcatalog.Tables.Append ADOXtable
    'internal index on two fields
  ADOXindex.Name = "TwoColumnsIndex" 'name of index
  ADOXindex.Columns.Append "LastName"
  ADOXindex.Columns.Append "ID"
   ADOXtable.Indexes.Append ADOXindex
errhandler:
  If Err.Number = -2147217897 Then
    MsgBox "Database already exists"
ElseIf Err.Number <> 0 Then
    MsgBox "Err " & Err.Description & "; operation not complete"
End If
Set ADOXtable = Nothing
Set ADOXindex = Nothing
Set ADOXcatalog = Nothing
End Sub

----------


## cgj

In vb.net i got some problems with these lines,

Dim ADOXtable As New table()  
Type Table not defined

ADOXtable.Name = "Employees"
        ADOXtable.Columns.Append("LastName", adVarWChar, 40)
        ADOXtable.Columns.Append("ID", adInteger)
        ADOXtable.Columns.Append("Department", adVarWChar, 20)

adVarWchar And adInteger  is not declared

Please Help
Thanks

----------


## powdir

adVarWchar And adInteger are 'old' vb6 constants - VB.NEt may use ADOX.DataTypeEnum.adInteger 0r something. Prolly the same deal with your Table type i.e. ADOX.Table.

Sorry dont have VS.NEt here so cant check but hope this is a lead.

Cheers

----------


## Pirate

I thought there is something missing too . I should've mentioned that you need to map these variables to the ADOX.DataTypeEnum . Here is the .NET Code : 


VB Code:
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'Shows how to create an Access 2000 database and append tables, fields, indexes using ADOX. Don't forget
        'a reference to ADOX (Microsoft ADO Ext. 2.x for DDL and Security)
        Dim ADOXcatalog As New ADOX.Catalog
        Dim ADOXtable As New ADOX.Table
        Dim ADOXindex As New ADOX.Index
         On Error GoTo errhandler
        ADOXcatalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "c:\newdata.mdb")
         On Error Resume Next
         ADOXcatalog.ActiveConnection = _
      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
      & "c:\newdata.mdb"
         'name table, append fields to table
        ADOXtable.Name = "Employees"
        ADOXtable.Columns.Append("LastName", ADOX.DataTypeEnum.adVarWChar, 40)
        ADOXtable.Columns.Append("ID", ADOX.DataTypeEnum.adInteger)
        ADOXtable.Columns.Append("Department", ADOX.DataTypeEnum.adVarWChar, 20)
         'append tables to database
        ADOXcatalog.Tables.Append(ADOXtable)
          'internal index on two fields
        'ADOXindex.Name = "TwoColumnsIndex" 'name of index
        'ADOXindex.Columns.Append("LastName")
        'ADOXindex.Columns.Append("ID")
         ADOXtable.Indexes.Append(ADOXindex)
errhandler:
        If Err.Number = -2147217897 Then
            MsgBox("Database already exists")
        ElseIf Err.Number <> 0 Then
            MsgBox("Err " & Err.Description & "; operation not complete")
        End If
        ADOXtable = Nothing
        ADOXindex = Nothing
        ADOXcatalog = Nothing
    End Sub
End Class

----------


## Pirate

Since some guys have faced some problems with finding ADOX Library , I put it here . Just unpack and copy it in your project folder then reference it .

----------


## marvinklein

ok thanks pirate. i didnt even need the file. i just didnt know what it was talking baout when it said to reference the ADOX dll...

so for everybody: go to project file menu, then go to add reference. on the com tab chose microsoft ado ext. ....

thanks pirate for all the help...

----------


## Pirate

Sure no prob. 

This referenced dll exist on both XP , Win200 but I'm not sure about older versions of Windows (ME,Win98) nor WinServer 2003. .So I'd suggest to make a copy in your project folder , just in case you face some problems while deployment .

----------


## Pirate

I'll do some code update since it raises nasty error , although it's still working .

----------


## Pirate

1st update .  :Big Grin:   :Big Grin:  . enjoy . Next update will include creating array of tables and columns and selecting datatype for each column .  

If you have any question , you can pester me . :Big Grin:

----------


## hellven

> 'name table, append fields to table
>         ADOXtable.Name = "Employees"
>         ADOXtable.Columns.Append("LastName", ADOX.DataTypeEnum.adVarWChar, 40)
>         ADOXtable.Columns.Append("ID", ADOX.DataTypeEnum.adInteger)
>         ADOXtable.Columns.Append("Department", ADOX.DataTypeEnum.adVarWChar, 20)


i wanna ask. how to make columns "ID" as a primary key. or "Department" as foreign key..

thank you very much

----------


## rohcky

I have a question about using ADOX.  Is there a way to create the access file as a hidden file?

----------


## Pirate

> I have a question about using ADOX.  Is there a way to create the access file as a hidden file?


 

```
 Call this method after you make sure the db is created .Probably after this line : 
 ADOXtable.Indexes.Append(ADOXindex)

 private void CreateMDBHidden(string MdbFile)
 		{
 			try 
 			{
 		    	if (File.Exists(MdbFile))File.SetAttributes(MdbFile,FileAttributes.Hidden);
 			}
 			catch ( Exception x)
 			{
 				MessageBox.Show(x.Message);
 			}			
 		}
```

 I didn't test the code yet but it looks it works .

----------


## rohcky

That did the trick.  Thanks Pirate.

----------


## sunsunlight

Hello Pirate, I'v followed your code and it does work. But I have another question: how to create multiple tables and establish relationship between them?

----------


## Pirate

You can create an array of ADOX.Table obj that will be tables later . As for creating relations with ADOX lib , this docu will help you : http://msdn.microsoft.com/library/de...ireference.asp

----------


## sunsunlight

Thanks for the information!

Btw, in the previous reply, you mentioned that you would create a table with setting the primary key and datatype. how is that going?

----------


## zen_master

oOO..


another piece of nice info for my future project!  :EEK!:

----------


## jain_mj

What should i do if i want to create a field of BLOB datatype(to store images)?

----------


## dinosaur_uk

How do i add a primary key to the table?

----------


## dinosaur_uk

Found it on MSDN!


VB Code:
Option Explicit
 Private Sub Command1_Click()
'
' This code adds a single-field Primary key
'
Dim Cn As ADODB.Connection, Cat As ADOX.Catalog, objTable As ADOX.Table
   Set Cn = New ADODB.Connection
  Set Cat = New ADOX.Catalog
  Set objTable = New ADOX.Table
   'Open the connection
  Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
   'Open the Catalog
  Set Cat.ActiveConnection = Cn
   'Create the table
  objTable.Name = "Test_Table"
   'Create and Append a new field to the "Test_Table" Columns Collection
  objTable.Columns.Append "PrimaryKey_Field", adInteger
   'Create and Append a new key. Note that we are merely passing
  'the "PimaryKey_Field" column as the source of the primary key. This
  'new Key will be Appended to the Keys Collection of "Test_Table"
  objTable.Keys.Append "PrimaryKey", adKeyPrimary, "PrimaryKey_Field"
   'Append the newly created table to the Tables Collection
  Cat.Tables.Append objTable
 ' clean up objects
  Set objKey = Nothing
  Set objTable = Nothing
  Set Cat = Nothing
  Cn.Close
  Set Cn = Nothing
End Sub

----------


## dani2

Does this work also for DBF fileS?

thank you

----------


## Scottssor

Hey great thread. 

Could someone tell me what would I need to add in order to make the columns I append allow Null values?

----------


## tim8w

Pirate,
I tried this code in VB.NET 2005 and get a "Type is Invalid." exception on the "Cat.Tables.Append objTable" line.

Any ideas?

----------


## JuggaloBrotha

I already know how to make the fields in a new table and setting the primary key already

how do I set the Primary Key field to AutoIncrement Integer? right now it's currently set as AdInteger, but what about the Auto Increment one?

----------


## Meisi

I can create a Database with ADOX Class but i have some problem in disconnecting from created database... these codes can not disconnect the program from the created database:

ADOXtable = Nothing

ADOXindex = Nothing

ADOXcatalog = Nothing

Can anyone help me?

----------


## danasegarane

If you want to create the database with password, Then change the connection string as follows




```
ADOXcatalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "c:\newdata.mdb;Jet OLEDB:Database Password=test")
```

----------


## Tddupre

I get a "Value Does Not Fall Within Expected Range"

Here Is My Code, I prbly messed it up.

vb Code:
Private Sub NewDatabaseToolStripMenuItem_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewDatabaseToolStripMenuItem.Click
          Dim ADOXcatalog As New ADOX.Catalog
         Dim ADOXtable As New ADOX.Table
         Dim ADOXindex As New ADOX.Index
         On Error GoTo errhandler
        ADOXcatalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Documents and Settings\Tdupre8863\Desktop\Accounts.mdb")
        On Error Resume Next
        ADOXcatalog.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Documents and Settings\Tdupre8863\Desktop\Accounts.mdb"
        'name table, append fields to table
         ADOXtable.Name = "Tabel1"
         ADOXtable.Columns.Append("Account", ADOX.DataTypeEnum.adVarWChar, 40)
         ADOXtable.Columns.Append("Username", ADOX.DataTypeEnum.adVarWChar, 20)
         'append tables to database
         ADOXcatalog.Tables.Append(ADOXtable)
         'internal index on two fields
         'ADOXindex.Name = "TwoColumnsIndex" 'name of index
         'ADOXindex.Columns.Append("Account")
         'ADOXindex.Columns.Append("Username")
         ADOXtable.Indexes.Append(ADOXindex)
        MsgBox("Accounts.mdb Created In C:/", MsgBoxStyle.Information, "Success")
 errhandler:
         If Err.Number = -2147217897 Then
             MsgBox("Database already exists", MsgBoxStyle.Exclamation, "No Need")
         ElseIf Err.Number <> 0 Then
             MsgBox("Err " & Err.Description & "; operation not complete")
         End If
         ADOXtable = Nothing
         ADOXindex = Nothing
         ADOXcatalog = Nothing
     End Sub

----------


## Jomz87

Hi anybody?

pls. HELP me on how to create database. 

pls. help me to have a codes of this data:

Entering Name and Address

then the checklist like example, 
Gender:
 O Male
 O Female

Age: ____  

and then SAVE, ADD, EDIT and DELETE. 

this what codes i need. Pls. help guys.

----------


## JuggaloBrotha

> Hi anybody?
> 
> pls. HELP me on how to create database. 
> 
> pls. help me to have a codes of this data:
> 
> Entering Name and Address
> 
> then the checklist like example, 
> ...


The last few years I've found it far easier to simply create an empty database (or one with a few tables), include it in the project, then whenever I need a new db created in the program I simply copy it to the desired location with a meaningful name, then if a few tables need to be created, I just use the CREATE TABLE sql statement.  Completely cuts out the frustrations of using ADOX.

----------

