1

I google a lot and read all post here related to this issue but found nothing that could give an explanation or help me to resolve this issue. Following here, a function which work great when the "TableName" parameter is a base table but raise error when it is an ms access view (query). I found nothing yet that could explain this issue as many access query already refer to such views (queries) without issues.

Function DBDistinctCount(FieldName As String, tableName As String) As Long
Dim rs As Recordset, curDb As Database, strSql As String

    On Error GoTo ERR_Handling
    Set curDb = CurrentDb
    'strSql = "SELECT COUNT(PR.[" & FieldName & "]) AS CNT FROM (SELECT [" & FieldName & "] FROM " & TableName & " GROUP BY [" & FieldName & "]) AS PR;"
    strSql = "SELECT COUNT(PR." & FieldName & ") AS CNT FROM (SELECT " & FieldName & " FROM " & tableName & " GROUP BY " & FieldName & ") AS PR;"
    'strSql = "SELECT COUNT([" & FieldName & "]) AS CNT FROM (SELECT [" & FieldName & "] FROM [" & TableName & "] GROUP BY [" & FieldName & "]);"
    
    'Debug.Print result: SELECT COUNT(PR.ID_Projet) AS CNT FROM (SELECT ID_Projet FROM R_CompilationProjet GROUP BY ID_Projet) AS PR
    
'    Dim qdf As DAO.QueryDef
'    Set qdf = curDb.CreateQueryDef(vbNullString, strSql)
    
    Set rs = curDb.OpenRecordset(strSql)
'    Set rs = qdf.OpenRecordset(dbOpenSnapshot)
    
    DBDistinctCount = Nz(rs.Fields("CNT"), 0)
    
ERR_Handling:
    If Err.Number <> 0 Then
        Dim mess As String
        mess = "Erreur vba " & Err.Number & " : " & Err.Description
        On Error Resume Next
        Call DBHelper.AddLog(mess, "DBDistinctCount")
    End If
    
    If Not rs Is Nothing Then rs.Close
    Set rs = Nothing
    
    If Not curDb Is Nothing Then curDb.Close
    Set curDb= Nothing
    
End Function

As you can see, I messed up the function a bit in order to find out what could be wrong. I even tried to use a querydef with the same result. I should mention that I've tried to put the resulting sql string itself inside an access query to see exactly the expected result when I ran the query. Any advice would be greatly appreciated.

Boisleduc
  • 21
  • 4
  • 3
    Most probably: your base query `R_CompilationProjet` refers to **form controls** for parameters. This doesn't work with DB.OpenRecordset, see https://stackoverflow.com/questions/49509615/how-do-i-use-parameters-in-vba-in-the-different-contexts-in-microsoft-access – Andre Feb 28 '21 at 19:42
  • You're right @Andre. I had a closer look to that query to see that it was using form parameters. I will have to find an alternative to that annoying issue. Thank you very much to bright my light. – Boisleduc Feb 28 '21 at 20:37
  • 1
    I've written a small Q&A for how to open recordsets using form-based parameters or UDFs, [here](https://stackoverflow.com/q/66414147/7296893). This could save you from having to fill in all those form parameters the query relies on manually, but is a bit hacky, so I'd probably manually fill in those parameters anyway if the query will not change. – Erik A Feb 28 '21 at 21:49

0 Answers0