Parameter Query

[bkessinger]

Hi all,

I am trying to create a make-table query where the name of the table would be a variable (use the same query to change the table name at runtime). I need assistance with how to do this. Any and all suggestions are appreciated. TIA.

Bill K.

[HansV]

You have to do this using VBA. One option is to modify the SQL of a stored query, but it's easier to execute an SQL string that you assemble in the code.

Create a form.
Place a text box txtTableName on the form.
Also place a command button cmdOK on the form.
Create an On Click event procedure for this button:

Code: Select all

Private Sub cmdOK_Click()
  Dim strSQL As String

  If IsNull(Me.txtTableName) Then
    Me.txtTableName.SetFocus
    MsgBox "Please enter a name for the table.", vbExclamation
    Exit Sub
  End If

  strSQL = "INSERT INTO [" & Me.txtTableName & "] ([FieldName1], [FieldName2]) " & _
    "SELECT [FieldName3], [FieldName4] FROM [SourceTable] " & _
    "WHERE [SomeField]=1"
  CurrentDb.Execute strSQL, dbFailOnError
End Sub

You must, of course, modify the SQL string for your situation.

[bkessinger]

Here is my SQL. Getting "expected end of statement" errors.

strsql = "INSERT INTO [" & me.txtTableName "] ([OffenderID], " _
& " [FirstName], [LastName], [Address], [City], [State], [Zip], " _
& " [VehicleMake], [StateOfPlate], [VehicleLicenseNo], [OwnerName], " _
& " [CaseNo], [InfractionDate], [InfractionTime], [HearingDate], " _
& " [InfractionLocation], [SectionNo], [ViolationDescription]) " _
& "SELECT [OffenderID], [FirstName], [LastName], [Address], [City], [State], [Zip], " _
& " [VehicleMake], [StateOfPlate], [VehicleLicenseNo], [OwnerName], " _
& " [CaseNo], [InfractionDate], [InfractionTime], [HearingDate], " _
& " [InfractionLocation], [SectionNo], [ViolationDescription] FROM [qryOffensesByDate] " & _
"WHERE [HearingDate] >= [forms]![frm_availability]![beginningdate] and " _
& " [HearingDate] <= [forms]![frm_availability]![endingdate];"

Please advise. Thanks.

[HansV]

Sorry, I omitted an &. The line

strSQL = "INSERT INTO [" & txtTableName "] ([OffenderID], " _

should be

strSQL = "INSERT INTO [" & txtTableName & "] ([OffenderID], " _

You can simplify the code slightly to

strSQL = "INSERT INTO [" & txtTableName & "] ([OffenderID], " _
& " [FirstName], [LastName], [Address], [City], [State], [Zip], " _
& " [VehicleMake], [StateOfPlate], [VehicleLicenseNo], [OwnerName], " _
& " [CaseNo], [InfractionDate], [InfractionTime], [HearingDate], " _
& " [InfractionLocation], [SectionNo], [ViolationDescription]) " _
& "SELECT [OffenderID], [FirstName], [LastName], [Address], [City], [State], [Zip], " _
& " [VehicleMake], [StateOfPlate], [VehicleLicenseNo], [OwnerName], " _
& " [CaseNo], [InfractionDate], [InfractionTime], [HearingDate], " _
& " [InfractionLocation], [SectionNo], [ViolationDescription] FROM [qryOffensesByDate] " _
& "WHERE [HearingDate] Between #" & Me.BeginningDate & "# And #" _
& Me.EndingDate & "#"

[bkessinger]

Thanks for your reply. The procedure seems to run, but no table is created. See attached.

[HansV]

There are two problems.

1) You omitted the line

CurrentDb.Execute strSQL, dbFailOnError

2) I mistakenly created the SQL for an append query instead of a make table query.

Moreover, if the user specifies the name of an existing table, you may want to delete that table before executing the make table query.

Here is the corrected code:

Code: Select all

Private Sub cmdOffensesByDate_Click()
    Dim strSQL As String

    If IsNull(Me.txtTableName) Then
        MsgBox "Please Enter a Table Name", vbExclamation
        Me.txtTableName.SetFocus
        Exit Sub
    End If

    On Error Resume Next
    ' Delete the table if it already exists
    DoCmd.DeleteObject acTable, Me.txtTableName
    On Error GoTo Err_cmdOffensesByDate_Click

    strSQL = "SELECT [OffenderID], [FirstName], [LastName], [Address], " & _
        "[City], [State], [Zip], [VehicleMake], [StateOfPlate], " & _
        "[VehicleLicenseNo], [OwnerName], [CaseNo], [InfractionDate], " & _
        "[InfractionTime], [HearingDate], [InfractionLocation], [SectionNo], " & _
        "[ViolationDescription] INTO [" & Me.txtTableName & "] " & _
        "FROM [qryOffensesByDate] WHERE [HearingDate] Between #" & _
        Me.BeginningDate & "# and #" & Me.EndingDate & "#"
    CurrentDb.Execute strSQL, dbFailOnError

Exit_cmdOffensesByDate_Click:
    Exit Sub

Err_cmdOffensesByDate_Click:
    MsgBox Err.Description
    Resume Exit_cmdOffensesByDate_Click
End Sub

[bkessinger]

Thank you Hans. That did the trick.