使用VB.NET在Access中创建表 [英] creating tables in Access using VB.NET

问题描述

我无法从VB.NET创建Access表.

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

推荐答案

如果我将所有ID都取出来(除了ID)，它就会起作用，然后我将它们重新添加到背面，它会停止"Year datetime"， _

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

YEAR是 Access SQL中的保留字.如果我尝试运行以下代码...

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

...我明白了

Syntax error in field definition.

但是，如果我将字段名称括在方括号中...

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

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

...然后我得到

Table created.

这篇关于使用VB.NET在Access中创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！