SQL Syntax Help Needed

  • Thread starter mattc66 via AccessMonster.com
  • Start date
M

mattc66 via AccessMonster.com

I want to add the following to VB Code. I an having trouble with the proper
syntax.

strSQL = "INSERT INTO APTRANS SELECT [qryAPTRANS.*] FROM qryAPTRANS"
DoCmd.RunSQL strSQL

Can anyone help?
 
D

Dirk Goldgar

mattc66 via AccessMonster.com said:
I want to add the following to VB Code. I an having trouble with the proper
syntax.

strSQL = "INSERT INTO APTRANS SELECT [qryAPTRANS.*] FROM qryAPTRANS"
DoCmd.RunSQL strSQL

Can anyone help?

Your brackets are invalid -- it should be "[qryAPTRANS].*". However, since
only one data source is involved, it should be enough to just write this:

strSQL = "INSERT INTO APTRANS SELECT * FROM qryAPTRANS"
 
M

mattc66 via AccessMonster.com

When it runs the DoCmd.RunSQL strSQL. I get an error. Run-Time Error 3126
Invalid bracketing of name '*]'. I replace the code with what you gave me, so
I am not sure why it's saying this?

Dirk said:
I want to add the following to VB Code. I an having trouble with the proper
syntax.
[quoted text clipped - 3 lines]
Can anyone help?

Your brackets are invalid -- it should be "[qryAPTRANS].*". However, since
only one data source is involved, it should be enough to just write this:

strSQL = "INSERT INTO APTRANS SELECT * FROM qryAPTRANS"
 
D

Dirk Goldgar

mattc66 via AccessMonster.com said:
When it runs the DoCmd.RunSQL strSQL. I get an error. Run-Time Error 3126
Invalid bracketing of name '*]'. I replace the code with what you gave me,
so
I am not sure why it's saying this?

Dirk said:
Your brackets are invalid -- it should be "[qryAPTRANS].*". However,
since
only one data source is involved, it should be enough to just write this:

strSQL = "INSERT INTO APTRANS SELECT * FROM qryAPTRANS"

Can you verify that it really is that particular RunSQL line that is raising
the error, and not some other statement? As you see, there are no brackets
in the SQL statement I provided.

If the error really is being raised by that SQL statement, then I wonder if
there's something wrong in the query, qryAPTRANS. Post the SQL of that
query.
 
M

mattc66 via AccessMonster.com

I got it to work. It had the old code in the memory. Do you know if I could
add a union the code? Then I wouldn't have to build a query.

Below is my qryAPTRANS. I am merging the two tables into the one APTRANS
table.

SELECT *
FROM [APTRANS_C1]

UNION ALL SELECT *
FROM [APTRANS_C2];

When it runs the DoCmd.RunSQL strSQL. I get an error. Run-Time Error 3126
Invalid bracketing of name '*]'. I replace the code with what you gave me, so
I am not sure why it's saying this?
[quoted text clipped - 6 lines]
strSQL = "INSERT INTO APTRANS SELECT * FROM qryAPTRANS"
 
J

John W. Vinson

I want to add the following to VB Code. I an having trouble with the proper
syntax.

strSQL = "INSERT INTO APTRANS SELECT [qryAPTRANS.*] FROM qryAPTRANS"
DoCmd.RunSQL strSQL

Can anyone help?

Try

INSERT INTO APTRANS SELECT * FROM qryAPTRANS

assuming that the table and the query have matching fieldnames. If they don't
you'll need to be explicit:

INSERT INTO APTRANS (field1, field2, field3, ...) SELECT field1, field2,
fieldmatchingfield3, ... FROM qryAPTRANS

For example, if APTRANS has an Autonumber primary key you would not ordinarily
insert anything into it, and would need to use the second syntax, leaving out
the autonumber field.

By the way, it would certainly be helpful if you indicate what KIND of
trouble. I'm guessing here, since you didn't post any error messages or other
symptoms, and may be altogether off base.
 
D

Dirk Goldgar

mattc66 via AccessMonster.com said:
Do you know if I could
add a union the code? Then I wouldn't have to build a query.

Below is my qryAPTRANS. I am merging the two tables into the one APTRANS
table.

SELECT *
FROM [APTRANS_C1]

UNION ALL SELECT *
FROM [APTRANS_C2];


You could do it using what's called a derived table, like this:

strSQL = _
"INSERT INTO APTRANS SELECT U.* FROM (" & _
"SELECT * FROM APTRANS_C1 " & _
"UNION ALL SELECT * FROM APTRANS_C2" & _
") AS U;"

DoCmd.RunSQL strSQL

On the other hand, in this case it seems to me you could get the same effect
by running two append queries, like this:

strSQL = "INSERT INTO APTRANS SELECT * FROM APTRANS_C1"
DoCmd.RunSQL strSQL
strSQL = "INSERT INTO APTRANS SELECT * FROM APTRANS_C2"
DoCmd.RunSQL strSQL

It's no big deal either way.
 
M

mattc66 via AccessMonster.com

That is exactly what I wanted...Worked great. I used the later suggestion.
Thank you!

Dirk said:
Do you know if I could
add a union the code? Then I wouldn't have to build a query.
[quoted text clipped - 7 lines]
UNION ALL SELECT *
FROM [APTRANS_C2];

You could do it using what's called a derived table, like this:

strSQL = _
"INSERT INTO APTRANS SELECT U.* FROM (" & _
"SELECT * FROM APTRANS_C1 " & _
"UNION ALL SELECT * FROM APTRANS_C2" & _
") AS U;"

DoCmd.RunSQL strSQL

On the other hand, in this case it seems to me you could get the same effect
by running two append queries, like this:

strSQL = "INSERT INTO APTRANS SELECT * FROM APTRANS_C1"
DoCmd.RunSQL strSQL
strSQL = "INSERT INTO APTRANS SELECT * FROM APTRANS_C2"
DoCmd.RunSQL strSQL

It's no big deal either way.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top