Results 1 to 4 of 4
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I use tipical ado con.
    I have my_var1="tablename" and my_var_2="my_filed_name"

    How to count only filled records in table = my_var1 in field = my_var_2

    Note:
    i can have in a fields a milions of records...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could do something like this:

    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strSQL As String
    Dim lngCount As Long

    Set cnn = ...
    strSQL = "SELECT Count(*) As Cnt FROM [" & my_var1 & "] WHERE [" & my_var_2 & ] Is Not Null"
    rst.Open strSQL, cnn, , adLockOptimistic, adCmdText
    lngCount = rst!Cnt
    rst.Close
    Set rst = Nothing

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    You could do something like this:

    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strSQL As String
    Dim lngCount As Long

    Set cnn = ...
    strSQL = "SELECT Count(*) As Cnt FROM [" & my_var1 & "] WHERE [" & my_var_2 & ] Is Not Null"
    rst.Open strSQL, cnn, , adLockOptimistic, adCmdText
    lngCount = rst!Cnt
    rst.Close
    Set rst = Nothing
    EXCELLENT!
    but is the fasted method?

    I want use the same code but with sql server (with ADO con) existis a fasted method for this type of database?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Using SELECT Count(*) is quite fast.
    For SQL Server, you could create a pass-through query, see Come creare una query SQL pass-through in Access.


Posting Permissions

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