Copy table using a form

In the database I am developing, users have to be able to copy a table, no data, structure only. The source table is always the same one. Users have to be able to specify the name of the new table.
I would like to have them do this on a form, easy of use is important. Any ideas on this? Thanks. Trucktime

Hi Trucktime

This will take a fairly simple code to create what you are looking for.

First create a table called testTable to test this code out with. Place what ever fields you like in the table and save it. Do not put any records into the table.

Next open the form that will have the command button to create a new table from the testTable.

Open the code window and place the following two functions at the top of the window

Private Function BuildSQL(strSQL As String) As Boolean
Dim strSELECT As String 'This builds the sql for the temp make table query
Dim strFROM As String
strSELECT = "* "
strFROM = "testTable"

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM

BuildSQL = True
End Function

Private Function BuildTable(strSQL As String, strTableName As String) As Boolean
Dim db As Database
Dim qfAction As QueryDef

Set db = CurrentDb
'Next code will delete a table with the same name if user selected the same table name again
On Error Resume Next
db.TableDefs.Delete strTableName
On Error GoTo 0

strSQL = Replace(strSQL, " FROM ", " INTO " & strTableName & " FROM ")

Set qfAction = db.CreateQueryDef("", strSQL) ' Run the temp make table query
qfAction.Execute dbFailOnError
qfAction.Close

BuildTable = True

End Function

The first function will create the SQL for the temporary make table query. The second function will create the temp make table query and run the query at the same time to give you a new table in the database

On the OnClick event of your command button place the following code:

Dim strSQL As String
Dim strTableName As String
strTableName = InputBox("Enter the Name of the New Table")
If Not BuildSQL(strSQL) Then
MsgBox "There was a problem building the SQL String"
Exit Sub
End If
If Not BuildTable(strSQL, strTableName) Then
MsgBox "There was a problem building the Table"
Exit Sub
End If

When the user presses the command button, a message box will pop up and ask the user for the name of the new table that they will create. After pressing the button, open the database and you will find the table with the name that the user typed in and it will be an exact copy the tempTable you created earlier. You can use this code and substitute the name with the table you want to duplicate. If the table already contains records, you can use a delete query to delete the records and place that code snippet right at the end of the code of your command button.

See if this works for what you wanted
lwells