 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / October 2007execute stored procedure
could i get an example of how to call a sql stored proc that requires 4 input parms and returns 1? DAO or ADO or both. thanks much, mcnewsxp The easiest way is to use a pass-through query, and change the SQL to include the parameters. Let's assume the pass-through query is named qryPassthrough. Dim qdfPassthrough = DAO.QueryDef Dim rsCurr As DAO.Recordset Dim strSQL As String Set qdfPassthrough = CurrentDb().QueryDefs("qryPassthrough") strSQL = "EXEC MyStoredProc 'Parm1', 23453, '2007-10-28', 'Parm4'" qdfPassthrough.SQL = strSQL Set rsCurr = qdfPassthrough.Execute MsgBox "The stored procedure returned " & rsCurr.Fields(0)  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > could i get an example of how to call a sql stored proc that requires 4 > input parms and returns 1? > DAO or ADO or both. > thanks much, > mcnewsxp no connect string or DSN info needed? > The easiest way is to use a pass-through query, and change the SQL to > include the parameters. [quoted text clipped - 16 lines] >> thanks much, >> mcnewsxp > no connect string or DSN info needed? While in a sense yes and in a sense no. The idea here is that you build a simple pass-through query something like: select * from tblCustomers If you make a query to pass through, then you'll supply all of that connects information when you build that query. Thus Douglas is suggesting is to use an *existing* query that already has all that wonderful connection information set up correctly. Since the pass through query connection information is already set up, then the only thing left to do is provide the actual SQL. That SQL you provide can be virtually any command that the SQL server can receive as a command line prompt (a pass through query simply passes whatever text you sent to the server un modified -- the SQL or execute commands). It does NOT actually have to be SQL. SignatureAlbert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com On Oct 29, 11:09 am, "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote: > The easiest way is to use a pass-through query, and change the SQL to > include the parameters. [quoted text clipped - 20 lines] > > thanks much, > > mcnewsxp i get an "expected function or variable" error on the .Execute. The implication is that you didn't supply all of the needed parameters when you rewrote the SQL for the pass-through query. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > On Oct 29, 11:09 am, "Douglas J. Steele" > <NOSPAM_djsteele@NOSPAM_canada.com> wrote: [quoted text clipped - 24 lines] > > i get an "expected function or variable" error on the .Execute. On Oct 30, 9:47 am, "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote: > The implication is that you didn't supply all of the needed parameters when > you rewrote the SQL for the pass-through query. [quoted text clipped - 35 lines] > > > i get an "expected function or variable" error on the .Execute. when i changed Set rsCurr = qdfPassthrough.Execute to qdfPassthrough.Execute it ran. but no return value. the ADO version is much simpler and less obfuscated IMO. > could i get an example of how to call a sql stored proc that requires 4 > input parms and returns 1? > DAO or ADO or both. > thanks much, > mcnewsxp this is what i was looking for: Dim objCmd As New ADODB.Command Dim longConveyance As Long objCmd.ActiveConnection = "Provider=SQLOLEDB;Server=SQI-CDCDV1.CDC.GOV \QSRV1;Database=SCANNEDFORMSEPIX;Trusted_Connection=yes;" objCmd.CommandType = adCmdStoredProc objCmd.CommandText = "spInsertConveyance" objCmd.Parameters.Refresh objCmd.Parameters(1).Value = txtAirlineCode objCmd.Parameters(2).Value = txtArrivalAirportCode objCmd.Parameters(3).Value = txtFlightNumber objCmd.Parameters(4).Value = txtArrivalDate objCmd.Parameters(5).Value = txtArrivalState objCmd.Parameters(6).Value = Null objCmd.Execute longConveyance = objCmd.Parameters(0).Value longConveyance = objCmd.Parameters(6).Value Set objCmd = Nothing |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.