 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / May 2007ADO Recordset .OPEN returns no records
Running Access 2002 under WinXP, all updates (including Jet) are in place. This one is driving me nuts. The table has over 1000 records. But opening an ADO.RECORDSET on CurrentProject.Connection returns no records!! I have written code like this hundreds of times and never experienced this problem. I have doublechecked all the spelling, cursor types, table name, etc.. I can see records from forms or queries or opening the table directly, but not from my VB function. Here is a code snippet from my function. ----------------------------------------------------------------- Function GetNextSibling(SiblingName As String, EntityLevel As Integer) As String Dim NextSibling As String, Parent As String Dim cn As ADODB.Connection, rs As ADODB.Recordset Set cn = CurrentProject.Connection Set rs = New ADODB.Recordset rs.CursorType = adOpenDynamic rs.LockType = adLockOptimistic Parent = "" rs.Open "Product_AV2Glossary", cn rs.Find "[Architecture Entity ID] = """ & SiblingName & """" If Not rs.EOF Then Parent = Nz(rs("Parent")) Else MsgBox "The entity " & SiblingName & " was not found. Can't help" GetNextSibling = "" Exit Function End If If Parent = "" Then MsgBox "The entity " & SiblingName & "does not have a parent listed. Can't help" GetNextSibling = "" Exit Function End If ... (more code) ----------------------------------------------------------------------------- There are no records after the rs.Open statement. Opening the Locals Window shows rs.RecordCount to be -1. rs.EOF is False after the Open statement, and True after the Find statement. Any Ideas?  SignatureTDinDC Where is the connection? What is CurrentProject.Connection? Your Recordcount property will return either -1 or the actual count for a dynamic cursor, depending on the data source. Recordcount works great with Keyset or Static cursor... Anderson > Running Access 2002 under WinXP, all updates (including Jet) are in place. > [quoted text clipped - 45 lines] > > Any Ideas? Anderson, CurrentProject.Connection is the Connection property of the CurrentProject. The Help file on CurrentProject states that "The CurrentProject object refers to the project for the current Microsoft Access project (.adp) or Access database (.mdb)." It provides the simplest means of defining the connection without having to type the ConnectionString, etc. Looking at the Locals Window, under cn after executing the code "cn = CurrentProject.Connection" one finds that the ConnectionString property is : ConnectionString : "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=K:\NASEAF-docs\NVS\NVS_EA_DATA_MASTER.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database=K:\NVS\NVS\System.mdw" I tried both cursor types you mention and neither of them fixes the issue: they both return no records. SignatureTDinDC > Where is the connection? What is CurrentProject.Connection? > [quoted text clipped - 53 lines] > > > > Any Ideas? > Running Access 2002 under WinXP, all updates (including Jet) are in place. > [quoted text clipped - 38 lines] > > Any Ideas? Nz is an Access feature, unless it's a function of yours. I think that is not working in vb ADO. Nz is some kind of NonZeroLength Or Null function, right? Try using an If statement to check for ZeroLength or Null. /Henning > Running Access 2002 under WinXP, all updates (including Jet) are in place. > [quoted text clipped - 38 lines] > > Any Ideas? One more thing, don't rely on .Recordcount. It's working or not depends on driver, cursortype aso. /Henning Henning, Yes, I have used Access' NZ() function. It simply assures that the returned record be internpreted as a blank string if for some reason it is a Null. I've never had any problems with it in the ten years I've been using Access. As to your other post, if you look at the code and my original comments, I stated that the issue occurs before it ever gets to that line. I can say with surety that the NZ() is not the problem because I step through the code [F8 key], where VB runs in interpreted, not compiled mode. So it hasn't even considered the NZ() statement yet. And as to the CursorType, I've tried them all and none resolve the issue. Finally, as I also mentioned in my original post, all the XP and Windows drivers are up to date on my machine. Thanks, SignatureTDinDC > > Running Access 2002 under WinXP, all updates (including Jet) are in place. > > [quoted text clipped - 51 lines] > > /Henning rs.Find "[Architecture Entity ID] = """ & SiblingName & """" This command format brings up an error something like *Type matching error* What if you use only this, since SiblingName is already a string. rs.Find "[Architecture Entity ID] = " & SiblingName /Henning > Henning, > [quoted text clipped - 52 lines] > > > End If > > > ... (more code) ------------------------------------------------------------------------- > > ---- > > > There are no records after the rs.Open statement. Opening the Locals [quoted text clipped - 9 lines] > > > > /Henning Your CursorType is adOpenDynamic therefoer yor CursorLocation will be adServerSide. This means that every rs.movenext will fetch a record from your database. rs.Find applies to the recordset. Due to your Cursolocation your recordset is not populated with data. If you want your recordset to be populated with data you should use rs.CursorLocation = adOpenStatic and rs.CursorLocation =adClientSide. If you call rs.Open(...) your recordset will be populated with data. You can even close your connection (disconnected recordset) Set rs.ActiveConnection = Nothing rs.RecordCount should now be up to1000 Arjen S > Running Access 2002 under WinXP, all updates (including Jet) are in place. > [quoted text clipped - 45 lines] > > Any Ideas? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.