Microsoft Access 2010 tutorial:Append data with DAO


Append data with DAO

III. Append data

In Microsoft Access, you can execute the SQL statements with Execute method of database object of DAO. To append data to a table, follow 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, append a new record to the TblBook when you click Add command button on the frmBook. Look at the figure below:

Access Book form

As you see on the frmBook, there are seven records in the list. The seven records displayed in the list are pulled from the TblBook, so they represent the records in the TblBook. Therefore, you can see all the data in the TblBook in this list.

Now, you append a new record into the TblBook. 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 as 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 'List box refreshes 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 the 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 TblBook 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 a new record displaying in the list box as figure below:

Access Book form append a new record 

 


Comments




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.