Dynamic Query


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

    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?


7/2/2007 10:00:46 PM
access 16762 articles. 3 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 55

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

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,


7/2/2007 10:36:26 PM

Similar Artilces:

service calendar in MS Dynamics 4.0
Does anyone knows how we can achieve the following requirement with MSCRM 4.0 In the service calendar in MS Dynamics 4.0 you are able to create new appointments and service activities. Appointments and Service Activities which are created will be present in the Service Calendar. To see all details of such a record[Subject(default Value)] you must open it. But by moving your mouse on the concerning appointment or service activity, a text-box-view appears with primary information. With appointments the content of the following fields is displayed: 'show time as', 'subject...

Controling queries displayed when clicking Load from Query button?
I want to be able to control the queries that are displayed when the Load from Query toolbar button is displayed. Can anyone tell me how the filter box list gets populated? Thanks. Paco ...

dynamic resource
Hi, Anybody know how to load e.g. bitmap into resource dynamicaly? The bitmap file will be downloaded by application itself , so it has to be somehow inserted into resource and displayed. thanks What do you mean by "into resoure dynamically"? Do you want to load a ..bmp file and display it? If yes, take a look at GDI+, which is by far the easiest way to achieve this. ---------- Ajay Kalra ajaykalra@yahoo.com I misspelled this, yes I'd like to load new bitmap on the fly. But resource file looks like is static. I use ATL/MFC applicatation. "Ajay Kalra" <aja...

Dynamic sum in Chart
Hello: So I've got this pivot chart that I've created, and it's great, EXCEPT when I select different categories to show up, I want to show a SUM of the categories, not the values of each category. I would appreciate any help/advice you may have. Thank you! Michelle In the pivot table you can select different thing like count and SUM etc. If you right click in the upper left vacant square and chose field settings, then select what you want to see in your data section. Misha wrote: > Hello: > > So I've got this pivot chart that I've created, and it's great...

Tab seq dynamically...
I have a button which added dynamically to a property sheet. Now for setting the tab sequence with this new button, Am using SetWindowPos() method. Now tab sequence is proper. THE PROBLEM IS Through the arrow keys, I could not navigate to the last button for which I called SetWindowPos. where as through tab keys I could go to the last control. I will appreciate ur help in this. DETAILED DESCRIPTION I have three buttons (OK,Cancel, Help)on the property sheet. I added a button "Default" to the property sheet and positioned between Cancel and Help. I set the tab sequence as ...

Error in query referring to Combo Box
I am very new to using VBA to build forms and am having a problem. I trying to embed a query within a public sub routine. The goal of this query is to use an alphanumeric code entered into a combo box on the form to retrieve an associated ID within a table. The bound column of the combo box is text. I have used the following code: Public Sub GetSturID(intSturID As Integer) Dim rsSturID As ADODB.Recordset Set rsSturID = New ADODB.Recordset rsSturID.Open "SELECT tblSturg.SturgID FROM tblSturg WHERE (tblSturg.PIT = '" & cmbPIT.Value & "')" _ &...

SharePoint Alternate Access Mapping to my Dynamics GP BP site
Hello, If I have posted this question in the wrong forum please let me know! I’m looking for some guidance on changing the Alternate Access Mapping for my Dynamics Business Portal site. So I can implement Single Sign On via ISA Server which is supported on one web listener. Present configuration: Default Zone: http://geneva:222 I would like to add: Internet Zone: http://dynamicsbp.proclub.com I presently have another SharePoint Collection (The Main Portal) http://geneva configured which listens on Port: 80. I tried to add the Alternate Access Mapping http://dynamicsbp.proclub.com...

dynamic charting #2
Hi I have to update a weekly spreadsheet With week numbers in column B x series and three y series percentages in G,H,I columns. Could someone please help I need these to update automatically. If someone can explain in detail I would appreciate 39 2097 2165 68 1770.7 97 96.86 118.43 40 2443.15 2201 -242.15 1548.93 97 111.00 157.73 41 2162.83 2259 96.17 1600.78 97 95.74 135.11 42 2163.08 2241 77.92 1545.1 97 96.52 140.00 43 2137.5 2271 133.5 1664.6 97 94.12 128.41 44 2160.67 2298 137.33 1455 97 94.02 148.50 Ta Brian ...

Need a method to Dynamicly change the X axis Scale on a chart. #2
I dynamically change the amount of data on a chart. This causes the X axis Scale to either to either show too few values or too many values ( a solid black line if too many). I would like a method to dynamically change the scale values in relation to the data span. Joe - Do you want to limit the number of points displayed? http://peltiertech.com/Excel/Charts/Dynamics.html http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246 Or do you want to link the axis scales to something you calculate in the sheet? http://peltiertech.com/Excel/Zips/SwitchXYAxes.zip - Jon ----...

Dynamically populate a drop down list
Hi, I'm trying to dynamically populate a drop down list in MS CRM (1.2) based on certain parameters. I suppose I could do it using a SQL stored procedure, but I would prefer to use standard/supported CRM customizations. Any assistance in this would be greatly appreciated. Thanks! -Dave- Dave, You can do it in 3.0 pretty easily based upon an example in the SDK... Here are some pretty pictures... http://blogs.msdn.com/midatlanticcrm/archive/2005/12/04/499868.aspx I have not tried it with 1.2, but it could probably work... Thanks Ben "Dave" <renor321@yahoo.com> wrot...

CRM Integration with MBS/Dynamics products
Hi, Anyone know anything about the integration between CRM 3.0 and ...say.... Dynamix AX/Axapta? I'm thinking sharing of customer data and such.... Jeppe Jespersen Denmark There is nothing available yet. You would need to build it. -- Matt Parks MVP - Microsoft CRM "Jeppe Jespersen" <jdj curly jdj dot dk> wrote in message news:OAfVImWLGHA.3276@TK2MSFTNGP09.phx.gbl... Hi, Anyone know anything about the integration between CRM 3.0 and ...say.... Dynamix AX/Axapta? I'm thinking sharing of customer data and such.... Jeppe Jespersen Denmark ...

Query to conditionally handle duplicates
I'm creating a Bill of Materials using a one-to-many relationship table between InternalPN and ExternalPN. So the query sometimes returns more than one record when an InternalPN is specified, because there are multiple External PN's. I would like to alert the user to this in my query by substituting the word "Multiple" for the ExternalPN. I can find multiples easily enough with the Query Wizard & so have created a separate query called QryPNMultiple. I'm trying to refer to that query in a dlookup statement in my BOM query. I've tried this: Expr1:...

Dynamics GP 10 and Dynamics AX 4.0
just a half year ago, our company has started to work with Dynamics AX 4.0. Now i am doing some programming in X++ and almost customizing reports. Accidently, i stumpled on Dynamics GP 10.0 and i am wondering what s the difference between AX and GP? What stands GP for? Maybe GP is to replace AX, and our company backs to wrong horse? Does GP 10 contains AX 4.0? I have learnt lot of X++ programming, would it be possible to using X++ in the GT 10 field, too? Great Plains (GP) is another ERP system that Microsoft acquired a few years ago when it acquired the company of the same name. GP ...

Suggestion: Make all find and queries case sensative or none We've discovered that the queries are not case sensative while the find functions (for example, the find function in the requisition vendor applet) tend to be case sensative. This creates a lot of confusion for the end users and a lot of "I can't find..." support calls. Consistency is a good thing, especially in a user interface. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree"...

Dynamic Range
Hi All, I have the following two dynamic ranges =OFFSET(PBR!$A$1,0,0,COUNTA(PBR!$A:$A),1) =OFFSET(PBR!$A$1,0,0,COUNTA(PBR!$A:$A),2) Note: there will be two ranges namecount & namelist The formula which would be using the above range is =IF(MAX(namecount)<ROW(1:1),"",VLOOKUP(ROW(1:1),namelist,2)) Big question!!!, can I join these ranges together in to one statement. if so than how ??? Thansks, Rashid Hi! If I follow what you want: Named range: List Refers to: =OFFSET(PBR!$A$1:$B$1,,,COUNTA(PBR!$A:$A)) Then: =IF(MAX(INDEX(List,,1))<ROW(1:1),"",VLOOKUP(R...

Business Card query
I wish to produce my own business cards for a self catering flat with details on one side and a picture on the other side. So far I have produced the details and the photograph in Word but now want to match the two sides so that when printed they coincide. I wondered if Publisher has this facility. Blair What is on the right on the front will have to be on the left for side two. Might do better with either two pages or two separate publications. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Blair" <Darrach@aol.co...

How to run a date based query for many months
Hello, I have one query based on a parameter "MyDate". I enter a full date ( dd/mm/yyyy french format but no matter ) and the query result is expressed by the month ( yyyy_mm) eg : i enter 25/12/2009 and the result is expressed in 2009_12 I have to run this query for n months before the entered date and 2 months after this date. Is it possible to get a SQL code for this ? Can you help me ? Thanks by anticipation. It would help if you posted the SQL of your query. Also, your requirement as stated is a bit vague. If you enter 25/12/2009 an...

dynamic menus
I want to be able to create a hierarchy of menus under the view menu. I don't know at design time how many items will be under each submenu. I have been able to access and add to the view menu itself. By I have not been able to add anything under that added menu. I know it shuold be a popup menu. but when I make it a popup, I get an assert when I try to open the view menu. Here's what I have so far (some experimentation still remains): m_pWndMenu = GetMenu(); //int count = m_pWndMenu->GetMenuItemCount(); //CString string; //m_pWndMenu->GetMenuString(2, string, MF_BY...

Dynamics Workflow windows in Dynamics
Hi there I have installed Dynamics Workflow for Dynamics version 10 successfully. I have created a simple Purchase Order workflow. I have clicked the Activate Workflow button in this company. However when the users capture a Purchase Order it does not have the additional workflow compnonents on the Purchase Order window e.g. Submit etc. I have logged out and back in again. What am I missing? Regards Robin The workflow functionality in GP 10 requires the deployment of MOSS 2007 (Enterprise Edition). Not a trivial thing. "Robinv" wrote: > Hi there > > I have ...

Querying Dates
Hello, I am looking for some advice on how to tackle querying some data. I have two tables, table one with around 10,000 records per month and columns that give me (for each record) a start date, start time, end date and end time. Table two details a percentage for each hour and day of the week (so 24 hours * 7 days = 168 records). This has three columns, the day of the week (Mon to Sun), the hour (00:00 to 23:00) and a percentage. What I want to do is sum the total of the percentage column in table 2 where the day of the week and the hour of the week fall within the st...

Creating a dynamic chart
I posted here a few days ago and was given some ideas on how to do this. However, I seem lost. I have a main worksheet which is updated from several worksheets by using code. On this worksheet is approximately 15-20 columns something like this. trimester year RW RW% WIW WIW% etc 1st 2001 80 89% 79 75% 2nd 2001 90 89% 84 87% 3rd 2001 87 88% 92 89% 1st 2002 88 88% 90 88% etc etc What I would like to do is create one chart and by selecting the title of the column from either a combo box or by using a bu...

Help with Query Code
Private Sub cmdVendor_Reviewer_Report_Click() Dim dbs As Database: Dim qdf As QueryDef: Dim strSQL As String Set dbs = db1 strSQL = "SELECT * FROM [Invoice Tracker] WHERE ((([Invoice Tracker].Vendor)=" & _ "Forms![Select Vendor and Reviewer]![cmbVendorName]" & ")AND ((" & _ "([Invoice Tracker].Market Reviewer)=" & "Forms![Select Vendor and Reviewer]![cmbMarketReviewer]" & "));" Set qdf = dbs.QueryDef("SecondQuarter", strSQL) executeQueryDef qdf End Sub I need help with...

Dynamic Information in Templates
I would like to create a template with textboxes whose contents would be filled with the Personal Information of the user that is creating the document. If I use Insert->Personal Information in the template, the same text stays no matter who opens. Does anyone know how to make it change for each user? ...

Hi, I am trying to update a field in a record of a Access Database (whose structure I can not modify). I got this error (translated from italian): "Data types do not match in the expression" = "Tipi di dati non corrispondenti nell'espressione criterio." The code is the following: (myField is a binary field as on Access 2007) 'determine new value Dim newValue() As Byte = ... (valid data) 'write new attributes Dim conn As OleDbConnection = myObj.getConnection Dim cmd As OleDbComman...

dynamic charts
Hi: I have a series of monthly reports within excel. Each of those reports has a raw data tab. The raw data goes from left to right and each column represents an additional month. The rows represent different metrics that are tracked over time. How can I create my charts so that when I add an additional months worth of data (ex: adding a column to the raw data table) the charts dynamically update with a rolling 13 month trend? I have seen other examples where the raw data is setup differently and can't format my data that way. Specifically, people had the months in each row and the ...