Modules & VBA :: Calling Stored Procedure With Parameters

Dec 2, 2013

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"

View Replies


ADVERTISEMENT

Modules & VBA :: Calling Stored Procedure From Access Gives Run Time Error

Nov 27, 2013

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].....

View 1 Replies View Related

Set Parameters For Stored Procedure

May 31, 2005

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.

View 1 Replies View Related

Run A Report Using A Stored Procedure With Parameters

Jan 7, 2005

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?

View 4 Replies View Related

Access 2003 Stored Procedure Parameters

Jul 19, 2005

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?

View 1 Replies View Related

Modules & VBA :: Returning A Value From Stored Procedure

May 11, 2015

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

View 4 Replies View Related

Modules & VBA :: Run Stored Procedure Providing Variables From Form

Jan 15, 2015

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)

View 4 Replies View Related

Calling Procedure In Subform Error

Jun 9, 2006

I have a form, Salesperson, that has a subform Salesperson_SalesGraph. The SalesGraph subform is a pivot chart.

I'm having problems with trying to access items on the subform. The weirdest part is that it seems to work if i call the event from form_open, but doesn't work if i move the code to form_current. This won't work for me, as i need the function called whenever i change rows.

The error received is: You entered an expression that has an invalid reference to the property Form/Report.

First I had the code on the main form, accessing the chart object on the subform but was getting errors that i couldn't access the form property in the assignment, Set objChartSpace = Me.salesperson_salesgraph.Form.ChartSpace (i tried a number of differnet ways, with !'s, with Forms("SalesPerson_SalesGraph), etc.

Then i moved the code to the subform and now i'm just trying to call the publically declared sub with the line:

Forms!Salesperson!Salesperson_SalesGraph.UpdateLeg end
or
Me.Salesperson_SalesGraph.Form.UpdateLegend

The subform has a sub declared as:
Public Sub UpdateLegend()



Any thoughts?

View 3 Replies View Related

Pass A Parameter When Calling A Stored Query... Without The Prompt.

Jun 29, 2005

I this is a simplifed version a saved query called "qryTest"

PARAMETERS [gUserID] Long;
SELECT [Id],'Complaint' AS IncidentType FROM tblComplaints WHERE tblComplaints.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Complaint');

UNION ALL SELECT [Id],'Fall' AS IncidentType, FROM tblFalls tblFalls.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Fall');


how to i call the result from code or from the query manager and include the parameter so there is no prompt that comes up?


like
Me.ListNew.RowSource = "qryTest " & gUserID &";"
or
Select * from qryTest , 31 - where 31 is the value fed to the parameter


??? does anyone know? I know it can be called in APS like this:

Dim Conn
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConn ' strconn is my connection string
set rsP=createobject("adodb.recordset")
conn.qryTest 31
.....

I can't believe I can't do it from the query string!
Heeeellpppp!

View 3 Replies View Related

Stored Procedure

Jun 12, 2007

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.

View 2 Replies View Related

SQL Stored Procedure And A Form Recordset

Dec 19, 2005

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!!

View 1 Replies View Related

How To Creat Stored Procedure In Ms-Access

Oct 28, 2004

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.

View 5 Replies View Related

Passing Criteria To A Stored Procedure

Feb 22, 2005

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.

View 1 Replies View Related

Access 2000 And SQL Stored Procedure

Oct 6, 2003

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.

View 8 Replies View Related

My Stored Procedure Reverted Back -- Could Access Be The Culprit?

May 1, 2006

[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?

View 2 Replies View Related

Reports :: Stored Procedure Parameter Query - SQL Server

Jan 20, 2015

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?

View 1 Replies View Related

Queries :: SQL Stored Procedure - INSERT Statement On Single Table

Jan 6, 2014

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 ')'.

View 2 Replies View Related

General :: Stored Procedure And Parameter Pass Via Form To Generate Report

Jan 28, 2015

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 .

View 1 Replies View Related

Parameters / Stored Procedures

Aug 25, 2006

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.

View 7 Replies View Related

Modules & VBA :: Procedure Declaration Does Not Match Description Of Event Or Procedure

Jul 31, 2014

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.

View 5 Replies View Related

Modules & VBA :: Options For Calling A Subroutine

Jan 14, 2015

Imagine I have two subrountines:

Code:
Private Sub DoSomething1()
[Do Something]
End Sub

Private Sub DoSomething2()
[Do Something]
End Sub

And this Declaration:

Code:
Dim str As String
str = 1

Is it possible to refer to/call one of these subroutines using something like this:

Code:
("DoSomething" & str)

View 1 Replies View Related

Modules & VBA :: Calling Variables From Other Subs

Oct 29, 2014

I have been tasked to create a multi-keyword search form, however, my form isn't working right and only the first record of the table is opened.

Code:
Public Sub txtSearch_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant
Dim i As Integer
Dim IngLen As Long

[Code] .....

These are the codes that I am using for my search form. I have a feeling that I am not calling the variable from the after update portion the right way.

View 14 Replies View Related

Modules & VBA :: Calling Function From Sub Routine

Aug 10, 2013

I have 28 combo boxes on a form, which I want to insert data into the table as they are changed. Each one will pass the same sets of data just with different parameters which come from the form.

Rather than putting the same code to insert on each of the 28 combo boxes I thought it would be easier to create a function to do it and pass the parameters to it through a sub on the AfterUPdate event of the combo box.

I need to pass 4 parameters, if I only put 1 in there it works fine, but when I start putting more in it doesnt work and I get compile errors or syntax errors.

Sub routine:

Code:
Private Sub cboMonday1_AfterUpdate()
If Me.cboMonday1 = 1 Then
Me.cboMonday1.BackColor = vbGreen
Me.cboMonday1.ForeColor = vbBlack

[Code] .....

View 9 Replies View Related

Modules & VBA :: Calling A Function And Passing Variables

May 29, 2015

I have never tried passing variables while calling a function so I don't know what the heck I'm doing. I'll give a simplified example of what I'm trying to do. The second variable vRank is reporting properly but the first one vID gets "stuck" on whatever the first item in the listbox is.

Code:
Dim vrt As Variant
Dim upSQL As String
For vrt = 0 To Me.List1.ListCount - 1
If Me.List1.Selected(vrt) = True Then
Call ChangeUp(Me.List1.Column(0, vrt), Me.List1.Column(1, vrt))

[Code] ....

View 3 Replies View Related

Modules & VBA :: Calling On Current Event Of Another Form

Apr 20, 2015

I want to call the "On Current" event of another form.

I have a parent(mainform) form and then I have a subform(CurrentForm) and in that subform(CurrentForm) I have another subform(mysubtab01). From the mySubTab01, I want to call the On Current of the parent form. How can i do this?

This is an example of how I am setting the record source of the deepest subform where I want to call the parent on current from:

Forms(mainform)(CurrentForm)(mySubTab01).Form.Reco rdSource = "Select * from subQrytab03"

View 12 Replies View Related

Modules & VBA :: Calling Public Function To Backup / Compact Back End?

Dec 23, 2013

I have the following function that I found online. Unfortunately I can't remember where I got it since I've had it for a little while. Today, when I tried to actually put it to use it didn't work.

I'm calling it from a form as follows: CompactDB (tblHotword)

tblHotword is just a random table from the back end. My understanding of the function was that it would use that table to connect and get the file name of the back end.

Whenever I run it, Access pops up a window that says "Object required" and nothing else. It doesn't look like a standard error message popup. When I click 'OK', Access continues with the rest of the code as if nothing went wrong. The function doesn't run though.

Code:
Public Function CompactDB(TableName As String) As Boolean
On Error GoTo Err_CompactDB
Dim stFileName
DoCmd.Hourglass True
stFileName = db.TableDefs(TableName).Connect

[code]....

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved