Dynamic Query

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.

Re: Dynamic Query

Ben (pillars4@sbcglobal.net) writes:
> 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."

ADOX is nothing I have experience of, but I found in MSDN under the Command
property in ADOX that it says:

An error will occur when getting and setting this property if the
provider does not support persisting commands.

Which provider are you using? How does your connection string look like?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Dynamic Query

Below is the connection string:

ODBC;DSN=YES2;DATABASE=YES100SQLC;




"Erland Sommarskog" wrote in message
news:Xns99621E8AFE47Yazorman@127.0.0.1...
> Ben (pillars4@sbcglobal.net) writes:
>> 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."
>
> ADOX is nothing I have experience of, but I found in MSDN under the
> Command
> property in ADOX that it says:
>
> An error will occur when getting and setting this property if the
> provider does not support persisting commands.
>
> Which provider are you using? How does your connection string look like?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Dynamic Query

Ben (pillars4@sbcglobal.net) writes:
> Below is the connection string:
>
> ODBC;DSN=YES2;DATABASE=YES100SQLC;

And what is in that DSN?

Particular which OLE DB provider do you use? I had a look in a book on
ADO, and it said that the only two providers to support ADOX are the
Jet provider and SQLOLEDB. The book is a bit old, but if ODBC means that
you are using MSDASQL, then we have the answer to your problem. Change
to use SQLOLEDB instead.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx