Retrieving ADO recordset doesn't bring back full set

[BellyGas]

I'm attempting to update a legacy report to a 64Bit environment. The report in question runs just fine under 32bit office using the 32bit Microsoft ODBC driver for Oracle.

When attempting to run this under a 64bit office environment running the 64bit Oracle ODBC driver (cos Microsoft never made a 64bit version of theirs) I'm getting some very odd results:


code:

Please Login or Register  to view this content.

The above only brings back 1 row of data, it should bring back 6 rows.

Here's the interesting bit. The command Rst.MoveFirst is in there because......if I stop the code just before the copyfromrecordset command and manually issue an Rst.movefirst command from the immediate window.....and then resume at the copyfromrecordset command.....it brings back the correct 6 rows of data. However, when the rst.movefirst command in the code itself.....it doesn't work and only brings back 1 row of data. I can even run the code as far as the copyfromrecordset command, so the rst.movefirst has already run......F8 the copyfromrecordset and it'll bring back 1 row of data......manually issue the movefirst command and then rerun the copyfromrecordset.....and it'll bring back 6 rows.

Also....on both the 32bit and 64bit versions, polling the rst.recordcount brings back -1.

What the actual f.....?


oh P.S. If I specify a cursor type of 'adopenstatic' in the Rst.Open command......Excel crashes.

[xlnitwit]

Hi,

Recordcount often brings back -1 depending on the cursor location and type.

You shouldn't actually need a MoveFirst command there- what happens if you remove it?

[BellyGas]

It brings back 1 row of data. The only reason it's in there is because I found that manually doing an rst.movefirst command from the immediate window after the recordset had been opened then made the copyfromrecordset command bring back the full recordset.

[xlnitwit]

Very strange. Does it make any difference if you change the buffer size setting (FBS in the connection string) to a larger value?

[BellyGas]

No, tried 500000, all it did was make the Db response time to triple.

[Doc.AElstein]

Hi BellyGas
I expect you are way , way ahead of me on all this ADO stuff..
_.. so this is just a shot in the dark…

I found the cursor options to be a bit hit and miss when ever I have dabbled in ADO stuff.
As Don said .RecordCount often returns -1.
It often did with me a lot.
I would experiment with all the CursorType and LockType ( https://www.excelforum.com/developme...ml#post4653550 )

Sometimes my best results often came however, from not specifying CursorType and LockType, but instead including a _..
oRst.CursorLocation = adUseClient
_.. just after the Set of the ADODB.Recordset, oRst
That made my codes a bit faster, and cured a .RecordCount = -1 problem

I might be talking rubbish, as I do not know exactly what .CursorLocation does, –so just a shot in the dark….

( Even if you are late binding , you will need to set a reference for Microsoft Active X Data Objects 2.5 Library, ( or similar ) for .CursorLocation to work ( https://www.excelforum.com/excel-pro...ml#post4657419 )

Alan

( P.s. I decided not to use .CopyFromRecordset or GetRows() .
I always found looping through records, once I knew .RecordCount in VBA to fill a VBA Array of the Recordset never slower and even surprisingly quicker occasionally.
.CopyFromRecordset or GetRows() , I found to be a pain in debugging, and even for fairly big data files I never saw any speed advantages using .CopyFromRecordset or GetRows() )

[xlnitwit]

Even if you are late binding , you will need to set a reference for Microsoft Active X Data Objects 2.5 Library, ( or similar ) for .CursorLocation to work

It is simpler to either use the literal value(s) or just declare the constant(s) yourself thus

Please Login or Register  to view this content.

There is no point in late binding if you set a reference anyway.

[Doc.AElstein]

Hi Don

It is simpler to either use the literal value(s) or just declare the constant(s) yourself thus…

I prefer not to declare the constants as it caught me out once with multiple declarations, https://www.excelforum.com/excel-pro...ml#post4653708
I prefer named arguments, - just a personal preference as it helps me remember what is going on

...There is no point in late binding if you set a reference anyway.

I agree with that. I just noticed that when I added a .CursorLocation to an existing code which was late binded, I got caught out once. So I was just mentioning it so that if anyone tried .CursorLocation out on an existing code, then they would have to make sure they had the reference set.
Alan

[xlnitwit]

But you only need the reference set if you use an enumeration from the ADO library without declaring it yourself. Using CursorLocation per se does not require a reference.

[Doc.AElstein]

Hi Don

But you only need the reference set if you use an enumeration from the ADO library without declaring it yourself. Using CursorLocation per se does not require a reference.

I expect I just do not understand what you are saying.
I had a late Binded code here: https://www.excelforum.com/excel-pro...ml#post4656066
https://www.excelforum.com/excel-pro...ml#post4657419
I added oRsT.CursorLocation = adUseClient
That code line errors without a reference
I am sure you are correct. I just don’t understand what you are saying.
Alan

[xlnitwit]

If you don't have a reference set and use adUseClient, it's an undeclared variable and will be coerced at runtime to a number value of 0. The valid values for CursorLocation are
adUseClient- 3
adUseClientBatch- 3
adUseNone- 1
adUseServer- 2
and thus passing 0 will indeed raise an error. If however you declare your own constant for adUseClient as I mentioned earlier, or use

Please Login or Register  to view this content.

there will not be any need for a reference. (on a side note, this has nothing to do with named arguments so I'm not really sure why you mentioned those)

All of that is true for any enumeration you use from any library to which you have not set a reference; it has nothing to do with CursorLocation specifically.

[BellyGas]

Found the issue: The IT dept here are a bunch of halfwits.

The issue is the server in question has a faulty RAM module and keeps seeing corrupted stack space. The bigger issue was that when we originally asked for a 64bit environment the request specified a virtual desktop machine.....and some BOFH from IT jumped in and insisted it should be a server instead.....so we let them get on with it. At 1.30pm today I went to IT with my concerns that this server they've built is, to coin a technical term: shagged.

To which I got the reply: Well you shouldn't be running Office on a server.

I was very close to being sent on an anger management course by HR.....

[Doc.AElstein]

@ Bellygas
Glad you got it sorted
I agree - sounds like your IT dept are …..xxxxxxx, but it ended up with me sorting out a problem I had had for a while. – and I learnt something useful
Half wits, nitwits, all happens for a reason I guess, what will be will be .. Variety is the spice of life …etc…
_..................

@ Don
Hi Don

If you don't have a reference set and use adUseClient, it's an undeclared variable,,,,The valid values for CursorLocation are...
All of that is true for any enumeration you use from any library to which you have not set a reference; it has nothing to do with CursorLocation specifically.

I see what you are saying now. - I see, and tried it out, … and as you say in the examples I had been looking at previously I could use the appropriate number without the “word” and then did not need the reference as I had needed for using the “word” instead.
I suppose that might be telling me that in the library those “words” are just defined as variables, - constant numbers as you showed.
Actually, that answers nicely now the question I had had about why I came across some defined constants in a code I copied. It never occurred to me at the time that that meant the code would work without a reference. That all makes sense now . Thanks
_.................

….on a side note, this has nothing to do with named arguments so I'm not really sure why you mentioned those….

That’s just me in my ignorance mixing up terminology I expect…..
This _..
a() = Split(Delimiter:="|", Expression:="a|b|c")
_..I believe would be a named argument version of
a() = Split(a|b|c","|")
( The later has to be in order, and in some cases might need a place holder, like , , . The former can be in any order )
I guess what we are talking about is something else – “enumerations”
I guess I should have just said I would prefer to use a “word version” or “word” in general to help me remember what is going on.
_.. Now that I have learnt this interesting “enumeration” thingy, I can go on to use it to good effect now ….
So like at the start of my module I can have something maybe not quite as bad as this:

Please Login or Register  to view this content.

Then I use it later in the code like this

Please Login or Register  to view this content.

Works nicely.
Thanks again
Alan