Multiple queries on MS Access

Hi,

I am going through some serious problem with ms access queries. I am trying to run multiple statements on ms access database in the "SQL View".

Moreover when I try to solve this problem through recordsets in ASP 3.0, I experience the same problem.

the error is thrown "characters found after the end of SQL statement"

I want to run 2 statements one after another on the same record set execution.

Example:

select * from temp; drop table temp;

its not working. ..eeeeh

why the hell ms access does not support multiple statements.

can anyone help me on this..

thanks,
Danish

Hi,
Have you found a solution?
I am going through the same problem. I would like to create multiple queries from the SQLView but it won't let me. It gives me the same error message you received.
thanks
Ofilha

try this

dim sqltext as string

sqltext = "select * from temp"
docmd.runsql sqltext

sqltext ="drop table temp"
docmd.runsql sqltext

I found that when I copied the coding from the sql after creating a query the ; caused a lot of problems, using your example the sql for the query was shown as
select * from temp; but to run it using coding I had to remove the ; e.g. "select * from temp"

Hi,
I have had the same problem. I can only do one query at a time. If i try more than one after the semicolon, i get an error message just like the one described earlier.

I am using the query tool to create my queries. Your solution appears like VB code. Can we use VB code inside the queries? Or are you saying that i need to create a program to run my queries.

My question is probably rather newbie, but i don't have much experience with MS Access.
Thanks.

It is VBA code Visual Basic for Access.

No, you cannot run VB code inside a query.

If you are just creating queries and then running them after each other then you can create a module or use code for when you click on a button to run your queries.

Module code
Sub run_queries()
docmd.openquery "query 1 name"
docmd.openquery "qury 2 name"
end sub

Button
sub update_results()
docmd.openquery "query 1 name"
docmd.openquery "qury 2 name"

end sub

I am using the query tool to create my queries. Your solution appears like VB code. Can we use VB code inside the queries? Or are you saying that i need to create a program to run my queries.

My question is probably rather newbie, but i don't have much experience with MS Access.

Please ignore the last two paragraphs in the previous post.

Copy and paste error.

Thanks much Allan,
I will give it a try and will keep you posted.