Microsoft Access 2007 tutorial: inserting data with DAO


Inserting data with DAO

III. Append, Update, Delete Data by SQL Statement with VBA

With DAO, you can use SQL statements to append, update, and delete data. In Microsoft Access you can execute the SQL statements with Execute method of database object.

III.1. Insert Data

To append the data use the syntax below:

DbObject.Execute(Query as String,[Options])

- DbObject: A variable that represents the existing database file that you want to open.

- Execute:  A method of database object in DAO is used to execute the SQL statement.

- Query: It represents the action queries include append, update and delete query. The Query is a requirement argument and has data type as string.

- Options: It is an optional argument and has data type as variant.

For example, insert a new record to the Book table(TblBook) when you click Add command button on the Book form. Look at the figure below:

Microsoft Access 2007 Book Information Form

As you see on the Book form, there are four records in the list. The four records displayed in the list is pulled from the Book table, so it represents the records in the Book table. Therefore, you can see all the data in the Book table in this list.

Now append a new records to the Book table. To do so, type the code below:

 

Option Compare Database

Dim Db As DAO.Database

' Declare a variable as database type of DAO

Private Sub Form_Load()

   Set Db to the current database

   Set Db = CurrentDb

'Display column header when viewing form

   lstViewData.ColumnHeads = True  

'Display four columns when viewing form

   lstViewData.ColumnCount = 4

'Set Table/Query to RowSourceType property of list box

   lstViewData.RowSourceType = "Table/Query

'Populate the data into the list box

   lstViewData.RowSource = "Select * from TblBook"

   ClearData 'Call ClearData sub procedure

   CmdUpdate.Enabled = False

   CmdDelete.Enabled = False

End Sub



Private Sub CmdAdd_Click()
 'Append a new record to TblBook
Db.Execute "INSERT INTO TblBook VALUES('" & txtBookID & "'," _
& "'" & txtPubID & "','" & txtTitle & "','" & txtISBN & "') "
MsgBox "Save Success!"   
lstViewData.Requery 'The list view refresh the new data

End Sub

Or

Private Sub CmdAdd_Click()

Dim sqlText as String

   sqlText = "INSERT INTO TblBook VALUES('" & txtBookID & "'," _
   & "'" & txtPubID & "','" & txtTitle & "','" & txtISBN & "') "
   MsgBox "Save Success!"   
   lstViewData.Requery
   sqlText.Execute


End Sub

Remark: If you want to type the code of INSERT statement above in a single line, you can type the code below:

Private Sub CmdAdd_Click()

   db.Execute "INSERT INTO TblBook VALUES('" & txtBookID & "','" & txtPubID & "','" & txtTitle & "','" & txtISBN & "')"
  MsgBox "Save Success!"
  lstViewData.Requery


End Sub

You see the code of INSERT statement above has two lines because the space for typing the code is not enough for one line. However, you must type it in one line. If so the error will occur.

Note: When working with SQL statements, the values need to be written with signs that are compatible to their data types.

Data Type of Variable Sign
Text or String '" & String Variable & "'
Date/Time #" & Date Variable & "#
Number " & Number Variable & "

If you want to append a new record to the Book table by assigning the values directly, the code can be written as below:


Private Sub CmdAdd_Click()

    Db.Execute "INSERT INTO TblBook VALUES  (' B005 ',' Pub002 ', " _  
   & " ' Ms.Access2 ',' 997088 ') "
    MsgBox "Save Success!"   
   lstViewData.Requery


End Sub

After you fill  the data for each text box and click Add command button, you will see the new record displays in the list view as figure below:

Microsoft Access 2007 Book Information Form




Comments

CAPTCHA image



This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.