create a form using a store procedure from sql

[rsbutterfly219]

hi all, I have a front end access database with a sql server backend for my
tables. I have an old form in which I have created a store procedure in sql
server, i would like to connect this form to my store procedure created it,
can someone tell me the steps to do this?

thank you,

Jessica

 

[Albert D.Kallal]

The first thing is to simply create a new linked table to the stroedproc.

I would then open this link..and see if you can edit the data..

I don't think stored proces are editable this way...but, jus try the
above...if it works..you can then simply base the from on this linked table
which is linked to a stored proc.

You can certainly link a table to a sql view, and I do that all the time. I
not on a machine right now with sql server, so I can't trying linking to a
stored proc to see if they are editable....

So, if possible, use a view in place of the stroced proc....at least we know
that will work....

 

[rsbutterfly219]

thanks for your reply, but my form is in access, what i did was i created a
sql pass through query that 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 to test it i put dates for the input
parameters. 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), how can i put that in
the sql pass through query?

 

[Albert D.Kallal]

thanks for your reply, but my form is in access, what i did was i created
a
sql pass through query that 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?

Yes, but in the above, you don't explain what the form is linked to
now...are you creating a reocrdset as a result of a pass-thoughquery, or
stored proc, and THEN assigning that reocrdset to the forms data source
(this is one appoarch...). (it is not clear at all how you are doing the
above...).

Also, while we are at this...what version of ms-access, and are you using a
mdb with linked tables...or a ADP project? Again, a adp, or a mdb to sql
server are VERY MUCH differnt here....

there has to be an easier way just to link the access form directly to the
sp?
correct?

Well, as mentioned, just link the form to a view. Then open the form using
the "where" clause. You made no case, or arguments as to why you are using a
SP now. You can use a view with no parameters, and use the form/reports
ability to have parameters on the fly via the "where" clause. That way, you
can change, or add, or have any new conditions you want..and you don't have
to run around like a chicken writing a new stored proc each time you have
some new condition for a form...

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 to test it i put dates for the
input
parameters. 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), how can i put that
in
the sql pass through query?

are you planning to send the reuslts to a reprot, or a form? (or both?).

The simple solution again is to supply the prompts on the ms-access
side..and also supply the conditions on the ms-access side...
don't hard code the parameters as you have..since then you always have to
supply them!!! As mentioned, link the form (or report)
to a view (Again, not clear if you are using a adp, or a mdb file with
linked tables to sql server).

Regardless of a adp/mdb, you simply promt the user in ms-access, and then
pass that..

eg:

strStartDate = inputbox("enter start Date")
strEndDate = inputbox("enter end date")

Then simply create a pass-thougth query such as

strMyExec = "exec myreprot '" & strStartDate & "', '" & strEndDate & "'"

(you need to use # in place of the above ' if you are using linked tables
from a mdb appcation to sql server. If using adp..aobve shouild be ok..

However, once again, I don't see why a SP is being used here...

You will find it MUCH easier to simply create the query sql side WITHOUT any
parameters and save that as a view. You then create a linked table (I am
assuming you are NOT using a adp project here), or if using a adp..just use
that view.

So, dump the use of store proc , and dump the use of parameters.....you
don't need them....

simply use the reports (or forms) "where" clause to open the form, or
report.

eg:

strWhere = "Invoice_Date >= '" & strStartDate & "' and Invoice_Date <= '" &
strEndDate & "'"

docmd.OpenForm "frmInvoices",,,strWhere

And, you can do do the same type of filtering for reprots

docmd.openReprot "invoices",,,strWhere

As mentioned, use # in place of ' in the above if you are using a mdb file
to sql server....

So, check out the use of the "where" clause when you open a form, or a
report. This approach will elimonate the need to write a zillion little
stored procs everywhere here and there....

 

[rsbutterfly219 via AccessMonster.com]

Thank you so much!, to answer your questions:
my form right now is linked to my sql pass thru query which exec my sp . I
did not know you can put parameters straight from the form. this will make
things a lot easier for me like you say. My database is in access 2000 and
my sql server is 2000 as well. it is an MDB. so i made my sp a view in sql
server and i edited my sql pass thru query to:

strMyExec = "exec myreprot '" & strStartDate & "', '" & strEndDate & "'"

now where exactly in the form do i put ?

strStartDate = inputbox("enter start Date")

strEndDate = inputbox("enter end date")

for this to prompt the user to enter this dates when running the form?

thanks again,
Jessica

thanks for your reply, but my form is in access, what i did was i created
a

[quoted text clipped - 3 lines]

sp?
correct?

Yes, but in the above, you don't explain what the form is linked to
now...are you creating a reocrdset as a result of a pass-thoughquery, or
stored proc, and THEN assigning that reocrdset to the forms data source
(this is one appoarch...). (it is not clear at all how you are doing the
above...).

Also, while we are at this...what version of ms-access, and are you using a
mdb with linked tables...or a ADP project? Again, a adp, or a mdb to sql
server are VERY MUCH differnt here....

there has to be an easier way just to link the access form directly to the
sp?
correct?

Well, as mentioned, just link the form to a view. Then open the form using
the "where" clause. You made no case, or arguments as to why you are using a
SP now. You can use a view with no parameters, and use the form/reports
ability to have parameters on the fly via the "where" clause. That way, you
can change, or add, or have any new conditions you want..and you don't have
to run around like a chicken writing a new stored proc each time you have
some new condition for a form...

another question:
my stored procedure has an input parameter :

[quoted text clipped - 14 lines]

in
the sql pass through query?

are you planning to send the reuslts to a reprot, or a form? (or both?).

The simple solution again is to supply the prompts on the ms-access
side..and also supply the conditions on the ms-access side...
don't hard code the parameters as you have..since then you always have to
supply them!!! As mentioned, link the form (or report)
to a view (Again, not clear if you are using a adp, or a mdb file with
linked tables to sql server).

Regardless of a adp/mdb, you simply promt the user in ms-access, and then
pass that..

eg:

strStartDate = inputbox("enter start Date")
strEndDate = inputbox("enter end date")

Then simply create a pass-thougth query such as

strMyExec = "exec myreprot '" & strStartDate & "', '" & strEndDate & "'"

(you need to use # in place of the above ' if you are using linked tables
from a mdb appcation to sql server. If using adp..aobve shouild be ok..

However, once again, I don't see why a SP is being used here...

You will find it MUCH easier to simply create the query sql side WITHOUT any
parameters and save that as a view. You then create a linked table (I am
assuming you are NOT using a adp project here), or if using a adp..just use
that view.

So, dump the use of store proc , and dump the use of parameters.....you
don't need them....

simply use the reports (or forms) "where" clause to open the form, or
report.

eg:

strWhere = "Invoice_Date >= '" & strStartDate & "' and Invoice_Date <= '" &
strEndDate & "'"

docmd.OpenForm "frmInvoices",,,strWhere

And, you can do do the same type of filtering for reprots

docmd.openReprot "invoices",,,strWhere

As mentioned, use # in place of ' in the above if you are using a mdb file
to sql server....

So, check out the use of the "where" clause when you open a form, or a
report. This approach will elimonate the need to write a zillion little
stored procs everywhere here and there....

 

[Albert D.Kallal]

Thank you so much!, to answer your questions:

You are most welcome.

my form right now is linked to my sql pass thru query which exec my sp .

Ah..ok, since you were "avaanced" enought to use a stroed roejct, I was
guiessing that you were perhaps using a ms-access proejct (adp), but
you are not....

now where exactly in the form do i put ?

strStartDate = inputbox("enter start Date")

for this to prompt the user to enter this dates when running the form?

The key to the above is best to build some type of reprot prompt form. Then,
you can even place two text boxes on a form.

ms-access queryes can prompt you, but as you see, then that query can' be
used with *differnt* condiations and parmatmers.

So, in most cases, if you just need a promt or so...build a form to promt
the user.

Here is the idea I am getting at:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The normal approach is to build a un-bound form (a un-bound form is a form
that is NOT attached to a table - these forms are typicaly desiged for user
interface face stuff like promtps, print buttions etc).

The above shold give you some ideas

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions or
paramters are placed in the query. Just nice clean sql for the report...

To "send" the conditions to the report (or form), you simply use the "where"
clause.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For a date range, we could put two calendar contorls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere

 

[rsbutterfly219 via AccessMonster.com]

hi again, so I understand what you meant.. i added a command prompt on my
form and put this code:

Invoice Detail Inquiry being my pass thru query that says:
exec myReport(sp)

Private Sub cmdDateRange_Click()

Dim qd As QueryDef

Set qd = db.QueryDefs("Invoice Detail Inquiry SQL")

qd.Parameters("[Please Enter a Date]") = " & Me.begindate & " And ([“Please
Enter End Date]”) = “&Me.Enddate & “”


End Sub

it does not work though... I am new to vb script... can you help?

Thank you so much!, to answer your questions:

You are most welcome.

my form right now is linked to my sql pass thru query which exec my sp .

Ah..ok, since you were "avaanced" enought to use a stroed roejct, I was
guiessing that you were perhaps using a ms-access proejct (adp), but
you are not....

now where exactly in the form do i put ?

strStartDate = inputbox("enter start Date")

for this to prompt the user to enter this dates when running the form?

The key to the above is best to build some type of reprot prompt form. Then,
you can even place two text boxes on a form.

ms-access queryes can prompt you, but as you see, then that query can' be
used with *differnt* condiations and parmatmers.

So, in most cases, if you just need a promt or so...build a form to promt
the user.

Here is the idea I am getting at:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The normal approach is to build a un-bound form (a un-bound form is a form
that is NOT attached to a table - these forms are typicaly desiged for user
interface face stuff like promtps, print buttions etc).

The above shold give you some ideas

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions or
paramters are placed in the query. Just nice clean sql for the report...

To "send" the conditions to the report (or form), you simply use the "where"
clause.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:

dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For a date range, we could put two calendar contorls on the screen. The code
could be:

dim strWhere as string
dim strStartDate as string
dim strEndDate as string

strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere