Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    83

    Unanswered: Always returning -1

    Code:
     
       Set Rec = New ADODB.Recordset
        Rec.Open "SELECT [hostname] from tblDevices WHERE [hostname] LIKE 'OCT2L32W*'",  CurrentProject.Connection
        MsgBox Rec.RecordCount
    What an I doing wrong here? A DCount with the same query returns 18 records.

    Any ideas?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Yes ... The ADO RecordSet cannot determine the # of rows in a ServerSide recordset ... Which is what you have by default ... You can perform a MoveLast MoveFirst combo to get the count or switch to a ClientSide recordset ... And to forstall the next question: Its the CursorLocation.
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'm not convinced that you need to use the square brackets [] within the SQL
    These force Acces to use the the underlying data/recordset values buthese are are relevant in the SQL
    I'd try
    Rec.Open "SELECT hostname from tblDevices WHERE hostname LIKE 'OCT2L32W*'", CurrentProject.Connection
    MsgBox Rec.RecordCount

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Square brackets are correct, they are used for fields' names containing spaces.
    Mr. Owen is correct.

  5. #5
    Join Date
    Mar 2004
    Posts
    83

    Cool

    Set Rec = New ADODB.Recordset
    Rec.CursorLocation = CursorLocationEnum.adUseClient
    Rec.Open "SELECT strHostNameNew from tblDevices WHERE strHostNameNew LIKE 'OCT2L32W*'", CurrentProject.Connection
    MsgBox Rec.RecordCount


    Slightly better, she's now returning 0. Just for the hell of it I copied my SQL statement into a query and it does indeed return 17 records.

    I put a watch on Rec and the Active connection is still adUseServer. Should I be making a new connection instead of CurrentProject.Connection?

    *EDIT*
    I changed the SQL statement in the code to read "SELECT * FROM tblDevices" (no where clause), and it returned 870 records...the full count. How does that make any sense?
    Last edited by High_D; 12-02-04 at 09:42.

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    IVe just tested and your problem lies here

    LIKE 'OCT2L32W*'",

    i know virtually nothing about ADO but it seems you have to use Standard SQL
    So Change your operator to this

    LIKE 'OCT2L32W%'",

    Dave

  7. #7
    Join Date
    Mar 2004
    Posts
    83
    Thanks dave, works like it should now.

    Does anyone know why that wildcard doest work?

Posting Permissions

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