locked
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

    Wednesday, April 02, 2008 7:03 AM

Answers

  •  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

    Thursday, April 03, 2008 3:48 AM

All replies

  •  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

    Thursday, April 03, 2008 3:48 AM
  • 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

    Thursday, November 05, 2009 12:50 PM
  • 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)
    Thursday, November 05, 2009 1:31 PM
  • 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)
    Wednesday, January 27, 2010 12:55 PM
  • Can you post your code for the INSERT statement?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, January 27, 2010 1:43 PM
  • 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
    • Proposed as answer by BillPJ7 Thursday, February 04, 2010 5:31 AM
    Wednesday, January 27, 2010 1:50 PM
  • 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.
    Thursday, February 04, 2010 5:44 AM
  • 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)
    • Proposed as answer by BillPJ7 Thursday, February 04, 2010 6:27 PM
    Thursday, February 04, 2010 1:56 PM
  • 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.
    Thursday, February 04, 2010 6:39 PM
  • 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

    Wednesday, January 05, 2011 5:33 PM
  • hi there . the code works fine but i want to know how to declare " field1" column as primary key...

    Tuesday, December 11, 2012 3:53 AM