Re: Calling SQL Stored Proc from Access (with Parameters)
From: Duane Hookom (duanehookom_at_NoSpamHotmail.com)
Date: 02/17/05
- Next message: rkp: "removing CRLF from a memo field"
- Previous message: ATanker62: "Same value multipal times displaying"
- In reply to: Jessard: "Re: Calling SQL Stored Proc from Access (with Parameters)"
- Next in thread: Malcolm Cook: "Re: Calling SQL Stored Proc from Access (with Parameters)"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 17 Feb 2005 11:21:53 -0600
You can put the code in the after update of your "form field". After the
text box (or combo box) is updated by the user, have the code change the SQL
property of the pass-through query. Assuming your pass-through is named
"qsptMyPT" and the text box/control is named "StatementNumber" then the code
in the after update event of the control "StatementNumber" would be:
Dim strSQL as String
strSQL = "exec spr_myReport " & Me.StatementNumber
CurrentDb.QueryDefs("qsptMyPT").SQL = strSQL
This would be in the form's code window.
-- Duane Hookom MS Access MVP -- "Jessard" <Jessard@discussions.microsoft.com> wrote in message news:AA9E5362-C47C-4396-BCCD-69E7A2382F04@microsoft.com... > Thanks again for your responses but i'm not quite there yet. > > This is the background. I have an access database and a SQL database. > The > access database has a heap of linked SQL tables through 'Linked Table > Manager' to the SQL database. I have now created a Pass-Through query in > access which, as far as I understand, can run a piece of SQL directly on > the > SQL database it links to. This works fine but I need to be able to change > the parameter that is in the Pass-Through query dynamically, ie. based on > the > value of a form field that is open. > > I have had good responses that sound like they will do what I want but I > need to know where to type the code that is given - considering I am using > an > access database. I have tried Duane's code in the query but that is > obviously not the right spot for it. It looks like VB code, I don't know > where to put it though > > Jesse > > > "Barry Jon" wrote: > >> Jesse, >> >> not sure what your connection method is. I believe, using ADO, that you >> could do something like this; >> >> Dim cmd As ADODB.Command >> >> Set cmd = New ADODB.Command >> cmd.ActiveConnection = [relevant connection] >> cmd.CommandType = adCmdStoredProc >> cmd.CommandText = "spr_my_report" >> >> cmd.Execute , Array([Parameter Value1], [Parameter Value2]...) >> >> Let me know if I am missing the mark or if this works for you :-) >> >> Regards >> >> Barry-Jon >> >> "Jessard" <Jessard@discussions.microsoft.com> wrote in message >> news:E10C43B1-8356-4A8A-99EA-51F5624BF3B5@microsoft.com... >> > Hi >> > >> > I am aware that one can run an SQL Server stored proc from Access and >> > return >> > records and even pass in parameters to the stored proc to get the >> > desired >> > result. At the moment I am doing this with a "Pass-through" query with >> > Hard-Coded parameters. >> > >> > What I want to do however is have the parameter that is passed in to >> > the >> > stored proc from access created dynamically from a form in Access. >> > >> > eg. >> > >> > exec spr_my_report [forms]![frmeomreports]![statementnumber] >> > >> > instead of >> > >> > exec spr_my_report 55 >> > >> > >> > Or equivalent, any ideas? >> > >> > Thanks, >> > Jesse >> >> >>
- Next message: rkp: "removing CRLF from a memo field"
- Previous message: ATanker62: "Same value multipal times displaying"
- In reply to: Jessard: "Re: Calling SQL Stored Proc from Access (with Parameters)"
- Next in thread: Malcolm Cook: "Re: Calling SQL Stored Proc from Access (with Parameters)"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
Loading
