Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Location
    Minneapolis, MN
    Posts
    12

    Unanswered: Missing the obvious

    Good morning,

    I am attempting to open a recordset in VBA. I have used the same procedure countless times in the past, but it doesn't seem to be working today, and it's driving me nuts! Here's the code:



    Public Sub Test1()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sSQL As String
    Dim iCount As Integer

    sSQL = "SELECT * FROM tblCSAInfo"

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(sSQL)

    iCount = rs.RecordCount
    MsgBox "There are " & iCount & " records."

    rs.Close
    Set rs = Nothing
    Set db = Nothing

    End Sub

    The table in question, tblCSAInfo, has 368 records. The code listed above returns a value of 1 to iCount. Now, if I replace the sSQL statement with just "tblCSAInfo" I receive an accurate count.

    I know I'm missing something obvious, I just can't seem to figure out what it is. Adding to the frustration is that this method works in other modules.

    Thanks,
    Paul

  2. #2
    Join Date
    Apr 2004
    Location
    Minneapolis, MN
    Posts
    12

    Re: Missing the obvious

    Something's got to be goofy. I just wrote a quick query called "qryCSAInfo" and set it to retrieve all records. The query works fine.

    Then, in my code, I dimmed "qd" as "DAO.QueryDef" and changed my code to this:

    Set db = CurrentDb()
    Set qd = db.QueryDefs("qryCSAInfo")
    Set rs = qd.OpenRecordset

    Again, the iCount value became 1.

    Could there be some kind of SQL/VBA conflict?

  3. #3
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: Missing the obvious

    It type of LockType and CursorType are you using? Some recordsets need to move last to get the right record count i.e.

    rs.open
    rs.movelast
    msgbox rs.RecordCount

    Also how about trying the same with ADO instead of DAO?

    Dim cn as ADODB.Connection
    Dim rs as ADODB.Recordset

    Set cn = new ADODB.Connection

    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Test.mdb;Persist Security Info=False"

    cn.open

    set rs = new ADODB.recordset

    rs.actiiveconnection = cn

    rs.open "SELECT * FROM tblCSAInfo"

    rs.close
    cn.close

    set rs=nothing
    set cn = nothing
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  4. #4
    Join Date
    Apr 2004
    Location
    Minneapolis, MN
    Posts
    12
    Thanks for the reply. I inserted the following code before "iCount = rs.RecordCount":

    If rs.RecordCount > 0 Then
    rs.MoveLast
    rs.MoveFirst
    End If

    and the sub works.

    Now that I'm confident that I'm pulling the recordset, I am filtering it and building code to assign values to variables based on contents of the recordset. This part will be fun...

    Thanks for your help,
    Paul

Posting Permissions

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