VB.NET - Create new MS Access Database using ADOX

[Pirate]

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 :

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 . . 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 .

[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!

[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!

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.

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,
Gender:
O Male
O Female

Age: ____

and then SAVE, ADD, EDIT and DELETE.

this what codes i need. Pls. help guys.

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.