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

 

[jackd]

see SQL SELECT INTO Statement

 

[welshgasman]

The more I see output from ChapGPT, the more I despair.

 

[chazrab]

Hi welshgasman - I'm beginning to agree - with asking for help in coding. Other things have proved to be accurate and correct, because I back door check chatgpt's answers on others things for accuracy, as neither you or I, or anyone else has time to waste.. Been trying to figure out what's wrong with the code line I submitted and the error msg given - for one week! way too long. The correct syntax should be somewhere out there but I'm not finding it anywhere. In the SELECT SQL solution Jackd gave - I'm copying records from a Query result - NOT another already existing table. Am new to Access VBA, and a Query result just seems like a filter and not a true table. Just some thoughts. cr

 

[welshgasman]

Double check the syntax from that link posted, but I would have thought all you needed was

CurrentDb.Execute "Select * Into target_table From Query2"

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

 

[chazrab]

see SQL SELECT INTO Statement

I tried using SELECT SQL statement in a button click code. It keeps asking for 'expected Case' as if its recognizing this as me
wanting a Select Case is = statement. Nope, not wanting that. I'm new to Access VBA, but very experienced in writing Excel VBA applications, and
to me, the two are as different as a spreadsheet is to a true database. I'm trying to copy records from an Access Query into a Table,, not from an
existing table to another new table. I'm lost, but thanks for trying to help. cr

Double check the syntax from that link posted, but I would have thought all you needed was

CurrentDb.Execute "Select * Into target_table From Query2"

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

 

[chazrab]

Double check the syntax from that link posted, but I would have thought all you needed was

CurrentDb.Execute "Select * Into target_table From Query2"

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

New error msg with your code copied. I'm using a button click event on the form (after another button on the form runs the code
to give the Query result). to run this code - if that makes any difference..
Your code II copied

CurrentDb.Execute "Select * Into target_table From Query2"

,,,too few parameters?!

 

[welshgasman]

Not something I have ever used, but the link states table to table, not query to table.
So I am assuming you actually have a table called target_table ? crappy name btw
So replace query2 with the WHERE criteria of Query2.

The fact that it expects 2 parameters, leads me to believe target_table does not exist?

 

[Micron]

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.

 

[welshgasman]

@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]