使用 VB.NET 在 Access 中创建表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25822458/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
creating tables in Access using VB.NET
提问by user3408291
I'm having trouble creating Access tables from VB.NET.
我在从 VB.NET 创建 Access 表时遇到问题。
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
回答by Gord Thompson
if i take all them out except ID it works and then i add them on by on back it stops "Year datetime," & _
如果我把它们都拿出来，除了 ID 它可以工作，然后我在后面添加它们，它会停止“Year datetime”，& _
YEARis a reserved wordin Access SQL. If I try to run the following code ...
YEAR是Access SQL 中的保留字。如果我尝试运行以下代码...
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.
