Run-Time 3705, Operation not allowed [open object]

[Trancemission]

I am an avid Unix DB programmer but have a current project tidying up Access. I have done some VBA coding before but seem to be having a little problem which I cannot solve. This simple code bombs out with:


'Run-time error '3705'

Operation is not allowed when the object is open'

'Declare vars
Dim cur As String
Dim x As Integer
'Db
Dim rst As Recordset
Dim cnn As Connection
Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
'
Dim rstEx As Recordset
Dim cnnEx As Connection
Set cnnEx = New ADODB.Connection
Set cnnEx = CurrentProject.Connection
Set rstRate = New ADODB.Recordset
'Run query, add to rst recordset
rst.Open "[b]SQL TO GET CURERNCY[/b];", cnnEx, adOpenKeyset, adLockReadOnly, adCmdText
cur = rst!Currency
'Loop through records
x = 0
For x = 1 To rst.RecordCount
'Get Currency
rstRate.Open "SELECT exchange_rates.currency, exchange_rates.rate, exchange_rates.code FROM exchange_rates WHERE exchange_rates.code like '" & cur & "';", cnn, adOpenKeyset, adLockReadOnly, adCmdText

However if i move rstRate.Open about the For x line:

rstRate.Open "SELECT exchange_rates.currency, exchange_rates.rate, exchange_rates.code FROM exchange_rates WHERE exchange_rates.code like '" & cur & "';", cnn, adOpenKeyset, adLockReadOnly, adCmdText


'Loop through records
x = 0
For x = 1 To rst.RecordCount
'Get Currency

Seems to work fine, even if I have For x = 1 to 50 :?

Trancemission
=============
If it's logical, it'll work!

 

[roppetech]

Are you closing rstRate before your NEXT line?
It might help to see the rest of the sub.
Also, which line gives you the error?

- RoppeTech

 

[Trancemission]

Sure,

The rest of the sub [only test so doesn't do anything yet]

' Lookup up currency from table here

rst.MoveNext
Next x

Set rst = Nothing
cnn.Close
cnnEx.Close

End Sub

The line that produces the error is when i try to open rstRate:

rstRate.Open "SELECT exchange_rates.currency, exchange_rates.rate, exchange_rates.code FROM exchange_rates WHERE exchange_rates.code like '" & cur & "';", cnn, adOpenKeyset, adLockReadOnly, adCmdText

Many Thanks.

Marcus



Trancemission
=============
If it's logical, it'll work!

 

[RoyVidar]

Sorry, that shows you don't close rstRate prior to entering the next loop, so you are trying to open a recordset that's alredy open.

I would also disambiguate the declarations:

[tt]dim rst as adodb.recordset
dim cnn as adodb.connection[/tt]

- both recordset and connection exists as DAO objects/properties

Be careful to rely upon recordcounts from ADO recordset, rather do a loop structure like this

[tt]do while not rst.eof
rstRate.Open "SELECT exchange_rates.currency, exchange_rates.rate, exchange_rates.code FROM exchange_rates WHERE exchange_rates.code like '" & cur & "';", cnn, adOpenKeyset, adLockReadOnly, adCmdText
' do something with the recordset
rstRate.Close
rst.movenext
Loop[/tt]

Roy-Vidar

 

[Trancemission]

thanks for that that I thought maybe I was accessing the same recordset.

However I fail to understand what is happening, I wish to create a recordset based on data in another looping recordset. ie: I loop thorugh all my hotel rooms which are all in different currency, I then want to lookup my exchange rate table based on the currency of the room I am looking at. I tried to prempt this by creating 2 connections and trying to keep these and the recordsets separate.

So how can I create a looping recordset and do lookups within that? I could use an array and loop thorough that but I have 200000 hotel rooms

Thanks for the other tips will amend.

I will rtfm 2nite a bit more I think - maybe thats why i can't get this loop structure going.

Thanks again

Marcus

Trancemission
=============
If it's logical, it'll work!

 

[RoyVidar]

I would rather return the question, why do you want to open multiple recordsets (multiple times)? Wouldn't this be much easier solved thru joining the tables in a query? It would perform faster, anyway.

It would perhaps help if you told us the purpose of this exercise, whith some sample data...

Roy-Vidar

 

[Trancemission]

Yes I think you have cracked it - coming from a unix sytle of coding I try to separate lookups. I can create a join no problem and that will work thanks!

Sample Data

TABLE: room_rates
---------------------------------------------------------
HotelCode | HotelName | RoomName | Cost | Curr
A1 | A1 Hotels | Room1 | 45 | USD
A1 | A1 Hotels | Room2 | 55 | USD
A2 | A2 Hotels | Room1 | 66 | USD
A2 | A2 Hotels | Room2 | 87 | USD

TABLE: excahnge_rate

LABLEL | Rate
---------------------------------------------------------
UKP | 1
USD | 1.75
EUR | 1.45

etc.......

I was going to loop thorough the rooms from the room_rates table and lookup the currency as I go through.

I understand now that a join query would work fine

However [as always] my next problem is:

I have another table which should contain more detailed data on the hotel, linked by the HotelCode field. I want to make sure that there is a record for each hotelcode in room_rates. Normally I would lookup though a query on room_rates [grouped by hotel code to get all types and no duplicates] - then do a lookup on my detailed table and see if a record exists. Will a join query help here? I don't think I can use this here I will apply some thought........


Thanks very much for you help, I undesrtand a lot more now

marcus

Trancemission
=============
If it's logical, it'll work!

 

[Trancemission]

Yes it should work shouldn't it? Just create the join query and see if I get any data back

Will go and try and then slap myself into VBA/Access programming mode

Cheers Again

Trancemission
=============
If it's logical, it'll work!

 

[RoyVidar]

Knowing that queries are faster and "preferred" I do, but creating them...

tblHotel, column HName? Findting rows without match.

[tt]select HName
from room_rates left join tblHotel on
tblHotel.HotelCode = room_rates.HotelCode
where room_rates.HotelCode is null[/tt]

Or was it the other way around?

[tt]select distinct HotelName
from tblHotel left join room_rates on
room_rates.HotelCode = tblHotel.HotelCode
where tblHotel.HotelCode is null[/tt]

SQL is far from my strong side, but is it something like this?

Roy-Vidar

 

[Trancemission]

Thats great the left join worked fine with IsNull After all that there was only 2 hotels not in there but still need to check

Thanks again for all your help

Marcus


Trancemission
=============
If it's logical, it'll work!