COUNT not blank records in a single field

Topic

New Reply

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…

Reply | Quote

Replies

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote