how to use VB code to insert into an Microsoft Access database

Question

I am very new to Microsoft Access. I need to write VB code to do a database insert on a button event click. The insert should take info from the form that the button is on. I know that you can get form info with this:

Form![tablename]![columnname]

but how would I take that info to insert into a different table? I saw this post How to insert data into a Microsoft Access Database? but I think its only with .NET because I can't find the OleDbConnection object listed on the Access reference page http://msdn.microsoft.com/en-us/library/office/aa296940(v=office.11).aspx

Tom Collins · Accepted Answer · 2013-06-11 02:52:09Z

5

You say VB, but I think you're talking about VBA (the VB that Access uses).

If the table you're trying to insert into is in the same database as the one you're giving the command from, then you don't need a connection. It's built in. The command to execute a query is CurrentDB.Execute {SQL statement}

So, you'd have something that looks like this:

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

answered Jun 11, 2013 at 2:52

Tom Collins

4,0592 gold badges22 silver badges36 bronze badges

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

4 Comments

user1015214 Over a year ago

Great! I never knew of this method. But two questions: a) why did you append a select to the end of the insert query and b)I see online that the CurrentDB method is part of the Application object. Why then do you not need to do Application.CurrentDB.Execute strSQL?

Tom Collins Over a year ago

It's the way Access wrote it. It works, but the correct syntax is w3schools.com/sql/sql_insert.asp. Application is assumed, at least in this case.

user1015214 Over a year ago

What do you mean 'its the way access wrote it'? What does the SELECT select? You're doing an insert!

Tom Collins Over a year ago

If you use Access to write your query, it uses the SELECT statement to choose what to insert. In this case it's a constant value, but it could also be fields from another table.

sabin · Accepted Answer · 2014-01-02 06:39:31Z

4

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click Dim sql As String

    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()

answered Jan 2, 2014 at 6:39

sabin

411 bronze badge

1 Comment

Michał Leon Over a year ago

This is the proper answer, as it uses VB, not VBA

Benjamin W. · Accepted Answer · 2016-02-21 07:35:28Z

0

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()

edited Feb 21, 2016 at 7:35

Benjamin W.

54k19 gold badges135 silver badges136 bronze badges

answered Feb 21, 2016 at 7:14

desmond

1

1 Comment

Benjamin W. Over a year ago

Maybe add a short explanation as to how and why this works?

Collectives™ on Stack Overflow

how to use VB code to insert into an Microsoft Access database

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related