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").Com mand

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

DoCmd.OpenReport stDocName, acViewPreview

Set cat = Nothing
Set cmd = Nothing

Can anyone help me out?


Thanks.

Ben (pillars4 (AT) sbcglobal (DOT) net) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Below is the connection string:

ODBC;DSN=YES2;DATABASE=YES100SQLC;




"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Ben (pillars4 (AT) sbcglobal (DOT) net) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx