Copying a Table

The procedure in Hands-On 11-2 uses the SQL SELECT.. .INTO statement to select all records from the Customers table in the Northwind database into a new table called CustomersCopy. The SELECT.. .INTO statement is equivalent to a MakeTable query in the Microsoft Office Access user interface. This statement creates a new table and inserts data from other tables. To copy a table, the SQL statement is passed as the first argument of the Execute method of the Connection object. Note that the copied table will not have the indexes that may exist in the original table.

^^ Note: Make sure that the Northwind database is in the same folder as the currently open database, or change the path to the Northwind database in the procedure code.

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the Copy_Table procedure shown below.

3. Choose Run | Run Sub/UserForm to execute the procedure.

Sub Copy_Table()

Dim conn As ADODB.Connection Dim strTable As String Dim strSQL As String

On Error GoTo ErrorHandler strTable = "Customers"

Creating and Manipulating Databases with ADO

strSQL = "SELECT " & strTable & ".* INTO " strSQL = strSQL & strTable & "Copy " strSQL = strSQL & "FROM " & strTable

Debug.Print strSQL

Set conn = New ADODB.Connection conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\Northwind.mdb"

conn.Execute strSQL

conn.Close

Set conn = Nothing

MsgBox "The " & strTable & " table was copied." Exit Sub

ErrorHandler:

If Err.Number = -2147217900 Then conn.Execute "DROP Table " & strTable Resume Else

MsgBox Err.Number & ": " & Err.Description End If End Sub

0 0

Post a comment