Copying a Query result to a new table

[chazrab]

I am trying to copy Query2 records to a new table.
This code

CurrentDb.Execute "Select Query2""Into target_table From Query2"

does not run. It keeps giving me following error message:
The solution came from chatGPT.

Can someone please help.

Thanks cr

 

[chazrab]

Using a query or table as the domain makes no difference. This works in query design
SELECT * INTO [NEWTABLE] FROM [AA];
however, VBA sometimes cannot "see" the sql side of Access. If you're getting parameter prompts and you're absolutely sure the object names are correctly spelled and they exist and things are correctly concatenated then you need to define the parameters in code or in the query properties. If you have no actual query then the latter idea is not possible. One way to test the sql you've coded is to debug.print it, copy and paste into a new query in sql view then switch to data sheet view. If it runs then you know its construct is OK, which tells you it's a vba problem. You can go the route of declaring parameter objects and all that, but long ago I discovered a work around by creating variables and assigning date/number/text values to them and using the variables in the sql concatenation instead of form control references.

Hi Micron:
Your reply is an advanced answer. Not sure if I understood everything you've stated above but I put the query in desgn mode and clicked on SQL view.
This is the actual SQL code that runs Query2 successfully every time, no matter what values I tell it to find:

SELECT Sheet2.Verse, Sheet2.NASB, Sheet2.Notes
FROM Sheet2
WHERE (((Sheet2.Verse) Like "*" & Forms!SEARCHF!Verse & "*") And ((Sheet2.NASB) Like "*" & Forms!searchf!NASB & "*"));

That said, how do I add what code to automatically copy the records this gives to a new table, named say, RESULT?
Each time the Query, Query2 runs, the RESULT table would be overwritten. In Excel VBA, this is accomplished by using

 Sheets("RESULT").UsedRange.ClearContents

But as you know, that's a sheet, not a database and that Excel VBA not Access VBA. Would something like this work?

SELECT Sheet2.Verse, Sheet2.NASB, Sheet2.Notes
FROM Sheet2
WHERE (((Sheet2.Verse) Like "*" & Forms!SEARCHF!Verse & "*") And ((Sheet2.NASB) Like "*" & Forms!searchf!NASB & "*"));
[now copy this Query result to the RESULT table] - not the code of , just the explanation of what should be done next

Thanks again for all your help.
cr

 

[chazrab]

@Micron
Something like this you mean?

   Const SQL As String = _
        "INSERT INTO TransactionTable " & _
            "( TheDate, Amount, Account ) " & _
        "VALUES " & _
            "( p0, p1, p2 )"
           
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters("p0") = Me.DateTextBox
        .Parameters("p1") = Amount
        .Parameters("p2") = 1
        .Execute dbFailOnError
    End With
[code]

welshgasman - I sure wish I understood what you did here, in response to Micron. Thx for your helpful efforts cr

 

[chazrab]

welshgasman - I sure wish I understood what you did here, in response to Micron. Thx for your helpful efforts cr

this sort of works - at least not error messages. But it does not populate the RESULT table with the Query records.
What it does is put one record in the RESULT table with the value I typed in to search for.
Did I get your code wrong? The little example in the image gives the Field names and records that should
be copied, transferred,, etc to the the RESULT table

Per below, the first image gives the result of Query2. A button on the form to copy to the RESULT table gave
the second image, which is the value to find, not the 9 records.

cr

 

[chazrab]

...this is the code I used, copied from you, with my values:

Private Sub Command36_Click()
'CurrentDb.Execute "Select * Into target_table From Query2" 'welshgasman 's code
 Const SQL As String = _
        "INSERT INTO RESULT " & _
            "( Verse, NASB, NOTES ) " & _
        "VALUES " & _
            "( p0, p1, p2 )"
          
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters("p0") = Me.NASB
        .Parameters("p1") = NASB
        .Parameters("p2") = NOTES
        .Execute dbFailOnError
    End With

Verse, NASB, and NOTES are the Field names of the Table searched,

 

[welshgasman]

...this is the code I used, copied from you, with my values:

Private Sub Command36_Click()
'CurrentDb.Execute "Select * Into target_table From Query2" 'welshgasman 's code
 Const SQL As String = _
        "INSERT INTO RESULT " & _
            "( Verse, NASB, NOTES ) " & _
        "VALUES " & _
            "( p0, p1, p2 )"
        
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters("p0") = Me.NASB
        .Parameters("p1") = NASB
        .Parameters("p2") = NOTES
        .Execute dbFailOnError
    End With

Verse, NASB, and NOTES are the Field names of the Table searched,

Well, yes that will only do one record, unless you have some sort of Where criteria to indicate more records.
However that will put the same values into each record, which I doubt is what you want?

 

[welshgasman]

Why can't you use that Select in post #11 as source for the Insert?

 

[chazrab]

I did use it in the beginning, and a few moments ago. What it attempted to do is paste the entire source sheet of 32,000 records
in the new table, not the Query results.
Maybe a query is not the best way to do this. In Excel VBA I used the FIND method with a Do loop to find and copy all records
to a new sheet, which is cleared each time a FIND is run and it works great - but, this is a sheet and Excel VBA - NOT Access VBA.
I copied my Excel Find code to an Access button and initial error messages did not recognize the code lines with the word 'cell'.
Here's the Excel code block

Private Sub cmdFIND_Click()
 Sheets("MAINARES2").UsedRange.ClearContents  --->clears the destination sheet
  Dim lastrow, lastrow2 As Integer, X As String, c As Range, rw As Long, firstAddress As Variant, rowno As Variant, RownoA As Variant
          X = MAINWINDOW2.TextBox11.Value
                     With Worksheets("Sheet2").Range("E1:E31103")  ---> col. to search
                          Set c = .FIND(X, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
                              If Not c Is Nothing Then
                                        rw = 1
                                        firstAddress = c.Address
                                        Do
                                            Worksheets("Sheet2").Select
                                            c.Select
                                            Range(Cells(c.Row, 2), Cells(c.Row, 7)).Copy Destination:=Sheets("MAINARES2").Range("B" & rw) --->copy operation
                                           rw = rw + 1
                                           Set c = .FindNext(c)
                                           Loop While Not c Is Nothing And c.Address <> firstAddress
                                            lastrow = Sheets("MAINARES2").Range("B" & rows.count).End(xlUp).Row
                                            If lastrow = 1 Then
                                                    Range(Cells(c.Row + 7, 2), Cells(c.Row, 7)).Copy Destination:=Sheets("MAINARES2").Range("B" & rw)
                                            Else
                                            End If
                              Else
                                 MsgBox "value not found"
                              End If
                     End With
                               rowno = Sheets("MAINARES2").Range("B2").End(xlDown).Row
                               Sheets("MAINARES2").Range("H1").Value = rowno 'total rows found in search
                               Sheets("MAINARES2").Range("I1").Value = X 'value to find, i.e.,, "last days"
                               MAINWINDOW2.ListBox1.ListIndex = 0
                               MAINWINDOW2.TextBox4.Value = Sheets("MAINARES2").Range("H1").Value
                                Dim firstrow As Integer, myrange As Range
                                 Set myrange = Sheets("MAINARES2").Range("B1")
                                 If myrange <> " " Then
                                      firstrow = myrange.Row
                                 Else
                                      MsgBox "First row is blank"
                                 End If
                 MAINWINDOW2.TextBox9.Value = Sheets("MAINARES2").Range("I1").Value

 

[welshgasman]

I am off diving in a minute, but while excel vba is similar to access vba, they have different objects, So you cannot just copy code from excel and expect it to run as is, unless it is a simple function that does not reference any objects.

 

[Micron]

Sorry for the late response. Yes WGM that is along the lines of what I meant for dealing with parameters. Chazrab, I don't' think it was meant for you do actually try to use as written.

Each time the Query, Query2 runs, the RESULT table would be overwritten

Two ways to do this.
1) clear the table first - DELETE * FROM tableNameHere; then run an append query on that table. If you can do this with actual queries it might be easier for you.
2) run a make table query with a defined table name and it will replace that table. You'd make a Select query first to ensure you get the desired results, then change it to a make table in design view. Research how to use make table queries for that approach. Remember that if using form field references in queries (e.g. Forms!myForm.NASB) referenced form must be open.

I'm getting lost here. The original post was about appending that didn't work (just too many or misplaced quotes?). The samples in post 11 are about Select sql statements. Why would they be relevant?