Multiple queries on MS Access

[danish_ara]

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

[ofilha]

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

[Allan Murphy]

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"

[ofilha]

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.

[Allan Murphy]

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.

[Allan Murphy]

Please ignore the last two paragraphs in the previous post.

Copy and paste error.

[ofilha]

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