Custom table name

[Kivan]

I've created a query which copies a table. The thing is I want to choose table name everytime I use this query. So i want the table name not to be defined but to be chosen by user everytime he runs query. Is there any way of putting a parameter into the table name? I'm very beginner with Access so the easiest solutions will be welcome .



regards

[JoeM]

The only way I can think of is to use a Form where the user can enter/select the name of the table they wish to copy, then using VBA write the SQL code for the query to copy that table.

If you create an example of your Make Table Query in Query Builder, if you switch to SQL view, this will show you the structure of the SQL code that you will need to build in VBA.

I created a simple form with three objects:
- A text box for entering in the name of the table you want to copy (I named it txtFromTable)
- A text box for entering in the new name of the table you are creating (I named it txtToTable)
- A command button to click to perform the copy

Here is what the code behind the Click event of my command button looks like.

Private Sub cmdCopyTable_Click()
    Dim mySQL As String
    
'   Verify there are entries in both fields
    If IsNull(txtFromTable) Or IsNull(Me.txtToTable) Then
        MsgBox "Missing table name entry!", vbOKOnly, "COPY CANCELLED!!!"
        Exit Sub
    End If
        
'   Build SQL code to copy table
    mySQL = "SELECT [" & Me.txtFromTable & "].* " & _
            "INTO [" & Me.txtToTable & "] " & _
            "FROM [" & Me.txtFromTable & "];"
    
'   Run SQL code to copy table
    DoCmd.RunSQL mySQL
    
End Sub