make a form use a stored procedure in sql server
-
hi all, I have a front end access database with a sql server backend for my
tables. I have an old form that used to use access queries and was super slow, so I have created a store procedure in sql server, my form is in access. I created a
sql pass through query to exec the procedure and it works fine but i know
there has to be an easier way just to link the access form directly to the sp?
correct?
another question:
my stored procedure has an input parameter :
CREATE PROC myReport (@begindate smalldatetime, @enddate smalldatetime)
WHERE (Sales_Detail.Invoice_Date)>=@begindate And (Sales_Detail.Invoice_Date)
<= @enddate
so when i do the sql pass through query from my access database, to test it i put dates for the input
parameters (@begindate, @enddate). for example:
exec myreport '05/09/2005', '05/09/2006',
and it runs myReport with those parameters; however, I would like the user to
be prompt to enter these dates(@begindate, @enddate), when running the form just like when it was completely in access, how can i put that in
the sql pass through query?
Jessica
-
OK I assume you are using an mdb rather than an adp.
Hopefully you do mean form rather than report. You can get a recordset from the server and attach it to the form OR you can modify the SQL of the stored query.
To do what you want, create a couple of boxes in the form header plus a command button. Hide the detail section. When you click on the button, set up the recordsource and display the detail section.
The recordset version goes like this
- Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
cnn.Open ConnectionString
Set cmd = New ADODB.Command
With cmd
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter( _
ParamName1, _
ParamDataType1, _
adParamInput, _
ParamSize1, _
ParamValue1)
.Parameters.Append .CreateParameter( _
ParamName2, _
ParamDataType2, _
adParamInput, _
ParamSize2, _
ParamValue2)
.CommandText = StoredProcName
Set .ActiveConnection = cnn
End With
Set rst = New ADODB.Recordset
With rst
.CursorLocation = adUseClient
.LockType = RstLockType
.Open cmd, options:=adCmdStoredProc
End With
Set rst.ActiveConnection = Nothing
- Set Forms!MyForm.Recordset = rst
- Dim cnn As ADODB.Connection
-
thanks but I am not too sure that is the answer i was looking for... i have an mdb access database. so I think my best bet is to link the form directly to a view ( i already have in sql server) instead of th sp and then from the form in access put the parameters for the begindate and enddate. do you have an idea of how to do this????, i think it has something to do with a where clause in the form... ..
do i have to create a sql pass thru query to link to the view first? if not how do i link my form to my view and then put the paremeters for my user to be prompted to input these dates?
help please
-
I have already been there done that. I STRONGLY recommend you reconsider jfmccabe's answer. That is THE only way you'll get the performance you are looking for. It's worked for me for 7+ years. The only better option I know of than that is to redevelop in .NET and it's also using the same ADO technology as jfmcabe's answer.
-
ok, but i don't have a cmd button in my form, i don' t want the user to enter any data...the only data i want them to enter is when they are promt to enter the dates. i only have a cmd button to reselect the dates...
i already have the view called "invoice inquiry" in my sql server, i don't know how to link this to your record set...
-
You have a couple options.
One, redevelop the solution using an .adp file instead of an .mdb file. This is not trivial, and you may have to find new solutions to achieve some of the functionality you have now. But, you can bind a form directly to a stored procedure.
Two, bind the form to a view instead. Create the view in SQL Server. Link to it just like it was a table (it will show up in the Tables tab in Access). It now functions for all intents and purposes just like any other table. However, note that if you attempt to apply a filter as you're describing, Access will pull down the entire contents of the view, and then filter them. This may not give you the performance you're looking for.
IIRC, you should be able to modify your pass-through query to be:
EXEC myReport [Enter Beginning Date:],[Enter Ending Date:]
This will prompt your user for the beginning and ending dates. However, this is in general a poor solution, as it takes the input as strings, and does not validation on it.
A better solution is to create an intermediary pop-up form, which prompts for the dates. This allows you to enforce such things as input masks, and to validate things such as the beginning date being less than the ending date. Once the user has entered the dates, you can modify the QueryDef of the pass-through query to use the dates provided.
-
hello i tried modifying the pass through query to be:
EXEC myReport [Enter Beginning Date:],[Enter Ending Date:]
but like you say the validation does not work
it says odbcc call failed
error converting data type nvarchar to small date time.
is there any other way i could prompt the user using the pass through query?
-
i also tried this:
EXEC myReport [Enter Beginning Date:],[Enter Ending Date:]
@begindate = "#" & format(me.StartDate,"mm/dd/yyyy") & "#"
@enddate = "#" & format(me.EndDate,"mm/dd/yyyy") & "#"
but did not work either
-
First, you need to remember that SQL Server does NOT use # to designate dates. It uses the singe quote.
Second, for SQL Server standards, you actually should put the dates into the "yyyymmdd" format. It likes it better that way.
You are probably going to want to use a mini-form to enter the dates. We'll call it frmSelectDate.
You're going to be better off transforming the input into a datetime on the Access end, rather than passing a string to SQL Server and making it do the conversion.
I think (I am not in a position to test it currently) that you can do this:
EXEC myReport CDate(Forms!frmSelectDate!StartDate),CDate(Forms!frmSelectDate!EndDate)
Put an input mask on the fields on frmSelectDate. Do the validation of the values BEFORE you call the pass-through query.
-
thanks, i think i am in the right track.. so i created the form, i added two text boxes one for @begindate and another one for @enddate. now in the control source how do i make sure it is connecting with the invoices dates from the sp?
-
I'm not entirely sure what you mean by that. You should just be using unbound controls. Then, make a command button that opens the report. Validate the dates before you actually run the OpenReport command. Set the control source of the report to the pass-through query. AFAIK, that should work.
-
I did everything you told me to do but when i try to run the query it says odbcc call failed, error converting data type nvarchar to smalldate time. even though for the txt boxes i put short time as data type and the input max also...
-
Did you add the CDate function to the EXEC statement?
In your SP, are the variables declared as smalldatetime type?
-
got it to work!!!! thank you so much !
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply