Post reply

Command Button to run Stored Proc

  • February 10, 2008 at 10:10 pm

    #378437

    I am not an Access DBA; I am a SQL 2000/2005 DBA that desperately needs help.

    I am running a SQL backend an Access Front End. What I am doing is creating the Access front end to run a stored procedure that I created in SQL 2005. The stored procedure takes the input of an employee id and resets the user. It is a 2 part procedure that first changes the value in one table to False, then the value in the 2nd table to 0.

    I have created an Access Form that has a drop-down list of the employee Id's and a "Reset" button. What I would like, is for the Reset button to take in the value of the selected ID from the Form and run the Stored Procedure. I do not know the code to use to get the Event Procedure to run the stored procedure. I have researched online but have found no help with the situation.

    Please Help

  • February 11, 2008 at 7:58 am

    #776903

    Is your Access front end an ADP or MDB?

  • February 11, 2008 at 1:50 pm

    #777068

    It is: Access front end an ADP

  • February 12, 2008 at 2:08 am

    #777235

    I found a snippet of sample code in my Access folder. Here it is, with the proviso that it is old, it came from some free online resource that I cannot recall, and I have never used it! My notes said is used to execute a stored proc from an Access form, passing parameters from form controlswhich sounds similar to what you are looking for.

    If you decide to try it, please let me know if it works or not, for future reference!

    thanks

    Paul

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

    --Note, strConnect is like:

    "ODBC;DSN=MyDSN;SRVR=MyServer;DATABASE=MyDatabase;UID=MyUserAccount;PWD=MyPassword;"

    Dim strConnect As String

    Dim strSQL As String

    Dim dbs As Database

    Dim qdf As QueryDef

    Dim strErrMsg As String

    Set dbs = CurrentDb

    strConnect = c_CONN_STR

    Set qdf = dbs.CreateQueryDef("")

    qdf.Connect = strConnect

    strSQL = "exec xyzabc_my_storded_proc " &

    Me![txtMy_Forrm_Field_ID] & ",'" & Me![txtMy_Form_SomeOtherData] &

    "', " & "'" & Forms![frmMyForm]![txtMy_Forrm_Field_ID] & "'"

    qdf.ReturnsRecords = False

    qdf.SQL = strSQL

    dbs.QueryTimeout = 2000

    qdf.ODBCTimeout = 1000

    qdf.Execute

    DoCmd.Hourglass False

  • February 12, 2008 at 9:49 am

    #777402

    With an ADP you can set the record source of your form to be a stored procedure from the properties menu. From the same menu you can establish the source of your input parameters.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply