SQL Problem

[JohannIcon]

Dear All, I have an sql problem which I cannot solve. Let me give you my scenario.

I have an Access table, from where I am retreiving the userID's there is in it, so I open up a recordset and populate it. Then what i want to do is I have a dataflex database, and from the userid's I collected from the Access table, I want to loop in this table and get the userid's there is in the Dataflex and display them. If they were 2 Access databases, I would have done a query, but I cannot (or don't know how) to do a query from both different databases.

So the first recordset is ok and being populated, then I am creating the second recordset in the loop like this:-

'Recordset from the Dataflex database
Set rsUsers=Server.CreateObject("ADODB.Recordset&quot
rsUsers.ActiveConnection = cnnDFlex
rsUsers.CursorType = 1
rsUsers.CursorLocation = 2
rsUsers.LockType = 1

rsUserGroups.movefirst
do
'if there are users in this group then
UserID = rsUserGroups("userid&quot
'Now check with the xersites table to check which users are in this group
sqlUsers="SELECT * from xersites WHERE record_number=" & userid
rsUsers.open(sqlUsers)

The error that i am getting is:-

ADODB.Recordset (0x800A0E79)
Operation is not allowed when the object is open.

I am assuming that it is because of opening the recordset more than once in the loop. How can i solve this problem? Any ideas?

Thanks for all your help and time

 

[Falcon99]

Well my suggestion would be to create another recordset variable:

Set rsUsers2=Server.CreateObject("ADODB.Recordset&quot


Then use this for the new result set:

rsUsers2.open(sqlUsers)


Hope this helps,
Falcon99

 

[JohannIcon]

Thanks for your help Falcon. I managed to concatenate the sql string to bring the desired results.

Thanks