Using NextRecordset errors

[eagletender]

Does rs.nextrecordset not work when using Access/Jet? I can get it to work
when I am connecting to a sql database but not an Access database.

Provider=Microsoft.Jet.OLEDB.4.0

My code is as follows:
set rsCakes = Server.CreateObject("ADODB.Recordset")
rsCakes.ActiveConnection = acConn
rsCakes.Source = "SELECT * from tblProducts; select * from
tblCategories"
rsCakes.CursorType = 2
rsCakes.CursorLocation = 2
rsCakes.LockType = 3
rsCakes.Open
rsCakes_numRows = 0

It errors on the rsCakes.Open with the error:
Microsoft JET Database Engine (0x80040E14)
Characters found after end of SQL statement.


Is there a way to do this? Thanks.

[Foo Man Chew]

Maybe using a stored query, instead of a raw SQL statement?



"eagletender" <eagletender2001@yahoo.com> wrote in message
news:vinlvnnvus3i12@corp.supernews.com...

> Does rs.nextrecordset not work when using Access/Jet? I can get it to

work

> when I am connecting to a sql database but not an Access database.
>
> Provider=Microsoft.Jet.OLEDB.4.0
>
> My code is as follows:
> set rsCakes = Server.CreateObject("ADODB.Recordset")
> rsCakes.ActiveConnection = acConn
> rsCakes.Source = "SELECT * from tblProducts; select * from
> tblCategories"
> rsCakes.CursorType = 2
> rsCakes.CursorLocation = 2
> rsCakes.LockType = 3
> rsCakes.Open
> rsCakes_numRows = 0
>
> It errors on the rsCakes.Open with the error:
> Microsoft JET Database Engine (0x80040E14)
> Characters found after end of SQL statement.
>
>
> Is there a way to do this? Thanks.
>
>

[Bob Barrows]

It's Jet, not SQL Server. A Jet stored procedure (saved query) can only
return a single resultset.

Bob Barrows

Foo Man Chew wrote:

> Maybe using a stored query, instead of a raw SQL statement?
>
>
>
> "eagletender" <eagletender2001@yahoo.com> wrote in message
> news:vinlvnnvus3i12@corp.supernews.com...

>> Does rs.nextrecordset not work when using Access/Jet? I can get it
>> to work when I am connecting to a sql database but not an Access
>> database.
>>
>> Provider=Microsoft.Jet.OLEDB.4.0
>>
>> My code is as follows:
>> set rsCakes = Server.CreateObject("ADODB.Recordset")
>> rsCakes.ActiveConnection = acConn
>> rsCakes.Source = "SELECT * from tblProducts; select * from
>> tblCategories"
>> rsCakes.CursorType = 2
>> rsCakes.CursorLocation = 2
>> rsCakes.LockType = 3
>> rsCakes.Open
>> rsCakes_numRows = 0
>>
>> It errors on the rsCakes.Open with the error:
>> Microsoft JET Database Engine (0x80040E14)
>> Characters found after end of SQL statement.
>>
>>
>> Is there a way to do this? Thanks.