creating tables in Access using VB.NET

Question

I'm having trouble creating Access tables from VB.NET.

This is the code I have come up with but I keep getting errors:

   Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try
        'connection string
        Dim dbpath As String = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().CodeBase)
        dbpath = New Uri(dbpath).LocalPath
        Dim my_connection As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\GhostDrive\Desktop\database.mdb"
        Dim userTables As DataTable = Nothing
        Dim connection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection()
        Dim source As String

        'query string
        Dim my_query As String = "CREATE TABLE " & TextBox2.Text & " ( " & _
            "ID Counter, " & _
            "Year datetime," & _
            "Title varchar(40)," & _
            "image1 Long," & _
            "Image2 Long," & _
            "Image3 Long," & _
            "Image4 Long," & _
            "Serial varchar(20)," & _
            "Purchaseprice Currency," & _
            "Evalprice Currency, " & _
            "Datepurchase DateTime, " & _
            "Dateeval DateTime, " & _
            "Sign varchar(40), " & _
            "Grading varchar(20)," & _
            "Eval YesNo, " & _
            "Star YesNo, " & _
            "Folder YesNo, " & _
            "Forsale YesNo, " & _
            "Error YesNo, " & _
            "Barcode(varchar(20)," & _
            "Comm YesNo )"

        'create a connection
        Dim my_dbConnection As New OleDbConnection(my_connection)

        'create a command
        Dim my_Command As New OleDbCommand(my_query, my_dbConnection)

        'connection open
        my_dbConnection.Open()

        'command execute
        my_Command.ExecuteNonQuery()

        'close connection
        my_dbConnection.Close()
        ListBox1.Items.Clear()
        source = TextBox1.Text
        connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + source
        Dim restrictions() As String = New String(3) {}
        restrictions(3) = "Table"
        connection.Open()
        ' Get list of user tables
        userTables = connection.GetSchema("Tables", restrictions)
        connection.Close()
        ' Add list of table names to listBox
        Dim i As Integer
        For i = 0 To userTables.Rows.Count - 1 Step i + 1
            ListBox1.Items.Add(userTables.Rows(i)(2).ToString())
        Next

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try


End Sub

sorry that that was a typo, the code runs and then stops at timedate — user3408291
– user3408291, Commented Sep 13, 2014 at 10:56
You're missing a comma after "Title varchar(40)" & Change to "Title varchar(40)," & and it will work. — jpw
– jpw, Commented Sep 13, 2014 at 10:57
if i take all them out except ID it works and then i add them on by on back it stops "Year datetime," & _ — user3408291
– user3408291, Commented Sep 13, 2014 at 11:02
Well, I tried it in Access, and the query worked and created the table, so your problem might be in another part of the code (which you didn't include). — jpw
– jpw, Commented Sep 13, 2014 at 11:02

Gord Thompson · Accepted Answer · 2014-09-13 13:51:35Z

8

if i take all them out except ID it works and then i add them on by on back it stops "Year datetime," & _

YEAR is a reserved word in Access SQL. If I try to run the following code ...

Dim connectionString As String = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Users\Public\mdbTest.mdb;"
Using con As New OleDbConnection(connectionString)
    con.Open()
    Using cmd As New OleDbCommand()
        cmd.Connection = con
        cmd.CommandText = "CREATE TABLE zzzTest (ID COUNTER, Year INTEGER)"
        Try
            cmd.ExecuteNonQuery()
            Console.WriteLine("Table created.")
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Using
    con.Close()
End Using

... I get

Syntax error in field definition.

However, if I enclose the field name in square brackets ...

        cmd.CommandText = "CREATE TABLE zzzTest (ID COUNTER, [Year] INTEGER)"

... then I get

Table created.

answered Sep 13, 2014 at 13:51

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HansUp Over a year ago

I would be leery of Error and Eval as field names, too. Reference: Allen Browne's Problem names and reserved words in Access

Collectives™ on Stack Overflow

creating tables in Access using VB.NET

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related