I have an Access 2003 table that is linked to a DB2 table. When I look at the table in design view, I see a column called JGLAN defined as Number with FieldSize=Decimal, Precision=15, and Scale=0.
Since the Decimal parameter data type does not seem to be supported in the Access 2003 UI for queries, I tried setting it to Value as follows:
PARAMETERS [@CONO] Long, [@GLAN1] Value, [@GLAN2] Value;
SELECT CMSFIL_APPCHK.JGLAN, Count("*") AS [Count]
FROM CMSFIL_APPCHK
WHERE (((CMSFIL_APPCHK.JGLAN)=[@
GLAN1] Or (CMSFIL_APPCHK.JGLAN)=[@GL
AN2]) AND ((CMSFIL_APPCHK.JCONO)<>[@
CONO]))
GROUP BY CMSFIL_APPCHK.JGLAN
ORDER BY CMSFIL_APPCHK.JGLAN;
In my data access layer I define these paramters as decimal as shown below:
Public Function CMSFIL_APPCHK_SelectCountF
orNotCoNoG
LANs _
( _
ByVal vlngNotCoNo As Long, _
ByVal vstrGLAN1 As String, _
ByVal vstrGLAN2 As String _
) As ADODB.Recordset
On Error GoTo Proc_Err
'If gerh.OnErrorGoto0 Then On Error GoTo 0
Const strcProc As String = "CMSFIL_APPCHK_SelectCount
ForNotCoNo
GLANs"
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim ct As RMKCallTrace
Set ct = NewRMKCallTrace(strcProc, mstrcModule)
Set cmd = New ADODB.Command
With cmd
.CommandType = adCmdStoredProc
.CommandText = mcproc & strcProc
If mfSupportsReturnValue Then
.Parameters.Append .CreateParameter(, adInteger, adParamReturnValue)
End If
.Parameters.Append .CreateParameter(mcprm & "CONO", adInteger, adParamInput, , vlngNotCoNo)
'
Set prm = New ADODB.Parameter
prm.Name = mcprm & "GLAN1"
prm.Type = adDecimal
prm.Precision = 15
prm.NumericScale = 0
prm.Direction = adParamInput
prm.Value = CDec(vstrGLAN1)
.Parameters.Append prm
Set prm = Nothing
'
Set prm = New ADODB.Parameter
prm.Name = mcprm & "GLAN2"
prm.Type = adDecimal
prm.Precision = 15
prm.NumericScale = 0
prm.Direction = adParamInput
prm.Value = CDec(vstrGLAN1)
.Parameters.Append prm
Set prm = Nothing
'
Set CMSFIL_APPCHK_SelectCountF
orNotCoNoG
LANs = GetRecordset(cmd)
End With
Set cmd = Nothing
Proc_Exit:
Exit Function
Proc_Err:
VBA.Err.Raise Err.Number, mstrcModule & "." & strcProc & vbNewLine & Err.Source
End Function
The query never returns any reconds using the data access layer, but it does return records if I use the Access 2003 UI.
How should I define the query parameters in the QueryDel and in the data access lauer so this will work?