How to pass params to Access2000

[MikkyThomeon]

I am trying to execute a stored proc in access. The body of the stored proc is this ...

PARAMETERS x DateTime, y DateTime;
SELECT [TRIHOVER 19072002 18% ACCOUNTS].[COMPANY HANDELING ACCOUNT] AS [Agent Code], Count([TRIHOVER 19072002 18% ACCOUNTS].[COMPANY HANDELING ACCOUNT]) AS [Files Allocated], Sum([TRIHOVER 19072002 18% ACCOUNTS].[HAND OVER AMOUNT]) AS [Allocated Value]
FROM [TRIHOVER 19072002 18% ACCOUNTS]
WHERE ((([TRIHOVER 19072002 18% ACCOUNTS].DATE) Between [x] And [y]))
GROUP BY [TRIHOVER 19072002 18% ACCOUNTS].[COMPANY HANDELING ACCOUNT];

From Access, when I do not use the parameters, the query works fine. I then include the Parameters line and try the following method of opening the recordset (The shortcut is not to use parameter objects and command objects)

Set rs = DataEnvironment1.Connection1.Execute("qryHandoverAlloc #2000-01-01#,#2002-01-01#")

The query executes but the recordcount is zero and both EOF and BOF return true. Does anyone know what I am doing wrong or why this problem happens?? PLEEEEZ. I will love anyone to death if they can help here!!

[swatty]

Ok i'll try and hope you won't love me.

To execute the string with parameters then you have to give the full string and change the values of x and y with your parameters

strSQL = "SELECT [TRIHOVER 19072002 18% ACCOUNTS].[COMPANY HANDELING ACCOUNT] AS [Agent Code], " & _
    "Count([TRIHOVER 19072002 18% ACCOUNTS].[COMPANY HANDELING ACCOUNT]) AS [Files Allocated], " & _
    "Sum([TRIHOVER 19072002 18% ACCOUNTS].[HAND OVER AMOUNT]) AS [Allocated Value] " & _
    "FROM [TRIHOVER 19072002 18% ACCOUNTS] WHERE ((([TRIHOVER 19072002 18% ACCOUNTS].DATE) Between " & _
    "#2000-01-01# AND #2002-01-01#)) GROUP BY [TRIHOVER 19072002 18% ACCOUNTS].[COMPANY HANDELING ACCOUNT]"
 
Set rs = DataEnvironment1.Connection1.Execute(strSQL)

[Desbaratizador]

With a ADODB.Command you can pass the parameters to the Execute method, example:

dim CMD as ADODB.Command
dim rs as ADODB.Recordset

set CMD = new ADODB.Command
with CMD
.ActiveConnection = CN
.CommandType = adCmdStoredProc
.CommandText = "XXX"
set rs = .Execute(, Array(Param1, Param2, ...))
end with

or create Parameter objects and append them to the Command object

dim P as ADODB.Parameter

Set P = New ADODB.Parameter
With P
.Name = "MyParameterName"
.Type = adDate
.Direction = adParamInput
.Value = MyDate
End With
CMD.Parameters.Append P
'Append parameter 2

set rs = CMD.Execute()

[MikkyThomeon]

Hi swatty

OK I have tried something else that gets closer to the problem. When I use my query IN ACCESS with params, the result is returned:

SELECT [TRIHOVER 19072002 18% ACCOUNTS].[COMPANY HANDELING ACCOUNT] AS [Agent Code], Count([TRIHOVER 19072002 18% ACCOUNTS].[COMPANY HANDELING ACCOUNT]) AS [Files Allocated], Sum([TRIHOVER 19072002 18% ACCOUNTS].[HAND OVER AMOUNT]) AS [Allocated Value]
FROM [TRIHOVER 19072002 18% ACCOUNTS]
WHERE ((([TRIHOVER 19072002 18% ACCOUNTS].DATE) Between [x] And [y]))
GROUP BY [TRIHOVER 19072002 18% ACCOUNTS].[COMPANY HANDELING ACCOUNT];

But when I use this (below) the result is no rows!

SELECT [TRIHOVER 19072002 18% ACCOUNTS].[COMPANY HANDELING ACCOUNT] AS [Agent Code], Count([TRIHOVER 19072002 18% ACCOUNTS].[COMPANY HANDELING ACCOUNT]) AS [Files Allocated], Sum([TRIHOVER 19072002 18% ACCOUNTS].[HAND OVER AMOUNT]) AS [Allocated Value]
FROM [TRIHOVER 19072002 18% ACCOUNTS]
WHERE ((([TRIHOVER 19072002 18% ACCOUNTS].DATE) Between #2000-01-01# AND #2002-01-01#))
GROUP BY [TRIHOVER 19072002 18% ACCOUNTS].[COMPANY HANDELING ACCOUNT];


In the first example Access prompts me for values and I enter them and then the query works. In the second part the query does not work. Any Ideas? I know about command objects but using all that jazz is not really needed. I prefer to connect using the shortcut method instead. But this prob is not really a VB thing as the same problem happens in Access...

[swatty]

My mistake i didn't look quite well at the params just copied them.


You better change the dates in mm/dd/yyyy format.
That shoulld work.

Try to change the between statement to
Between #01/01/2000# AND #01/01/2002#

[MikkyThomeon]

THANX A MIL Swatty!!

With your help and a bit of my own I came to the conclusion that Access is very temperamental when it comes to date params.

A few rules..

The date seperator must match the date seperator as defined in the regional settings in Control Panel

The date must be specified in Y2K compliant format (4 digit year)

The date must match the date format on the local machine

The following solved my prob (after about 4hrs!!! of struggling)

Set rs = DataEnvironment1.Connection1.Execute("qryHandoverAlloc 2000/01/01,2002/01/01")

Thanks so much for your help and the time you have taken to think of my situation. I have a control to convert large text files to CSV format. Interested?

[MikkyThomeon]

Dont use hashes in the line that passes the date params to the Access DB!!!.

[swatty]

You have to be carefull with dates and floating point numbers when you try to access of fill databases (tables)