ms access - SQL multiple queries response to variables -

i want make multiple queries in single connection

i like

filename = "d:\database.mdb" strcnn = "provider=microsoft.jet.oledb.4.0;data source=" & filename strsql = "select id [users];select distinct id [users];select count(name) [users];select phone [users]; "                  set objxdatabase = server.createobject("adodb.connection")                 objxdatabase.cnn = strcnn                 objxdatabase.sql = strsql                 arrrst = objxdatabase.getdatabasearray()                 set objxdatabase = nothing 

problem error

microsoft jet database engine error '80040e14' characters found after end of sql statement.

but when post directly sql server mananger through new query window normal response.

so how multiple response different variables or in array can each response different variable

response1 = "select id [users];" response2 = "select distinct id [users];" responsenth = ... 

you can't single command because need return 4 separate datasets - 1 command = 1 dataset returned (even if empty). there's no reason close database connection between queries; open connection, execute query ids, query distinct ids, query count names, query list phone numbers, , close connection. each of these disparate result datasets - ids not tied phone numbers instance.

maybe try this....

filename = "d:\database.mdb" strcnn = "provider=microsoft.jet.oledb.4.0;data source=" & filename strsql1 = "select id [users]" strsql2 = "select distinct id [users]" strsql3 = "select count(name) [users]" strsql4 = "select phone [users]; " set objxdatabase = server.createobject("adodb.connection") objxdatabase.cnn = strcnn objxdatabase.sql = strsql1 arrrst1 = objxdatabase.getdatabasearray() objxdatabase.sql = strsql2 arrrst2 = objxdatabase.getdatabasearray() objxdatabase.sql = strsql3 arrrst3 = objxdatabase.getdatabasearray() objxdatabase.sql = strsql4 arrrst4 = objxdatabase.getdatabasearray() set objxdatabase = nothing 

this not tested chunk of code, rather indication of how think need proceed. performance, biggest hit opening , closing database connection. once connection open, running 4 such queries before close should not problem (depends how data there is, you're using jet , .mdb file i'm going guess isn't millions of records). hope works you.