Parameter query won't work with an Array of Values

Hi - I'm using VB in an Access DB. I'm trying to open an ADOB.Recordset using
a Command Object by passing a Variant array. for some reason it works fine
when I pass only one value, but now when I'm trying to pass an array I get
this error:

Run-time error '3001';

Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

The parameter query takes 2 values of type Long. The only values I can pass
are of type Variant, I don't think this is the problem because when I use the
same query with only 1 parameter it works.

Here is my recordset Sub for 1 parameter:

Sub setRcdSetCmd(rcdSet As ADODB.Recordset, qry As String, par As Variant)

setCmd Cmd, qry

rcdSet.CursorType = adOpenStatic
rcdSet.LockType = adLockOptimistic

Set rcdSet = Cmd.Execute(Parameters:=par)

End Sub

Here is the recordset sub that I was trying to use for the array:

Sub setRcdSetCmdAry(rcdSet As ADODB.Recordset, qry As String,
ParamArray par() As Variant)

setCmd Cmd, qry

rcdSet.CursorType = adOpenStatic
rcdSet.LockType = adLockOptimistic

Set rcdSet = Cmd.Execute(Parameters:=par()) **********

End Sub

Here is my Command function;

Function setCmd(Cmd As ADODB.Command, qry As String) As ADODB.Command

With Cmd
.CommandText = qry
.CommandType = adCmdStoredProc
Set .ActiveConnection = setCnxn
End With

End Function

The line with the *'s is the one that throws the error

I've tried calling the setRcdSetCmdAry 2 ways:

setRcdSetCmdAry rcdSet, qry, 13, 1

and

dim param(2) as variant

param(0) = 13
param(1) = 1
setRcdSetCmdAry rcdSet, qry, param()

I've also tried using the setRcdSetCmdAry sub withouht the ParamArray. I'm
not sure what exactly it is that I'm doing wrong, when I run the query in
Access it works.

I also have one other question just if anyone happens to know....
One of my primary keys is of type long, I've been doing a lot of programming
all using type long and example data. But I've ran into a snag - half of the
values are regular numbers like 127985 and the other half have 0's in the
front like 027854. When I try to use the numbers with the 0's in front Access
trunks the 0 and gives me 27854 insted of 027854. Is there anything I can do
to preserve the 0 in front (and all of my hard work programming with type
long)?

Any help is appreciated
--
Piper

.

Relevant Pages

  • Re: Need Help in VBA Code
    ... Public Sub AssignNumber() ... Dim strSysNumber As String ... Dim varResult As Variant ... then call this Sub from a command button or an After Update ...
    (microsoft.public.access.formscoding)
  • Form wont hide
    ... I think it has something to do with screenupdating turned off, but that command comes after the hide command. ... Sub Face_Sheet ... Sub main(printall As Variant, officeloc As Variant) ... Dim Rng1, Rng2, col, i As Integer ...
    (microsoft.public.excel.programming)
  • Re: cell value swapping
    ... > dim v as variant ... >End Sub ... >> I have a region of cells that need to meet certain ... >> region) then clicking the command button will find those ...
    (microsoft.public.excel.programming)
  • Re: Code to delete/unlink Linked tables
    ... Public intLinkODBCTables As Variant, intLinkDB2Tables As Variant ... Public strLinkBackendDB As String, strLinkDSNname As String, strLinkLibName ... ' MsgBox "This database is in MDE format...I will delete/recreate ODBC ... Public Sub fncLinkDB2Table() ...
    (microsoft.public.access.modulesdaovba)
  • Re: Difference between Subs and Functions
    ... Robert Morley wrote: ... I didn't say anything about a Sub returning a value. ... The Variant part is the problem, not the Sub Function part. ... In Access it's sometimes necessary to declare a Sub as a Function so ...
    (microsoft.public.vb.general.discussion)