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 view, click the Make Table command in the Query Type group on the (Query Tools) Design tab, 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 field contains False. This query creates a new table named UnpaidOrdersSummaryTable, which is set in the query's Destination Table property.

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:

SELECT Orders.OrderID, 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 that the query produces into a table named

UnpaidOrdersSummaryTable.

When a Make-Table query executes, it first checks whether the destination table (UnPaidOrdersSummaryTable, in this example) exists. If that table exists, it's 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 is the same for any query — it's simply a matter of copying the SQL statement to the Code window and tweaking the statement so that 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 = "SELECT Orders.OrderlD, Orders.[Order Date], [Order Details].ProductID," mySQL = mySQL & " [Order Details].Qty, [Order Details].[Unit Price], Orders.Paid"

'Build mySQL string from query's SQL statement.

mySQL = "SELECT Orders.OrderlD, 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

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

Was this article helpful?

0 0

Post a comment