Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Unanswered: ADOdB.Recordset problem

    Hello,

    I have following code:

    Code:
    Private Sub cmbAddNewHW_AfterUpdate()
        Dim strSQL As String
        Dim rsLastLabel As New ADODB.Recordset
        
        strSQL = "SELECT HW_Label FROM qryHW_AllHW_GeneralSpecs WHERE HW_Label Like 'LT*'"
    
        rsLastLabel.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
        
        Debug.Print rsLastLabel.RecordCount
        
        Do While Not rsLastLabel.EOF
            Debug.Print rsLastLabel.Fields(0)
            rsLastLabel.MoveNext
        Loop
        
    End Sub
    When I run the strSQL in access itselfs, it returns 25 rows.
    When I debug.print the recordcount of the recordset it gives 0 records.
    This also means that my code wil not get into the do while-loop.

    How is it possible access gives me the exact number of records and I can't get them in my recordset?

    By the way: In a previous db I made it all worked fine, in this one not. Is it perhaps I used Access XP earlier and 2003 now?

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by artemide
    Hello,

    I have following code:

    Code:
    Private Sub cmbAddNewHW_AfterUpdate()
        Dim strSQL As String
        Dim rsLastLabel As New ADODB.Recordset
        
        strSQL = "SELECT HW_Label FROM qryHW_AllHW_GeneralSpecs WHERE HW_Label Like 'LT*'"
    
        rsLastLabel.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
        
        Debug.Print rsLastLabel.RecordCount
        
        Do While Not rsLastLabel.EOF
            Debug.Print rsLastLabel.Fields(0)
            rsLastLabel.MoveNext
        Loop
        
    End Sub
    When I run the strSQL in access itselfs, it returns 25 rows.
    When I debug.print the recordcount of the recordset it gives 0 records.
    This also means that my code wil not get into the do while-loop.

    How is it possible access gives me the exact number of records and I can't get them in my recordset?

    By the way: In a previous db I made it all worked fine, in this one not. Is it perhaps I used Access XP earlier and 2003 now?

    Thanks
    Okay ... 1st off: Your recordset is by default a serverside recordset. RecordCount is not applicable with serverside recordsets (Hint: Read the Docs ...) With ADO you NEED to move to the 1st record explicitly with a MoveFirst call unlike DAO (which places you on the 1st record). Also you need to test for either BOF or EOF to determine if you have a result set.
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Quote Originally Posted by M Owen
    Okay ... 1st off: Your recordset is by default a serverside recordset. RecordCount is not applicable with serverside recordsets (Hint: Read the Docs ...) With ADO you NEED to move to the 1st record explicitly with a MoveFirst call unlike DAO (which places you on the 1st record). Also you need to test for either BOF or EOF to determine if you have a result set.
    Is there a difference between Access XP and 2003 concerning this issue, because I used the same syntax in an earlier DB (made in XP) and got no problems.

    I did an rsLastLabel.MoveFirst before checking on EOF, but it gave me an error. "Either BOF or EOF is true, or the current record has been deleted. ..."
    Last edited by artemide; 09-22-04 at 09:07.

  4. #4
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    I got the solution --> in my strSQL I replaced the wildcard '*' by '%'.
    Is there anyone out there who can give me a descent explanation why sometimes I need to use a '*' and sometimes I need to use a '%'?

    Thanx

  5. #5
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Thats the diff in SQL version
    * is Access SQL
    % is ORACLE SQL

    Greetz

    The Mailman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •