tags:

views:

224

answers:

5

in access i am doing this:

insert into accounts_changes 
 (select * 
  from accounts 
  where [Agency Code] 
  in (select * from tableimport))

it says that it doesnt like this INSERT statement

update:

sSql = "insert into accounts_changes (select * from Accounts where [Agency Code] in (select [Agency Code] from tableimport))"

i did what mark said and it is still giving me the same error message

syntax error in INSERT INTO statement

when i do this:

ssql = "select [Agency Code] from tableimport"
CurrentDb.Execute ssql

it says CANNOT EXECUTE SELECT QUERY

+3  A: 

You probably need to select the agency code from tableimport instead of *.

bshields
+14  A: 

This is wrong:

select *
from accounts
where [Agency Code] in (select * from tableimport)

You can only select one column in the subquery for an IN clause. You want something like this:

select *
from accounts
where [Agency Code] in (select [Agency Code] from tableimport)

You need to check the exact name of the column in the table tableimport. The above is just my best guess.

Mark Byers
mark sorry this does not work unfortunately, im getting the same error statmeent
I__
You should provide the error message
Daniel DiPaolo
syntax error in INSERT INTO statement
I__
+2  A: 

You should specify the column you'd like to search on tableimport inner select.

Also, you could declare the fields of the table being inserted and the fields returned:

INSERT INTO TABLE_EXAMPLE (A, B) SELECT AA, BB FROM TABLE_ORIGIN
pcent
Yes that is the best practice and do anything else is to risk your data integrity if columns change. Additionally you probaly don't want the () around the select statement. I know in SQL server that would not work.
HLGEM
A: 

this worked:

insert into accounts_changes select * from Accounts where [Agency Code] in (select [Agency Code] from tableimport)
I__
Sorry, I overlooked your own answer. I'll leave mine up so you can look at the part about Execute with a SELECT query.
HansUp
no you are the man thank you again for your help
I__
Why use an IN clause instead of a plain old INNER JOIN?
David-W-Fenton
+1 for fenton cause he's the man!
I__
+2  A: 

This part may have misled you:

ssql = "select [AgencyCode] from tableimport"
CurrentDb.Execute ssql

Execute requires an "action" query (INSERT, DELETE, UPDATE, or SELECT INTO). When you give Execute a plain (row returning) SELECT query, you will always get error #3065, "Cannot execute a select query". It doesn't mean there was anything wrong with your SELECT statement. Test your SELECT statement by pasting it into SQL View of a new query.

You showed two variations of [AgencyCode] ... one with and another without a space between Agency and Code. Which is it?

I think your original INSERT statement had an extra pair of parentheses which aren't needed. Try it this way:

insert into accounts_changes
select * 
from Accounts
where [Agency Code] in (
    select [Agency Code] from tableimport)

If that still fails, verify you have the same number of fields, with the same field names and data types in both Accounts and accounts_changes. If the fields in the two tables don't match exactly, list the fields explicitly as @pcent showed you.

HansUp
While Access uses the term "action query," for general SQL terminology, these are DML statements.
David-W-Fenton
+1 for fenton cause he's the man!
I__
Access calling DML statements "action queries" used to make me cringe. Seems like a query should only be a row-returning SELECT statement. I finally gave in to make it easier to communicate with normal Access users.
HansUp
I understand, but you get ridiculed on SO for using Access-specific terminology to answer Access questions if it violates the rigid rules of those who never use Access. It's also good to not be provincial when it comes to terminology -- my comment was never intended as a criticism of your post, just a tangential comment/reminder.
David-W-Fenton