Problems with App role

Hello I have a huge problem with Application
role


Dim oConn as new ADODB.Connection
Dim sConnectionString as string
Dim oRec as ADODB.Recordset

sConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=false;" _
& "Initial Catalog=Recording;Use Procedure for Prepare=1;" _
& "Auto Translate=true;Packet Size=4096;Locale Identifier=1033;" _
& "Connect Timeout=15;Workstation ID=SVEPPUR;Data Source=SVEPPUR"

oConn.Provider = "sqloledb"
oConn.Open sConnectionString, "be3", "bjossi"

oConn.Execute "sp_setapprole 'LabDatabase','123456'"

set oRec = oConn.Execute("SELECT * FROM tblPatientInfo;")

oConn.Execute "SELECT * FROM vScheduleWithPatient;"




In this sample code I will get a error on the second Select sentace because I have not close the first recordset, apparently I can only open one recordset at a time when in this mode

Now if I change the login so that administartor logs in (but still I set the AppRole), then suddenly I'm alowed to open more than one recordset at a time.

I really need to be able to use more than one recordsets at a time, so if anyone knows what the problem might be then please let me know

why don't you open a second connection and use a second recordset?


Dim oConn as new ADODB.Connection
Dim oConn2 as new ADODB.Connection
Dim sConnectionString as string
Dim oRec as ADODB.Recordset

Dim oRec2 as ADODB.Recordset
sConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=false;" _
& "Initial Catalog=Recording;Use Procedure for Prepare=1;" _
& "Auto Translate=true;Packet Size=4096;Locale Identifier=1033;" _
& "Connect Timeout=15;Workstation ID=SVEPPUR;Data Source=SVEPPUR"

oConn.Provider = "sqloledb"
oConn.Open sConnectionString, "be3", "bjossi"
oConn2.Open sConnectionString, "be3", "bjossi"

oConn.Execute "sp_setapprole 'LabDatabase','123456'"

set oRec = oConn.Execute("SELECT * FROM tblPatientInfo;")

set oRec2 = oConn.Execute ("SELECT * FROM vScheduleWithPatient;")

why don't you open a second connection and use a second recordset?


Dim oConn as new ADODB.Connection
Dim oConn2 as new ADODB.Connection
Dim sConnectionString as string
Dim oRec as ADODB.Recordset

Dim oRec2 as ADODB.Recordset
sConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=false;" _
& "Initial Catalog=Recording;Use Procedure for Prepare=1;" _
& "Auto Translate=true;Packet Size=4096;Locale Identifier=1033;" _
& "Connect Timeout=15;Workstation ID=SVEPPUR;Data Source=SVEPPUR"

oConn.Provider = "sqloledb"
oConn.Open sConnectionString, "be3", "bjossi"
oConn2.Open sConnectionString, "be3", "bjossi"

oConn.Execute "sp_setapprole 'LabDatabase','123456'"

set oRec = oConn.Execute("SELECT * FROM tblPatientInfo;")

set oRec2 = oConn2.Execute ("SELECT * FROM vScheduleWithPatient;")

Sorry for posting the "same" reply twice.
The first one had an error (set oRec2 = oConn...., should be oConn2...)