Results 1 to 6 of 6
  1. #1
    stalk is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2014
    Posts
    143

    How can I execute a sql procedure from Access DB?

    I have three SQL stored procedure that I would like to run frequently from Access DB where the start date and end date changes.



    Planning to create a form with a drop down to select the procedure and date fields to select the date range.
    What event I should select to run the stored procedure?

    Exec TN_user_changes '9/10/2021', '11/9/2021'
    Exec RN_user_changes '10/1/2021','10/31/2021'
    Exec Seq_user_changes '1/1/2021', '11/10/2021'


    Can some one share sample code? How to start this?

    Thank you

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You might create some confusion by calling a stored query a procedure. Procedures are something else. Also, execute implies the query is an action query, which seems it may not be the case here.

    If you format form date textboxes as one of the available date options, you'll get the bonus of the built in calendar. To answer which event, I'd use a button click. That would be the most efficient method IMO, because you can validate that the date controls actually contain dates and that the end date is not before the start date. Can also validate that other controls have values if required, or deal with it if optional. Since code would be involved, I wouldn't bother with a stored query because if you create references to form controls, the query does not care if there are dates - the lack of which will produce no results. There is a way to deal with this but it's more effort than running a sql statement or a query from code instead.

    You didn't say what the query does - update, append, delete or will just return records. If the latter, users should not be dabbling in updatable queries as opposed to working in forms, so there's that. You can open a select query with DoCmd.OpenQuery "nameOfQuery" and get a datasheet if you really must. In that case, your simplest solution would be to have this query reference form fields as criteria and use the button code to validate first. However, this query would be useless without the form being open and populated.

    If none of that seems appealing, best to tell more about what you want to do.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    The given code is exactly how you would run a stored procedure in SQL Server, so I assume TN_user_changes, RN_user_changes and Seq_user_changes indeed are 3 stored procedures created on the server.
    To execute them from access you can call them from access using a pass-through query. Beware you're calling them from a different environment so pass the complete address [servername].[schemaname].[procedurename]
    something like :
    MyServer.dbo.TN_user_changes
    '9/10/2021', '11/9/2021'



  4. #4
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I would always pass a date to an SP in 'yyyy-mm-dd' format to avoid any issues with regional variations.

    Create a passthrough query in Access then, assuming it doesn't return a result set, set the option for Returns records in the pass through to No
    You pass through query can be saved with the server connection details.

    It would then literally look like
    EXEC [dbo].[TN_user_changes] @StartDate = '2021-10-29', @EndDate = '2021-10-30'

    You will notice I include the parameter names, I find it helps in debugging when it all wrong.
    If you do that the parameters can be supplied in any order but the names must explicitly match the named parameters in the stored procedures.

    I have a generic function for using a stored Access pass through dynamically.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I totally missed that this was sql server or some version of it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    stalk is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2014
    Posts
    143
    Thank you all for responding. I tried to open my Access file and it gave me error and erased all the code. Not sure what happened none of the buttons are working.
    Ned to fix the DB before implementing the above stored procedure code

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Execute SP from Access as datasource
    By jaryszek in forum Access
    Replies: 4
    Last Post: 01-18-2018, 03:32 AM
  2. Event Procedure won't execute into an If..Then segment
    By hfreedman1957 in forum Programming
    Replies: 6
    Last Post: 05-25-2017, 02:18 PM
  3. RunSQL vs. QueryDef.Execute vs. CurrentDB.Execute
    By caubetiep1337 in forum Queries
    Replies: 8
    Last Post: 12-16-2015, 05:35 PM
  4. Replies: 33
    Last Post: 09-25-2015, 08:39 AM
  5. Execute MySQL Stored Procedure with Access 2010 VBA?
    By DanielHofer in forum Programming
    Replies: 5
    Last Post: 01-23-2012, 01:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums