Post reply

Command Button to run Stored Proc

  • 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 4 posts - 1 through 5 (of 5 total)

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