Open an ADO recordset with cmd syntax problem

  • Thread starter Thread starter AkAlan via AccessMonster.com
  • Start date Start date
A

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)
 
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
 
Worked like a champ, thanks very much!!!!!



Sylvain said:
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
Click to expand...
[quoted text clipped - 6 lines]
Set rst = cmd.Execute(Parameters:="'" & datCurStart & "'", options:
=adCmdStoredProc)
Click to expand...
Click to expand...
 
Back
Top