如何使用VB代码插入到Microsoft Access数据库中

Question

我对Microsoft Access非常陌生。我需要写VB代码来做一个按钮事件点击数据库插入。插入应该从按钮所在的表单中获取信息。我知道您可以通过以下方式获取表单信息：

Form![tablename]![columnname]

但是我如何将这些信息插入到不同的表中呢？我在How to insert data into a Microsoft Access Database?上看到了这篇文章，但我想它只适用于.NET，因为我在访问参考页面http://msdn.microsoft.com/en-us/library/office/aa296940(v=office.11).aspx上找不到列出的OleDbConnection对象

Answer 1

你说的是VB，但我想你说的是VBA ( Access使用的VB )。

如果您尝试插入的表与从中发出命令的表在同一个数据库中，则不需要连接。它是内置的。执行查询的命令是CurrentDB.Execute {SQL statement}

所以，你会得到如下所示的东西：

Dim strSQL as String
strSQL = "INSERT INTO TableName( FieldName)
strSQL = strSQL & "SELECT 'MyData'"
CurrentDB.Execute strSQL

Answer 2

私有子btnAdd_Click(ByVal发送者作为System.Object，ByVal e作为System.EventArgs)将btnAdd.Click Dim sql作为字符串处理

    Dim con As New OleDb.OleDbConnection

    Dim cmd As New OleDb.OleDbCommand


    con.ConnectionString = "PROVIDER = Microsoft.Jet.OLEDB.4.0; Data Source = E:\test.mdb"

    con.Open()

    sql = "INSERT INTO student(name, Address)  VALUES('" & Me.txtName.Text & "','" & Me.txtAddress.Text & "')"
    cmd = New OleDb.OleDbCommand(sql, con)
    cmd.ExecuteNonQuery()
    MsgBox("saved")

    txtName.Text = ""
    txtAddress.Text = ""
    con.Close()

Answer 3

Dim con As New OleDb.OleDbConnection

Dim cmd As New OleDb.OleDbCommand


con.ConnectionString = "PROVIDER = Microsoft.Jet.OLEDB.4.0; Data Source = E:\test.mdb"

con.Open()

sql = "INSERT INTO student(name, Address)  VALUES('" & Me.txtName.Text & "','" & Me.txtAddress.Text & "')"
cmd = New OleDb.OleDbCommand(sql, con)
cmd.ExecuteNonQuery()
MsgBox("saved")

txtName.Text = ""
txtAddress.Text = ""
con.Close()