Dynamic Query

[Ben]

Hi!

I am trying to dynamically modify my pass-through query containing a
procedure call with 2 parameters.

When I run my access app, I get this error: "Object or provider is not
capable of performing reuqested operation."

Below is my access code:

Dim varItem As Variant
Dim strSQL As String
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim strMyDate As String, dtMyDate As Date

dtMyDate = CDate([Forms]![ySalesHistory]![Start Date])
strMyDate = Format(dtMyDate, "yyyymmdd")

strSQL = "procCustomerSalesandPayments '" & strMyDate & "', '" &
[Forms]![ySalesHistory]![Customer Number] & "'"

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection


'= = > > >NOTE: THIS IS WHERE THE ERROR POPS OUT!
Set cmd = cat.Procedures("Ben_CustomerSalesandPayments").Command

cmd.CommandText = strSQL
Set cat.Procedures("Ben_CustomerSalesandPayments").Command = cmd

DoCmd.OpenReport stDocName, acViewPreview

Set cat = Nothing
Set cmd = Nothing

Can anyone help me out?


Thanks.

 

[krissco]

I'm not familiar with the ADOX.Catalog object, but it seems like you
created it and expect it to be populated. What I mean is that you
never set up your cat object yet you reference its Procedures
collection.

Are you able to enumerate the procedures within your cat object? My
guess is that there are 0 items within that collection. If that is the
case, then you are wanting to SET cat = (some existing catalog)
instead of new.

Good Luck,

-Kris