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)

Answer 4

I finally can create a table using your code, but, the problem is, that's just a blank table, I can't manage to add records to it.
here is my code, please take a look, thankyou
This code is to add tables with the name "Bai1" "Bai2" "Bai3" .....

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim newrow As DataRow
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        newrow = ds.Tables("bai").NewRow()
        newrow.Item(1) = "Bai" & (maxrow + 1)
        ds.Tables("bai").Rows.Add(newrow)
        ComboBox1.Items.Add(ds.Tables("bai").Rows(maxrow).Item(1))
        Dim cmd As New OleDb.OleDbCommand("CREATE TABLE [" + ds.Tables("bai").Rows(maxrow).Item(1) + "] ([ID] TEXT,[Tiếng Việt] TEXT, [Tiếng Nhật] TEXT,[Sample] TEXT)", con)
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
        maxrow = maxrow + 1
        da.Update(ds, "bai")
        MsgBox("Đã thêm 1 bài học mới")
    End Sub

and this is the code to add records to the new table ( I use a label stand for table's name)

  Private Sub Buttoncommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Buttoncommit.Click
        Dim newrow As DataRow
        Dim da1 As OleDb.OleDbDataAdapter
        Dim sql1 As String
        Dim selectedtable As String = ComboBox1.SelectedItem
        sql1 = "select*from " & selectedtable
        da1 = New OleDb.OleDbDataAdapter(sql1, con)
        con.Open()
        da1.Fill(ds, selectedtable)
        con.Close()
        newrow = ds.Tables(selectedtable).NewRow()
        newrow.Item(1) = Viet.Text
        newrow.Item(2) = Nhat.Text
        newrow.Item(3) = Sample.Text
        ds.Tables(selectedtable).Rows.Add(newrow)
        Dim cb1 As New OleDb.OleDbCommandBuilder(da1)
        da1.Update(ds, selectedtable)

    End Sub

the error is Syntax error in INSERT INTO statement. (OLEDBexception was unhandled) at line da1.update(ds,selectedtable)

Answer 5

Can you post your code for the INSERT statement?

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 6

actually, i always use oledb.oledbcommandbuilder, I don't know the insert statement ( I'm very new to VB.NET, really sorry)
If you want, i can upload my project

Answer 7

actually, i always use oledb.oledbcommandbuilder, I don't know the insert statement ( I'm very new to VB.NET, really sorry)
If you want, i can upload my project

Same problem here, but my insert works on a table created manually in access.  The exact same insert will not work on a table created programatically, so I think your code is fine.  The key must be to find out what's different about the 2 tables.

Answer 8

You would have to clarify what you mean by "will not work". That includes posting code and the exception that occurs and the line of code that generated the exception.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 9

You would have to clarify what you mean by "will not work". That includes posting code and the exception that occurs and the line of code that generated the exception.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

quanghan, I think I figured out your problem.  Sorry I jumped to conclusions again, and I call myself an experienced programmer.
I had a field name called "Desc", an sql reserved word, which caused the sql syntax error.  So I changed it to "Description".

Try swapping out field names in your text boxes.

Answer 10

Martin,

Can you please tell me how can I add the row and some data to that row?

Do I need any update after adding data?

Thanks for help in advance.

Regards

Ashkan

Answer 11

hi there . the code works fine but i want to know how to declare " field1" column as primary key...