What's wrong with this statement

[zhuanyi]

Hello,
I was trying to create a chunk of code like follows:
sql = "SELECT * INTO AppendAllFields FROM [some table];"
Set qry = db.CreateQueryDef(qryName, sql)
DoCmd.OpenQuery (qryName)
where qryname is alr. defined.

When I wronf it, it kept giving me the error on the DoCmd line saying that runtime error 3001, invalid argument....

Anyone could help?

Thanks!

 

[WayneRyan]

Z,

What is AppendAllFields?

Does it have the SAME structure as [some table]?

Wayne

 

[zhuanyi]

they are both strings with the name of the table, so i assume the answer is yes, i did not define the structure of the AppendAllFields initially so I assume it would inherite the structure of sometable as well
Thanks!

 

[WayneRyan]

Z,

That is true it will inherit the structure.

That just leaves the query. Is it what you expected in Design View?
How about right-clicking to SQL view?

Wayne

 

[zhuanyi]

SQL View says:
SELECT * INTO AppendAllFields
FROM [some table];
But the design view is blank

 

[WayneRyan]

Z,

That looks like it should work.

While I try it, Is it really called [some table]?

Wayne

 

[WayneRyan]

Z,

Worked fine here.

Wayne

 

[zhuanyi]

interesting, i must have messed up something in the tables then, thx anyways.

 

[willpower]

they are both strings with the name of the table...

Do you mean that AppendAllFields and [some table] are string variables in your code? In which case, try something like:

sql = "SELECT * INTO " & AppendAllFields & " FROM " & [some table] & ";"

The brackets around [some table] suggest to me that it may be a value you're pulling from a table/query/form earlier in your procedure?

willpower

 

[modest]

DoCmd.OpenQuery (qryName)

1: make sure qryName is a variable with the actual query name
2: remove the parentheses; DoCmd.OpenQuery qryName

Also, make sure "Some Table" is the actual name of the table or query you are copying from. If [Some Table] is a variable, then your SQL should be:
sql = "SELECT * INTO AppendAllFields FROM [" & TABLENAME_VARIABLE & "];"