how to create a ms access db and table using adodb in vb.net

Question

 

Hi All,

 

I have to create  ms access database and a table in it using adodb in vb.net by using some SQL queries. i am new to vb.net. please help me to create a database and table in vb.net

 

Regards

phone

Answer 1

phone wrote:

I have to create  ms access database and a table in it using adodb in vb.net by using some SQL queries. i am new to vb.net. please help me to create a database and table in vb.net

 

Hi phone,

 

Please check this document:

HOW TO: Create a Microsoft Access Database Using ADOX and Visual Basic .NET

http://www.codeguru.com/vb/gen/vb_database/microsoftaccess/article.php/c5149/

 

You can create Access databases by using the Microsoft Jet OLE DB Provider and Microsoft ADO Ext. 2.x for DDL and Security (ADOX) with the COM Interop layer.

 

Firstly Add Reference COM component Microsoft ADO Ext. 2.7 for DDL and Security to your project.

Code Snippet

Imports ADOX

Imports System.Data.OleDb

 

Public Class Form1

 

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim databaseName As String = "C:\AccessDB.mdb"

        Dim tableName As String = "MyTable"

 

        ' Part 1: Create Access Database file using ADOX

        Dim cat As ADOX.Catalog = New ADOX.Catalog()

        cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & databaseName & ";Jet OLEDB:Engine Type=5")

        MessageBox.Show("Database Created Successfully")

        cat = Nothing

 

        ' Part 2: Create one Table using OLEDB Provider 

        Dim con As New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & databaseName)

        con.Open()

        'Get database schema

        Dim dbSchema As DataTable = con.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, tableName, "TABLE"})

      con.Close()

 

        ' If the table exists, the count = 1

        If dbSchema.Rows.Count > 0 Then

            ' do whatever you want to do if the table exists

        Else

            'do whatever you want to do if the table does not exist

            ' e.g. create a table

            Dim cmd As New OleDb.OleDbCommand("CREATE TABLE [" + tableName + "] ([Field1] TEXT(10), [Field2] TEXT(10))", con)

            con.Open()

            cmd.ExecuteNonQuery()

            MessageBox.Show("Table Created Successfully")

            con.Close()

        End If

    End Sub

 

End Class

 

References:

1. Create Access database table in VB.NET using OleDb data Provider.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2822564&SiteID=1

 

2. Using ADODB Services in VB.NET

http://www.vbdotnetheaven.com/UploadFile/ptailor/ADODBServices04082005081324AM/ADODBServices.aspx

This article walks you through the usage of ADODB services in .NET application using VB.NET language. The example details the data access using ADODB, fetching recordset, filling ADO.NET dataset from the recordset and binding the same to datagrid for user display.

 

3. Data Access to Microsoft Access database using the OleDb data Provider.

http://www.startvbdotnet.com/ado/msaccess.aspx

 

 

Regards,

Martin

Answer 2

Hi,
   I have to create a database and based upon some file selection I have to create tables.
   When I am selection from a list box control and executing the code, it is working fine. but when I am using the code to create for more than 1 tables , it is giving error. Any help on this.

 

Regards

Manoj

Answer 3

You would need to post your code and identify the error and where it occurs.

Also, after creating your database with ADOX you can use SQL DDL to create tables.

Fundamental Microsoft Jet SQL for Access 2000

---

Paul ~~~~ Microsoft MVP (Visual Basic)