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:
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.
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.
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
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!
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.
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 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.
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
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
[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.
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?
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 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
I have a form displaying the contents of a table in a datasheet view. I want to click on a row and open up a new form that will use a field in the selected row as a parameter.
I know how to call the VBA procedure by setting the double click on field (Cant do it for click on any fields in the row?) and in the VBA was using:
strText = Me.[Product Reference].Value
to get the required value. However this only seems to work in single form view. If it is in datasheet view it gives an error and will not let you save the form.
I am guessing I am missing the row identifier in my VBA code.
The procedure call "Call RearrangeCoordinates(RearrangeCoords(), ReturnCoords())" return a message "Type mismatched: Array or user-defined type expected"Are there any views as to why the message is being displayed?
Private Sub CommandButton1_Click() Dim ReturnCoords As Variant Dim ........................................... ArrayLength = UBound(ReturnCoords)
On my form I have a combo box where the user can select the sex of a person. I had set the row source of the combo to a table, tblSex and this works fine. Now I want to programatically add the table entries ( simply male or female) to the row source, having set the combobox to value list. I cant see how to pass the combobox and tablename parameters and I am getting an error on form load "The expression you entered as the event property setting produced the following error: UserDefined type not defined" The calling procedure from frmPeople is
I think the way the parameters combobox and tablename are passed is incorrect. I want to write a robust procedure which I can use for each of the comboboxes on my form.
I have a table with a list of different government programs that products can take advantage of. Each of these programs has criteria such as "must use less than 1000W" or "lasts for 100 hours". Rather than have a column for each possible condition, I've created 3 fields that will accept any type of condition; Var1Condition (example: watts) , Var1Requirement (example: >=), and Var1Value (example: 50). I figured this would be the more efficient database design than to add 15-20 columns.
I then built a form that would where I could enter product attributes and would hopefully query my database and only return the programs for which the product would qualify. So I would have a field named "Watts" in which I would put the wattage of the product and then I would see which programs it would fall in.
In theory, it should be simple. I figured I could just find a way to combine my 3 criteria fields into one string "Forms!Search!watts >= 50" and then use that as a query filter, but I can't find a way to do it.
I am using Office/Access 2003. I have created a report that functions fine within access. The report is based on a query requesting the users Representative ID. It functions as expected. We then creayed a web page using the same query. Again this works quite well. The user clicks on the link - Access pops up the criteria dialog asking for the rep id. What our problem is that we are trying to determine where we can put the Rep ID in the page so it will automatically fill in the Criteria Dialog. We see no place in the access page where the criteria can passed. We have the criteria because they use it when they sign in to the sight. I can pass it as a hidden field, an application variable, or even a server variable but how do I get the web page to see it and stuff it into the criteria dialog.
Frustrated in Tucson! Thanks for your help in advance
I have recently started using queries to base my forms on. Even for basic forms i am using the query as the record source.
If for example i have a standard query with no criteria e.g. a employee form. How would i then use that query to create a report that filters for example a list of employees for a certain company?
Would i have to create a seperate query to base the report on? because the form when opened will always filter that criteria?
If this is the case, is there a way to keep a query set up with no criteria but when the object being used is opened some code will pass to the query the criteria e.g. a combo box on a form list all the companies and when one is selected it passes this to the query e.g. without the criteria already being in the query?
I have a combo box in a form which allows users to select a Client Group. One of the choices in the combo box is ALL.
I have some code in a STEP1 query that says SELECT IIf(Forms![Date Picker].[Client Group]="ALL","'SEDP' Or 'LD' Or 'MH'",Forms![Date Picker].[Client Group]) AS Expr1 FROM MPI_PERSON GROUP BY IIf(Forms![Date Picker].[Client Group]="ALL","'SEDP' Or 'LD' Or 'MH'",Forms![Date Picker].[Client Group]);
I have a STEP2 query to select all records on the MPI_PERSON table and do some slight processing.
Now, in a STEP3 query, I want to select all records from the STEP2 query where the field [Client Group] satisfies the STEP1 criteria. If the Client Group chosen in the Combo box is one of the existing categories i.e. SEDP or MD or LH, it works but where the ALL has been entered, it is returning no records in the final query.
I have tried pasting the results of the STEP1 query into the criteria box as a test - and in that case it does return all records I want. What do I need to do to get the criteria recognised in the QBE box - do I need to use Eval or something?
Here is the SQL for the final query SELECT [Find all Clients STEP 2].[Paris ID], [Find all Clients STEP 2].DOB, [Find all Clients STEP 2].[DOB Estimated], [Find all Clients STEP 2].Gender, [Find all Clients STEP 2].[Status From Date], [Find all Clients STEP 2].[Status To Date], [Find all Clients STEP 2].STS_MAIN, [Find all Clients STEP 2].Title, [Find all Clients STEP 2].Name, [Find all Clients STEP 2].NAM_FROM, [Find all Clients STEP 2].NAM_TO, [Find all Clients STEP 2].[Client Group], [Find all Clients STEP 1].Expr1, Len([Expr1]) AS Expr2 FROM [Find all Clients STEP 2], [Find all Clients STEP 1] GROUP BY [Find all Clients STEP 2].[Paris ID], [Find all Clients STEP 2].DOB, [Find all Clients STEP 2].[DOB Estimated], [Find all Clients STEP 2].Gender, [Find all Clients STEP 2].[Status From Date], [Find all Clients STEP 2].[Status To Date], [Find all Clients STEP 2].STS_MAIN, [Find all Clients STEP 2].Title, [Find all Clients STEP 2].Name, [Find all Clients STEP 2].NAM_FROM, [Find all Clients STEP 2].NAM_TO, [Find all Clients STEP 2].[Client Group], [Find all Clients STEP 1].Expr1, Len([Expr1]) HAVING ((([Find all Clients STEP 2].[Client Group])=[Find all Clients STEP 1].[Expr1]));
Hope there isn't too much "Social Services speak" in there - I can also strip down non-crucial fields if it makes it easier to follow what I'm on about.
i have a query in design view to which i'd like to pass a criteria value for one field... I have some routine in VBA which creates a string as below:
"SupplierName1" And "SupplierName2" And "SupplierName3"
I am passing this to a textbox on the form exactly as shown above.
I would now like to use this value as criteria input for the query. However, it seems that Access doesn't recognise this....if I write just SupplierName1 in the textbox, the query works fine but as soon as I put in the quotes or else more than one supplier name, it does not work...
any ideas how can i resolve this?
Just FYI, the string of names of suppliers is being generated through string manipulation from a listbox using a value list.
