Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    3

    Unanswered: Module in Access

    Hi,

    Im trying to write some VBA in a module in Access and was wondering if anyone knew how I can return the value from a
    Select Count(*) From tablename
    into a variable in the module

    Cheers

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    you could do it like this using ADO

    Code:
    Sub testCount()
        Dim cmd As ADODB.Command
        Dim rs As ADODB.Recordset
        
        Dim lngCount As Long
        
        Set cmd = New ADODB.Command
        Set rs = New ADODB.Recordset
        
        cmd.ActiveConnection = CurrentProject.Connection
        cmd.CommandText = "SELECT COUNT(*) FROM tblTest"
        Set rs = cmd.Execute
        
        If Not rs Is Nothing Then
            lngCount = rs.Fields(0)
        End If
        
        Debug.Print lngCount
        
        Set cmd = Nothing
        rs.Close
        Set rs = Nothing
        
    End Sub
    Dave

  3. #3
    Join Date
    Feb 2005
    Posts
    3

    Legend

    That works,

    Thanks loads

Posting Permissions

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