Open an ADO recordset with cmd syntax problem

[AkAlan via AccessMonster.com]

I'm trying to open an ADO recordset using the following method but keep
getting a syntax error when I get to the Set rst line. None of the examples I
have seen on how to perform this deal with passing a date to the sp.
Thanks for any help.

datCurStart = "2/1/2006"
cmd.CommandText = pr_EAR_Records
Set rst = cmd.Execute(Parameters:="'" & datCurStart & "'", options:
=adCmdStoredProc)

 

[Sylvain Lafontaine]

Try converting to a Date variable or use an Array of variants:

cmd.CommandText = pr_EAR_Records
cmd.CommandType = adCmdStoredProc
Set rst = cmd.Execute( , Array (CDate ("2/1/2006")))

It is also possible that the following may work:
...
Set rst = cmd.Execute( , Array ("2/1/2006"))

Using the Parameters argument is not a good idea because you cannot have a
return value or output parameters; so here an exemple that should work for
you and that will be more versatil:

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "dbo.MyStoredProcedure"

' Could also be a Variant, maybe a string:
Dim date1 as Date
date1 = CDate ("2/1/2006")

cmd.Parameters.Append cmd.CreateParameter("@Date1", _
adDBTimeStamp, adParamInput, , date1)

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open cmd, , adOpenStatic, adLockReadOnly

 

[AkAlan via AccessMonster.com]

Worked like a champ, thanks very much!!!!!

Try converting to a Date variable or use an Array of variants:

cmd.CommandText = pr_EAR_Records
cmd.CommandType = adCmdStoredProc
Set rst = cmd.Execute( , Array (CDate ("2/1/2006")))

It is also possible that the following may work:
...
Set rst = cmd.Execute( , Array ("2/1/2006"))

Using the Parameters argument is not a good idea because you cannot have a
return value or output parameters; so here an exemple that should work for
you and that will be more versatil:

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "dbo.MyStoredProcedure"

' Could also be a Variant, maybe a string:
Dim date1 as Date
date1 = CDate ("2/1/2006")

cmd.Parameters.Append cmd.CreateParameter("@Date1", _
adDBTimeStamp, adParamInput, , date1)

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open cmd, , adOpenStatic, adLockReadOnly

I'm trying to open an ADO recordset using the following method but keep
getting a syntax error when I get to the Set rst line. None of the

[quoted text clipped - 6 lines]

Set rst = cmd.Execute(Parameters:="'" & datCurStart & "'", options:
=adCmdStoredProc)