Adding Records to a Table

VBA can also append (add) records to any table that already exists in the database without deleting or changing any records that might already be in the table. If the records to be appended to the table already exist in some other table, you can use a simple append query (in Access) to generate the appropriate SQL statement.

For example, Figure 7-8 shows an append query that selects several fields and records from two related tables in a database. The name of the destination table, PaidOrderSummary, is visible in the query's Destination Table property. You specify the destination table's name after choosing Append Query from the Query Type group on the (Query Tools) Design tab. When you view the SQL statement for the query, you also see the destination table's name there, as shown here:

INSERT INTO PaidOrderSummary ( OrderlD, [Order Date], ProductID, Qty, [Unit Price] ) SELECT Orders.OrderID, Orders.[Order Date], [Order Details].ProductID, [Order Details].Qty, [Order Details].[Unit Price] FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID WHERE (((Orders.Paid)=True));

Because an append query is an action query, you can execute it by using DoCmd.RunSQL just as you can execute other action queries shown in this chapter. You can add the various portions of the lengthy SQL statement to a variable and then execute the statement in the variable:

'Declare a string variable named mySQL. Dim mySQL As String

'Put a lengthy SQL statement into mySQL (in chunks). mySQL = "INSERT INTO PaidOrderSummary"

mySQL = mySQL & " (OrderlD, [Order Date], ProductID, Qty, [Unit Price] ) "

mySQL = mySQL & " SELECT Orders.OrderlD, Orders.[Order Date], "

mySQL = mySQL & " [Order Details].ProductID, [Order Details].Qty,"

mySQL = mySQL & " [Order Details].[Unit Price]"

mySQL = mySQL & " FROM Orders INNER JOIN [Order Details]"

mySQL = mySQL & " ON Orders.OrderID = [Order Details].OrderID"

mySQL = mySQL & " WHERE (((Orders.Paid)=True))"

'Turn off warnings and append the records as specified in the SQL. DoCmd.SetWarnings False DoCmd.RunSQL mySQL DoCmd.SetWarnings True

Figure 7-8:

A sample append query in Access Query Design.

Figure 7-8:

A sample append query in Access Query Design.

Was this article helpful?

0 0

Post a comment