February 10, 2008 at 10:10 pm
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
Is your Access front end an ADP or MDB?
February 11, 2008 at 1:50 pm
It is: Access front end an ADP
February 12, 2008 at 2:08 am
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
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