Creating new tables from existing tables

The easiest way to use VBA to create a new table from an existing table is to first design a Make-Table query in Access. (In Query Design, choose QueryO Make-Table Query from the Access menu bar, and then specify the name of the table to create. Refer to Figure 7-5.) Figure 7-7 shows an example of a Make-Table query that selects fields from a couple of related tables, where the Paid fields contain False. Although not readily apparent just by looking at the query, when run, this query actually creates a new table named UnpaidOrdersSummaryTable.

Figure 7-7:

Sample Make-Table query.

Figure 7-7:

Sample Make-Table query.

Viewing the SQL statement for the Make-Table query shown in Figure 7-7 reveals the following SQL statement:

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

Even in the SQL statement, the only indication that this is a Make-Table query are the words INTO UnpaidOrdersSummaryTable, which tell the query to store a copy of the records the query produces into a table named

UnpaidOrdersSummaryTable.

JflNG/

When a Make-Table query executes, it will first check whether the destination table (UnpaidOrdersSummaryTable, in this example) exists. If that table does exist, the table will be deleted before the new table is created. If you want to add new records to an existing table, use an Append query rather than a Make-Table query.

Of course, the Make-Table query shown here is just an example. The technique for converting the Make-Table query to code would be the same for any query. It's simply a matter of copying the SQL statement to the Code window and tweaking the statement so it works in VBA. The following code shows how the Make-Table query shown in Figure 7-7 looks after being properly formatted to work in a VBA procedure:

'Declare a variable to store SQL statement. Dim mySQL As String

'Build mySQL string from query's SQL statement.

mySQL = "SQLECT Orders.OrderID, Orders.[Order Date], [Order Details].ProductID, mySQL = mySQL + " [Order Details].Qty, [Order Details].[Unit Price], Orders.Paid"

mySQL = mySQL + " INTO UnpaidOrdersSummaryTable" mySQL = mySQL + " FROM Orders INNER JOIN [Order Details]" mySQL = mySQL + " On Orders.OrderID=[Order Details].OrderID" mySQL = mySQL + " WHERE (((Orders.Paid)=False))"

'Now turn off warning and execute the SQL statement. DoCmd.SetWarnings False DoCmd.RunSQL mySQL DoCmd.SetWarnings True

0 0

Post a comment