Using ADODB recordset doesn't return a value when same query in Access does

Question

I am having a frustrating problem with EXCEL VBA using an ADODB recordset. I have a query which directly in Access works correctly but when I put it in the VBA the recordset always comes back null. I have other queries that work as expected but something about this type of query always fails to return a result in VBA even though it works in Access.

Below is the function responsible for executing the query. It is built to allow me to do counts, and in a similar function, sums on the ProjList table. The first style query is used where no year limiting flag is to be used (sYrIn = -1) and it works as expected.

The second style query is when the year in flag contains a value to limit the count or sum to a specific year. Our Project IDs are of the form X99-999 where the first position denotes a type of project, the next two digits signify the year the project opened and the last three digits are a unique incremented number which resets to 001 each year.

There are a number of lines here that are only for debugging feedback. Below the code I have included some of the debugging output.

I understand we are to ask a specific question, but I have four that are tightly related to this situation and code sample and do not think that four posts so tightly related are a good thing. My questions are about the second set of queries. They never work in VBA.
1) Am I reading the recordset incorrectly in that case?
2) Is there some limitation to the "complexity" of query that can be executed in this way?
3) Why does the first query return runs and the second doesn't?
4) Is there a better way to see the real query execution result?

Again all of the queries work in Access.
Note: the code as it appears has the year search hard coded since the actual code uses the current year and not enough data for 2020 exists for the result to be meaningful (0 is 0!)
Also there are two queries for the second type. One is commented out--it suffers the same failure in VBA and success in Access.

   Function GetDBProjCount(sCharIn As String, sFldIn As String, Optional sYrIn As Integer = -1) As Integer
   Dim iResult As Integer
   Dim sQryString As String
   Dim sSearchChar As String
   Dim pQryParam As Object
   Dim sParamValA As String
   Dim sParamValB As String
   Dim iParamLenA As Integer
   Dim iParamLenB As Integer

   iResult = 0
   Call MakeDBConnection
   Set pQryParam = CreateObject("ADODB.Parameter")
   If CInt(sYrIn) > 0 Then
      If ((CInt(sYrIn) > 10) And (CInt(sYrIn) < 50)) Then
         sYrIn = Right(sYrIn, 2)
      ElseIf ((CInt(sYrIn) > 2010) And (CInt(sYrIn) < 2050)) Then
         sYrIn = Right(sYrIn, 2)
      Else
         sYrIn = -1
      End If
   End If

   If sYrIn < 0 Then
      sQryString = "SELECT Count(*) FROM " & tblProjList & " WHERE Left(" & sFldIn & ", 1)=?;"
      sParamValA = sCharIn
      sParamValB = ""
      iParamLenA = 1
      iParamLenB = 1
   Else
      sQryString = "SELECT Count(*) FROM (Select * from [" & tblProjList & "] WHERE [" & garProjListFlds(4, 1) & "] Like ?) WHERE Left([" & sFldIn & "],1)=?;"
      'sQryString = "SELECT Count(*) FROM [" & tblProjList & "] WHERE ((Left([" & sFldIn & "], 1)= ? ) AND ([" & garProjListFlds(4, 1) & "] LIKE ?));"
      sParamValA = "*19-*"   'comment "'*" & CStr(sYrIn) & "-*'"
      sParamValB = sCharIn
      iParamLenA = 8
      iParamLenB = 1
   End If

   If QDebugMode Then Debug.Print "GetDBProjCountA: " & sQryString

   With goDBCmd
      .ActiveConnection = goDBConn
      .CommandText = sQryString
      .CommandType = adCmdText
      Set pQryParam = .CreateParameter("ParamA", adChar, , iParamLenA, sParamValA)
      .Parameters.append pQryParam
      If sYrIn > 0 Then
         Set pQryParam = .CreateParameter("ParamB", adChar, , iParamLenB, sParamValB)
         .Parameters.append pQryParam
      End If
      Set goDBRecSet = .Execute
   End With

   If QDebugMode Then Debug.Print ("GetDBProjCountB:  Parameters:   A: " & sParamValA & " B: " & sParamValB)
   Dim msg, fld
   For Each fld In goDBRecSet.Fields
      msg = msg & fld.Value & "|"
   Next
   If QDebugMode Then Debug.Print ("GetDBProjCountC:  Result: " & msg)

   GetDBProjCount = goDBRecSet.Fields(0)
   Call CloseDBConnection
End Function

Here is what I see in the immediate window:

GetDBProjCountA: SELECT Count(*) FROM ProjList WHERE Left(ProjArchiveFlag, 1)=?;
GetDBProjCountB:  Parameters:   A: O B: 
GetDBProjCountC:  Result: 45|
GetDBProjCountA: SELECT Count(*) FROM (Select * from [ProjList] WHERE [ProjCCID] Like ?) WHERE Left([ProjArchiveFlag],1)=?;
GetDBProjCountB:  Parameters:   A: *19-* B: O
GetDBProjCountC:  Result: 0|    (In Access this one returns 44)

The recordset is instantiated in the MakeDBConection sub as

Set goDBRecSet = New ADODB.Recordset
   goDBRecSet.ActiveConnection = goDBConn
   goDBRecSet.CursorLocation = adUseClient
   goDBRecSet.CursorType = adOpenStatic
   goDBRecSet.LockType = adLockPessimistic

   Set goDBCmd = New ADODB.Command

The connection string is: "Provider=Microsoft.ACE.OLEDB.12.0;" & " Data Source=" & DBPath

Erik A: THANK YOU!!! Please make that a solution and I will vote it up! That solved the problem with virtually no effort!!!! Hours lost searching and I never found a reference that said don't use the SQL standard '*' but rather '%'. THANK YOU!!! — apf2

Sola Oshinowo Sola Oshinowo · Accepted Answer · 2020-01-13T10:04:54

0

votes

Try using wildcard character % not asterik *

Using ADODB recordset doesn't return a value when same query in Access does

1 Answers