I would like to run a report that uses a stored procedure with parameters. Is there a way I can pass the parameters from the report to the stored procedure? I am NOT running it from a form.
I want to call the report from VBA code and pass it the parameters that are necessary to run the stored procedure. Any ideas?
I'm running a access 2000 adp front end working from a SQL2000 database. I've got a sproc P_insertsessions i can get this work from a cmd button on a form but i'm having problems setting the paramerters i want it to use controls on the form.
@contractid = contractid
@start_date = startdate
@end_date = enddate
@hours = duration
Can anyone help.
I know this isnt strictly an sql server problem but I am currently using an access data project as a front end to my sql server database. Trying to upsize from access 97.
On one of my forms I have two sub forms which take their parameters from the parent form. In the input parameters box of the sub form I currently have:
@param1 = forms![parent form name]![field name]
i want
@param1 = me.parent.[field name]
I know this works as i ran the code on a command buttton and it returned the correct values. Yet in the input parameters box doesnt work any suggestions?
I have code for calling stored procedure with parameters,which is as follows
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Dim IdValueToProcess As Long
IdValueToProcess = 221177 ' test data
Debug.Print (IdValueToProcess)
Set qdf = CurrentDb.CreateQueryDef("")
[Code] ....
And my stored procedure is
Code:
ALTER PROCEDURE [dbo].[spItemDesc]
@ItemNo varchar(200) ,
AS
BEGIN
set nocount on ;
select ProductDesc1,ProductDesc2 from ProductDatabase.dbo.tblProductInfo where ProductNumber = @ItemNo
END
The error comes on debug.print(rst!ProductDesc1)
And it says "Item not found in the collection"
I have a stored procedure created in SQL SERVER 2008r2
I have a form in access adp project with combo boxes, when I click the submit button I want the values chosen to be the parameters and the stored procedure called to generate a report
Is this possible .
Hi,
I need to write a stored procedure that sits between sql server and MS access. This procedure will make sure that the queries run in access against the data in sql server will not access data for more than two years back. This is so that the queries run against the sql server do not hang up the server for too long.
Thank you.
Hi All
Im having some Major issues with this problem, i only hope someone can help me!!
Firstly, let me set the scene. I have 1 Form, And 1 Subform within that form. The form gets the Main Data, the SubForm gets all the Related Data (in this case, its all the Items Of Equipment this client has) and then lists them in the subform. So it could return 1, or it could return 20 records.
What we want from this is to be able to record what the client thought of the piece of equipment, so there is a Option Group on the SubForm, which allows the user to choose 1 of 6 options saying whether it was useful or not and things.
BUT here lies the problem, if the SubForm has returned 20 records, i change the OptionGroup on record one, and every other 19 records become the same. if i change Record 2, record 1 and 3 to 19 are the same.
I want to be able to have a seperate choice for each record returned, but tis not working!!
The subform is populated by calling a SQL SP, i cant set the form to link directly to a view or SP because access doesnt allow me, by saying the recordset is not updatable!
I hope all that makes sense!!
Hello,
can we create a stored prodecure in Access 2000 and call it from VB. I've created in SQL Server with ASP but have no idea in Access. Can anyone please tell me how to do it. If so kindly specify some resources from where i can learn more related to this. thank you.
Not sure which forum this was under, but I figure Access might be it
I have a stored procedure already written which works fine, if I supply the criteria to it before or at manual execution. I want it to use a field on a form in an Access Data Project as it's criteria (as a form is built off the results of the procedure). I can't find any documentation on how to pass criteria to a stored procedure for use in SELECT WHERE statements.
Can somebody point me in the right direction? Here is my stored procedure:
spGetContact:
Code:CREATE PROCEDURE dbo.[spGetContact](@parHomePhone numeric)AS SELECT dbo.tblContactAddress.AddressLine1, dbo.tblContactAddress.AddressLine2, dbo.tblContactAddress.City, dbo.tblContactAddress.Zip, dbo.tblContactAddress.State, dbo.tblContactEmail.PrimaryEmail, dbo.tblContactEmail.SecondaryEmail, dbo.tblContactPhone.HomePhone, dbo.tblContactPhone.BusPhone, dbo.tblContactPhone.CellPhone, dbo.tblContact.ContactID, dbo.tblContact.FirstName, dbo.tblContact.LastName FROM dbo.tblContact INNER JOINdbo.tblContactAddress ON dbo.tblContact.ContactID = dbo.tblContactAddress.ContactID INNER JOINdbo.tblContactEmail ON dbo.tblContact.ContactID = dbo.tblContactEmail.ContactID INNER JOINdbo.tblContactPhone ON dbo.tblContact.ContactID = dbo.tblContactPhone.ContactIDWHERE (dbo.tblContactPhone.HomePhone = @parHomePhone)GO
When i execute this manually I get the dialog prompt to enter the value for @parHomePhone, which is what I want to automatically pull from txtPhone on the frmSearch form.
I have a MS SQL 2000 stored procedure that acepts a parameter and returns a recordset.
I want to run this Stored procedure from an Access 2000 report and use the recordset for the report.
I want to pass a field off of a form that is user entered to the stored procedure.
Is this possible. Any help is appreciated.
I can figure out how to attach the stored procedure to the report as the dataset but cannot seem to figure out how to pass the dynamic parameter to the Pass-Through Query.
Thanks in advance,
Knight.
I'm having an issue getting a return value from a stored procedure that I'm calling from VBA. This is what I have at the moment:
Code:
Dim strDate As String
Dim strWOStatus As String
Dim CurrentConnection As ADODB.Connection
Dim adoCMD As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim ParamReturn As ADODB.Parameter
[Code] .....
The problem I am having is this error:
Error: 424
Description: Object Required
The line of code it errors on is:
Code:
Set .Parameters("@PartsUSedMTD").Value = ParamReturn
And the value of ParamReturn is always Null after it hits the line before it.
So it seems like it's not really creating the parameter variable SQL Server needs to run
[Note: This pertains to an Access Data Project (ADP).]
I modified a stored procedure on SQL Server, and later discovered that my changes had disappeared. I'm wondering if there's any possiblity that my Access ADP might have been the culprit.
I would think the answer is no. When I open an ADP in design mode, I think of the the top three displayed categories (Tables, Queries and Diagrams) as "windows" (binoculars, whatever) onto SQL Server. My understanding is, ADPs do not directly store any data in these categories.
Since Access lumps stored procedures under "Queries" I would think they would be no exception. The one thing that makes me a bit suspicious is, when I look at the stored procedure from the Access side, it begins with "ALTER PROCEDURE", whereas SQL Server stores it as "CREATE PROCEDURE". I see why it makes sense to implement it this way, but it makes me wonder if Access is actually storing a local version of the script.
Any ideas?
Following concerns about someone accidentally deleting the access database we have been using to crunch performance numbers, I have successfully moved the data on to an SQL server
While the database works as it is, several of the queries are running extremely slow. I therefore decided to see if a stored procedure could run the number crunching on the server instead of passing the data back and forth all the time.
I have taken the series of queries and converted them into a stored procedure that runs too fast for me to blink while giving the same results as before.
The problem I face is that I can trigger the stored procedure from the server management studio manually while supplying the variables needed thus providing the data I need to export to excel in a table for this purpose.
What I want to do is to have a form in access supply the chosen variables (like I could before) and run the stored procedure at the click of a button as part of a series of other queries.
I have looked at pass-through queries but apparently they do not take kindly to variables unless they are hardcoded. The other solution would be to trigger it from VBA but I have not been able to find a solution I could get to work.
How to run a stored procedure on an SQL server from access while also giving it the variables it needs?
Stored procedure name: spNearMissCalculation
Variables:
@SelectedDate (date format) (taken from a form field)
@SelectedVessel (nvarchar(max) format) (taken from a form field)
@SelectedVesselGroup (nvarchar(max) format) (taken from a form field)
I have created a stored procedure parameter query and using access created a report that runs the procedure and creates a report based on a parameter entered:
1) I wanted to know if I can specify a default paramter so if I do not input it returns all records?
2) Can I create a stored procedure so it asks for month, for example if I had a createdDate field of data type datetime and wanted to return records for a specific month?
I am trying to call a stored procedure from access ,but it is giving me this runtime error :
Code:
2147217900
Syntax error or access voilation
I am doing this first time so i dont know about how to pass parameters (IN and OUT)..
My code is
Code:
Public Sub createDataToAnalyze()
Dim objConnection As New ADODB.Connection
Dim objCom As ADODB.Command
[Code].....
SQL stored procedure which is simple INSERT statement on a single table 'tblSOF'
Code:
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter values below.
[Code] ....
I am stumped with the following error.
Error: Msg 102, Level 15, State 1, Procedure InsertINTO_tblSOF_sp, Line 80 Incorrect syntax near ')'.
I come from a SQL Server background and am very familiar with store dprocedures and input parameteres.
Is there a way to use a similar method in Access. or example, passing parameters from a button on a form to a query, which can change depending on the input parameter? If so, how ?
Also, how much scope do you have in this for running SQL IF statements and such in the query? It seems that many Access queris can only have one statement at a time.
Hi All;
Any advice would be much appreciated….
I have a db of products which drives an ASP website, and includes links to images, which I have now stored locally (with the image path amended to point to the local images folder) along with the images folder containing all the images.
What I now need to do, is produce Access reports using these images, this I initially assumed would be a simple task, but hasn’t in practice worked out that simple!!
So, I now have local folder containing the images, and the products table with an Image field that stores the local path to the image – How can I now get the record and corresponding image to appear in my report? I’ve tried using an OLE field for the images, but this means updating each record manually (unless there is any way of updating the OLE field from the existing image path field??)
Am I missing something totally obvious here?
Thanks in anticipation!
I have just made a change to one of the forms by adding a button (by copying the only other button on the form) to cancel any changes and close the form. However, as soon as I added it I started getting the error message in the title. Please attachment LA Err1 for the full message. I also changed the caption on the other button on the form from "Close Form" to "Save && Close Form" this button is now giving the same error.
I have Compacted and repaired the DB on several occasions to no avail. I have deleted the procedures from the module and recreated them using the properties window - still get the error. I have deleted the buttons from the form and recreated the both via the object wizard and without it. Nothing I have tried has made any effect.
I've created a database where I have all the information for people like name, address, etc. Then there's a list box to choose what events they will be attending. I want to be able to run a report and see all the information for the people who are attending any specific event. Is there a way to create a form where I can click a button then it brings up the screen to choose the parameters of my report, then generates the report? Thanks!
View 4 Replies View RelatedI am trying to figure what code to use. What i am trying to accomplish is when the use clicks to open a report i want a form to open and make them choose from a combo box a "training activity". then the report will generate info for that activity only.
Any thoughts??
Thanks
Megan
I have a field in a table called reasons. Then I have a list of possible reasons in a drop down list for my form. I want to create a report that allows the user to select from a drop down of these reasons and do a query for only those records with that specific reason. I know you can ask that info be typed in by using brackets in the query but instead of typing the reason I want to be able to select from the drop down list to produce the report. Can this be done?
View 1 Replies View RelatedI created a report using wizard and altered the parameters to what I need on my report and saved it as a new autoformat. The autoformat did not retain the parameter adjustments I made...only the basic format...no size changes...grouping I added ...a seperator line between the groups and the bound items I deleted that I did not want were still there. All I want to do is to be able to use the report format I created numerous times as it is without me having to go in and make adjustments with each query I pull a report from. There are several different query's with different tables involved. To sum it up one report format to use multiple times....Help Please!
View 5 Replies View RelatedI have an SQL statement which is dynamically built on a form.
It can be truncated to read
[Colour] = 'Blue'
Or it can be left as a complete statement - SELECT * from tblOrders WHERE [Colour] = 'Blue'
The statements work when I open a form.
Is there a simple way to open a report (or query) with these parameters?
I have an report that uses name paramaters.this is the sql for the report
Code:
PARAMETERS [whatCompany] Text ( 255 );
SELECT tblInvoices.ClientCompany, tblInvoices_Details.Charge, Sum(tblInvoices_Details.Hours)
AS SumOfHours, tblInvoices.InvoiceID
FROM tblInvoices INNER JOIN tblInvoices_Details ON tblInvoices.InvoiceID = tblInvoices_Details.InvoiceID
GROUP BY tblInvoices.ClientCompany, tblInvoices_Details.Charge, tblInvoices.InvoiceID
HAVING (((tblInvoices.ClientCompany)=[whatCompany]));
How do I pass the paramaters to the report? I've tried several different ways but can't get it to work
Code:
Dim stdocname As String
Dim stLink As String
stdocname = "RptWithParm"
stLink = "ClientCompany = " & "'" & Me.lstCustomer & "'" 'Using the field name doesn't work
DoCmd.OpenReport stdocname, acViewReport, , stLink
'When I try to set the value of the paramater that doesn't work either
stLink = "[whatCompany] = " & "'" & Me.lstCustomer & "'" 'using the paramater name doesn't work
DoCmd.OpenReport stdocname, acViewReport, , stLink
I know I could use the value of the form in the criteria like this
Code:
HAVING (((tblInvoices.ClientCompany)=[Forms]![frmTesRptParm]![lstCustomer]));
If I use the list box as the criteria I want to be able to use reports in other than one place, plus there are over 80,000 records and it'll run faster if I set the criteria before the report opens instead of setting a filter after it opens to only show up to about 100.
I've made a report on a blank report template. I've dropped in 5 sub reports. I have entered into the criteria for each of the queries based on [forms]![ClientForm]!CleintID. to run the report. It wortks but I have to enter the ClientID 5 times. Usually, when I create a button to run the form from, it only asks for the CleintID once. The 5 reports are based on 3 separate queries.
View 14 Replies View Related