This may be something everyone knows but me (seems like a lot of things like that) but since I spent a bit of time figuring out the problem I thought I would pass it on...
Spent a lot of time trying to figure out why the following wasn't working since it had worked a short time before in the same script.
ctxt = myREC2.Fields.Item(x).Properties("BASECOLUMNNAME").Value
ttxt = myREC2.Fields.Item(x).Properties("BASETABLENAME").Value
Kept thinking I had inadvertently changed something and wasn't seeing it. Finally asked the right question, "What was different than before?" The answer was the Open Method parameters. The first one allows for the interrogation of Properties. The second does not...actually it doesn't error out on everything...sometimes just returns a blank value which was annoying and misleading. Haven't sorted out what combinations work and which ones don't but thought I would pass on my hard-earned knowledge :)
myREC2.Open (SQLText, myConn, adOpenForwardOnly, adLockBatchOptimistic, adCmdText)
myREC2.Open (SQLText, myConn, adOpenStatic, adLockReadOnly, adCmdText)
Jim
Here is a link to the ADO documentation that explains those Open Method parameters meaning: http://www.w3schools.com/ado/met_rs_open.asp
Thanks...that is a useful link. Don't see anywhere it mentions why certain ones block Properties but I may have missed it....mainly just wanted to let people know of my experience so as to help them keep head-shaped holes out of their walls like mine have.
Jim
I thought it was good you brought that up. It is akin to RecordCount returning 0 with the wrong cursortype. My rule is always to use keyset,optimistic unless I have over a million rows. BatchOpimistic only for persisted recordsets.
That might solve a problem I encountered along that line some time back. I had to do a MoveLast to get the record count. May be related. If I run across that code again I'll experiment. Thanks.
Jim