?
Solved

adOpenDynamic v adOpenKeyset - Recordsets 'Bombing' out

Posted on 2004-11-28
66
Medium Priority
?
10,788 Views
Last Modified: 2008-01-09
Problem that's been bugging me

Back End - MS SQL Sever 2000
Front End - Access 2003 SP 1
MDAC 2.8 Installed
OS - Win XP Pro SP 2

'this doesn’t work - returns 0 rows from Sotred Proc
rs.Open strProc, gAcn, adOpenDynamic, adLockOptimistic

'this works, returns all rows
rs.Open strProc, gAcn, adOpenKeyset, adLockOptimistic

gAcn is my ADODB connection to MS SQL Server using a trusted connection

Some recordsets (Stored Procs) do work with adOpenDynamic but some 'bomb' out.  Had a few hours playing around and adOpenKeyset seemed to work every time

If I create a DSN and run a "Pass-through query" with the exact same query (strProc), it returns everything, every time

I am an admin on my PC with the necessary privileges to run the stored proc

I can return all the data (bind it to a datagrid) if I do the same thing with ADO .Net (using VB .Net)

I'm lost… Any insight, some SP or hot fix I'm missing, or am i just an idiot.

PS. the exact same code works on another PC running Win 2k, but not using a trusted conneciton (the username and password way)


Dave :-)
0
Comment
Question by:flavo
  • 34
  • 26
  • 6
66 Comments
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12693654
Hi Dave,

what is the cursorlocation of your global connection object?

Alan
0
 
LVL 34

Author Comment

by:flavo
ID: 12693662
i used both client and server..  hmm..... or did i test server with adOpenDynamic

Im no ADO "guru" infact, i NEVER use it... Why would that cause a problem  (forgive my negligence )
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12693681
Ive seen similar thing before, and from memory it was to do with the cursorlocation.
Not sure you can change the cursorlocation once the connection object has been opened.
I usually create a global connect string, then instantiate a new connection object using the global connection string wherever I need a connection.

  But you can try this see  if it has any affect.


  Dim objCmd  As ADODB.Command
  Dim objRs As ADODB.Recordset
 
  Set objRs = New ADODB.Recordset
  With objRs
    .CursorType = adOpenDynamic
    .CursorLocation = adUseServer
  End With
 
  Set objCmd = New ADODB.Command
 
  With objCmd
    .ActiveConnection = gAcn
    .CommandType = adCmdStoredProc
    .CommandText = strProc
    Set objRs  = .Execute
    ' note sql connections often return -1 for the recordcount no matter how many records are returned.
  End With


Alan




0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 34

Author Comment

by:flavo
ID: 12693693
hmm... Well im just about to head home (and face peak hour traffic)
Ill give it a crank when i get home (maybe 30mins or so)

Cheers Alan!

Dave :-)
0
 
LVL 34

Author Comment

by:flavo
ID: 12694053
hmmm just did some more tests..

If i use adUseClient it works, if i use adUseServer it bombs out...(with adOpenDynamic)

hmmm... looks like i have to do some light reading tonight..
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12695209
Ping!

you back on deck now Dave?
0
 
LVL 34

Author Comment

by:flavo
ID: 12695227
Sure am.. stupid wireless keeps going out.. only about 10m away from the router, moved back into the lounge room.. may as well be using a cord still :-(
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12695233
have you set up encryption yet?
0
 
LVL 34

Author Comment

by:flavo
ID: 12695244
i havent done anything, it was a big enough strugle to get it up..  really should do that hey.... maybe one of the grannies in the building could be hijacking my pc...
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12695259
I've no doubt about it - LOL

can you browse into your router

http://192.168.1.1/   

or whatever the starting address is set to, often 192.168.1.100

Alan
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12695268
re: this q
  are you using JET mdb/adp?
0
 
LVL 34

Author Comment

by:flavo
ID: 12695294
mdb

Just lost connection again (room mate is on cordless phone, maybe thats it??) back to being cable'ly joe
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12695311
You can find the address of your router by using IPCONFIG at a command prompt

C:\Documents and Settings\Alan>ipconfig

Windows IP Configuration


Ethernet adapter Local Area Connection:

        Connection-specific DNS Suffix  . : internode.on.net
        IP Address. . . . . . . . . . . . : 192.168.1.102
        Subnet Mask . . . . . . . . . . . : 255.255.255.0
        Default Gateway . . . . . . . . . : 192.168.1.1                   '<--Address

Most routers have a default  page at this address, easy setup
0
 
LVL 34

Author Comment

by:flavo
ID: 12695315
There is a firewall, but not sure what i can / cant block.. my room mate didnt have to do anything to connect to the internet after i spent ages setting it up on my pc, he just browsed freely... cant seem to see each others pcs, not too fussed if the old lady down stairs gets free internet.. good luck to her...
0
 
LVL 34

Author Comment

by:flavo
ID: 12695319
yeah this is it -> 192.168.1.1  had to use it to set it up.. using IE to goto it didnt work for some reason.. ended up using start -> run, worked a treat..
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12695351
might be set up as https://192.168.1.1
0
 
LVL 34

Author Comment

by:flavo
ID: 12695367
nope.. wouldnt i need to be connected to the internet to get a SSL for that to work??
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 2000 total points
ID: 12695388
Anyway Dave, I think the problem you are having with the dynamic cursor thingy, getting technical now huh, is this:

JET cannot instantiate a serverside cursor, no way, caint be done.
You can set one but it is automatically converted, I read that on msdn somewhere, wish I saved the link.
On the other hand stored procs are always executed serverside, so there seems to be some ambiguity.
ADO faithfully trying to return a serverside cursor but JET dilligently converting it.

I always use keyset, seems less problematic.

Alan
0
 
LVL 34

Author Comment

by:flavo
ID: 12695426
What do you do about new records??
0
 
LVL 34

Author Comment

by:flavo
ID: 12695437
>> You can set one but it is automatically converted, I read that on msdn somewhere, wish I saved the link.

Hmmm.. so you're saying that Jet will "change" adUseServer  to adUseClient?

What's the difference anyway?
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12695455
ping...
0
 
LVL 34

Author Comment

by:flavo
ID: 12695465
keeping me on my toes hey...
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12695479
sorry mate, just fartin around ":0)
0
 
LVL 34

Author Comment

by:flavo
ID: 12695488
its fine.. need a good laugh to stay awake at this crazzy hour..

Have to be up for my "real" job in 6 hrs..
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12695509
yes I have to deliver a job at  9:30 am Colorado time  - 2hrs and 20 mins from now. hmmm...
0
 
LVL 34

Author Comment

by:flavo
ID: 12695537
LOL!

And so the fun continues..

Oh well, i may as well leave you to it mate, im getting a little to tired for my liking... oh what the heck, one more XXXX
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12695571
the q on global MDW looks like fun, not.
0
 
LVL 34

Author Comment

by:flavo
ID: 12695590
I have NEVER touched mdw or any security in Access.. seems like a waste of time to me..
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12695613
here here!
  nothing but trouble, that and dns' and replication objects
0
 
LVL 34

Author Comment

by:flavo
ID: 12695628
replication!

Had a look, scared me, never went back.. seems like WAY to much trouble.. Plus i usually use 97, hence my 0 knowledge of ADO :-)
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12695676
Just reading this from: http://www.devhood.com/tutorials/tutorial_details.aspx?tutorial_id=624&printer=t
its C++ code but the reading is good


MSDN Online Reference


CursorLocation Property
Indicates the location of the cursor service.
Settings And Return Values
Sets or returns a Long value that can be set to one of the CursorLocationEnum values.
Remarks
This property allows you to choose between various cursor libraries accessible to the provider. Usually, you can choose between using a client-side cursor library or one that is located on the server.
This property setting affects connections established only after the property has been set. Changing the CursorLocation property has no effect on existing connections.
Cursors returned by the Execute method inherit this setting. Recordset objects will automatically inherit this setting from their associated connections.
This property is read/write on a Connection or a closed Recordset, and read-only on an open Recordset.
Remote Data Service Usage When used on a client-side Recordset or Connection object, the CursorLocation property can only be set to adUseClient.

MSDN Online Reference


Uses client-side cursors supplied by a local cursor library. Local cursor services often will allow many features that driver-supplied cursors may not, so using this setting may provide an advantage with respect to features that will be enabled.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 12695774
u guys are up late

Alan, hows the contracting business
Dave, you know whereabouts your gonna be in England?

Some terrible stuff I received recently


A BLOKE and his girlfriend were making love in the back of his van when
all of a sudden she shouted at him to whip her.
"Find something to whip me with now! If you do it will be the best sex
you have ever had" she screamed.
So he opened the window, snapped off the aerial and started whipping
her
with it.
Three weeks later she went to the doctor and asked him to take a look
at
some infected cuts that she had on her back.
The doctor said: "These are really badly infected.  I bet you got them
making wild passionate love didn't you?''
"Yes" she replied, ''But how did you know that?'' she asked.
"Well, this is the worst case of van aerial disease I have ever seen'.'
---------------------------------

I SAW six men kicking and punching the mother-in-law.
My neighbour said: "Are you going to help?"
"No" I said, "six should be enough."
---------------------------------

DID you hear about the dyslexic pimp? He bought a warehouse.
---------------------------------

THREE old Ladies named Gertrude, Maude, and Tilly were sitting on a
park
bench having a quiet conversation when a flasher approached from across
the
park.
The man came up to the ladies, stood right in front of them, opened his
trench coat and exposed himself.
Gertrude immediately had a stroke.
And then Maude also had a stroke.
But Tilly, being older and feebler, bless her heart, couldn't reach
that
far.
---------------------------------
CLUBBERS in Yorkshire have taken to using dental syringes to inject
Ecstasy directly into their mouths.
This dangerous practice is known as "E by gum".
---------------------------------

A CHAP walks into a pub and orders himself a beer.
He notices that Vincent Van Gogh is sitting on the next stall, and asks
him if he wants a beer.
"No thanks," replied Vincent, "I've got one ear."
---------------------------------
WHY did the jelly baby go to school?
Because he wanted to be a smarty.
---------------------------------

A LADY walked into a drug store and told the pharmacist she needed some
cyanide. The pharmacist said: "Why in the world do you need cyanide?"
The lady then explained she needed it to poison her husband. The
pharmacist's eyes got big and he said: "Lord have mercy, I can't give
you
cyanide to kill your husband!
"That's against the law! They'll throw both of us in jail and I'll lose
my
license."
Then the lady reached into her purse and pulled out a picture of her
husband in bed with the pharmacist's wife and handed it to pharmacist.
The pharmacist looked at the picture and replied: "Well now, you didn't
tell me you had a prescription."
0
 
LVL 34

Author Comment

by:flavo
ID: 12695775
There's way to many -> for my liking in that code :-)

Im gonig to hit the hay Alan.

Good luck with your project mate!

Dave :-)
0
 
LVL 34

Author Comment

by:flavo
ID: 12695807
>> Dave, you know whereabouts your gonna be in England?

Where ever i get work
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12695845
Love the van aerial one - ROTFLMAO
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12695875
did you hear about the irishman with lumps all over his head?
0
 
LVL 34

Author Comment

by:flavo
ID: 12695887
Yes, you are one sick puppy Rock!

Now i really better go...

ohh.. still got beer... maybe a few more minutes..
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12695929
He tried to hang himself with a rubber-band
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12695941
You've inspired me dave, got a chilling VB from the fridge, mmm
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12695953
0
 
LVL 34

Author Comment

by:flavo
ID: 12695959
LOL!

Except for that VB stuff... yuck! :P

Got a deadline in 1 1/2 hours, lets open a beer ;-) I like your style!
0
 
LVL 34

Author Comment

by:flavo
ID: 12695963
<-- accidental bad link removed -->

Alan
PageEd(Databases)
0
 
LVL 34

Author Comment

by:flavo
ID: 12695967
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12695993
whoops! whoops?, I better edit that one for ya
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 12696015
Im at work, no beer, but eh, its my anniversary today, and birthday tomorrow
So drinking on a school nite, Im allowed to have a beer tonite and tomorrow


talking about anniversary's, that reminds me of the wife

How do you turn a fox into an elephant?
Marry it!

urm, thats bad!



ok, last lot, whilst your drinking your last beer

So I said to the Gym instructor "Can you teach me to do the splits?".
He said "How flexible are you?". I said "I can't make Tuesdays".

--

Do you know I've got a friend who's fallen in love with two school bags,
he's bisatchel.

--
So I said "Do you want a game of Darts?", he said "OK then",
I said "Nearest to bull starts".

He said "Baa", I said "Moo", he said "You're closest".

--

A polar bear walks into a bar and says to the bartender: "I'll have a gin......................................................
.....

.....

.....

.....

.....

.....

.....

.....

.....

.....and tonic."

The bartender asks: "What's with the big pause?"

The bear responds: "I dunno... I've always had them."

--

What fruit teases you a lot?
A ba na..na..na..na..na!

--

and Ive saved the best for last


One day God calls down to Noah and says, "Noah me old china, I wants you to make me a new Ark"

Noah replies, "Nay probs God, me old supreme being, anything you want after all you're the boss!"

But God interrupts, "Ah but there's a catch this time Noah, I want not just a couple of decks,........ I want 20 decks one on top of the other."

"20 DECKS!" screams Noah, "Well, ok big man, what ever you say,should I fill it up with all the animals just like last time?"

"Yep, that's right, well..... sort of right..... this time I just want you to fill it up with fish" God answers.

"Fish?" Queries Noah.

"Yep, fish........ well, to make it more specific Noah, I want Carp, wall to wall, floor to ceiling Carp!"

Noah looks to the skies, "OK God baby, let me get this right, you want a new Ark?"

"Check."

"With 20 decks, one on top of the other?"

"Check."

"And you want it full of Carp?"

"Check"

"Why?" asks the perplexed Noah, who was slowly but surely getting to the end of his tether!

"Dunno" says God, "I just always fancied a Multi-Story Carp Ark!"





good nite chaps, Ive still got about 4hrs to do before I can leave this place!


0
 
LVL 34

Author Comment

by:flavo
ID: 12696043
Good laugh to end the night (now early morning)

I think i may sleep in tomorrow :-)

Later
        z
       z
     z
    z
:-|
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12696048
CarpArk - thats terrible rock

see ya L8a mate
0
 
LVL 34

Author Comment

by:flavo
ID: 12696054
Thanks Alan.. now how did i forget the au and the extra x

Maybe i should get a job a telstra :-)
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12696119
Yes, it happens hey?
0
 
LVL 34

Author Comment

by:flavo
ID: 12701656
Thanks Alan,

You got me in the right direction :-)
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12701818
Hi Dave,

thanks for the points, that looks like the link I was thinking of.

Alan
0
 
LVL 34

Author Comment

by:flavo
ID: 12701851
500 points... its the least i could do mate :-)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 12705832
there is a lot of beer talk here, but how are you guys into drinking mexican stuff, in particular, tequilia

before I go into the main joke, I remember this from the Stranglers concert

what did the mexican fireman name his two sons?
hose and hose b

why did the mexican push his wife off the cliff?
tequilia!


ok, main joke


A Mexican is strolling down the street in Mexico City and kicks a bottle
lying in the street. Suddenly out of the bottle comes a Genie.  The Mexican
is stunned and the Genie says, "Hello master, I will grant you one wish,
anything you want."

The Mexican begins thinking, "Well, I really like drinking tequila."
Finally the Mexican says, "I wish to drink tequila whenever I want, so make
me pee tequila."

The Genie grants him his wish. When the Mexican gets home, he gets a glass
out of the cupboard and pees in it. He looks at the glass and it's clear.
Looks like tequila. Then he smells the liquid. Smells like tequila. So he
takes a taste and it is the best tequila he has ever tasted. The Mexican
yells to his wife,"Consuelo, Consuelo, come quickly!"

She comes running down the hall and the Mexican takes another glass out of
the cupboard  and fills it.  He tells her to drink it. It is tequila.
Consuelo is reluctant but goes ahead and takes a sip. It is the best tequila

she has ever tasted. The two drank and partied all night.

The next night the Mexican comes home from work and tells his wife to get
two glasses out of the cupboard. He proceeds to fill the two glasses.  The
result is the same. The tequila is excellent and the couple drink until the
sun comes up.

Finally Friday night comes and the Mexican comes home and tells his wife,
"Consuelo, grab one glass from the cupboard and we will drink Tequila."

His wife gets the glass from the cupboard and sets it on the table.  The
Mexican begins to fill the glass; and when he fills it, his wife asks him,
"But Pancho, why do we need only one glass?"

Pancho raises the glass and says, "Because tonight, Mi Amor, you drink from
the bottle."

Arriba!!!!
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12712697
one tequilia, two tequilia, three tequilia, floor!
0
 
LVL 34

Author Comment

by:flavo
ID: 12712757
That's about right...
hmm. i have a bottle of tequilia at home, maybe i wont have beers tonight for a change..

Ok,  party at Dave's house

Rocki, you better get on a plane 16hours ago (sorry for the late notice)
Alan, you may still be able to make it

:-)
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12713864
Hi Dave,
sorry mate I got a better offer tonight, 5'0" dark brown eyes, 35 years old, about 40kg.

":0)
0
 
LVL 34

Author Comment

by:flavo
ID: 12713995
35.. too old for me :-)

Have a good one mate!

Dave :-)
0
 
LVL 34

Author Comment

by:flavo
ID: 12714033
Alan,

What type of router do you use?  I have a linksys and it keeps dropping out, read all the posts on EE about, seems like its a common problem.. probably shouldnt have let my roommate buy it hey...
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 12714235
5" and zero inches, sounds good, better than tequilia. Not heard the one,two,...,floor one before. must remember that one.

Dave, real late notice, perhaps next time, when England take on the Aussies for the ashes, I was hoping the kiwi's beat the aussies, they then lose confidence, which gives England a great chance of perhaps winning two matches instead of the usual one!

Ive got a linksys router, it does drop connections occasionally but not that often. Not had real problems with it. The only place it loses connection is when Im in the back room, signal strength is very low there.

0
 
LVL 34

Author Comment

by:flavo
ID: 12714261
signal strength is mostly high (only at most 10m away), hasnt dropped out tonight.. {yet}.

I guess ill just get used to it.. hey, i used dial-up for the first 18 months i lived here, this ADSL is pure GOLD!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 12714295
I have a cable modem, use Broadband from my cable provider. Big big difference!
No download limits, so Im happily downloading stuff all day every day

I wonder if it makes a difference which router you have. I have WRT54G one, made for cable modems, not ADSL
0
 
LVL 34

Author Comment

by:flavo
ID: 12714398
i have a WAG54G - ADSL & Router in one, with a Belkin 54g card sticking out the side of my laptop
0
 
LVL 34

Author Comment

by:flavo
ID: 12714402
>> No download limits

As do i, 1.5Mbit connection, fast as i could find for residential
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 12714445
some of the ISP's here impose a download limit, bit naff really

your setup sounds good, Ive found a place to download dvd rips of bollywood movies, keeps the wife happy, havent found one for hollywood movies yet. File sharing stuff isnt so good with dvd rips
0
 
LVL 34

Author Comment

by:flavo
ID: 12714490
You can choose.. some have limits at like 3, 5 or 20 gigs in a month (speed cut back to something like 56k)
others have $20 for 200meg... thats WAY too low, id go through that with ee alone.

What are prices like over there.. i had a look at those "hotspot" providers (yes i know, it'll cost me much more) and it seemed to be about <pound>40</pound> a month.  I dont really want to install cable or get a land line for ADSL if im going to be there for only 4months, so it seemed like the way to go for me
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question