SQL SERVER SP --> Access Report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL SERVER SP --> Access Report

Author

Topic

doco
Yak Posting Veteran

77 Posts

Posted - 2010-06-03 : 16:58:59

Is it possible to run a SQL Server Stored Procedure remotely from Access and use in an Access report?

Education is what you have after you've forgotten everything you learned in school

doco
Yak Posting Veteran

77 Posts

Posted - 2010-06-07 : 17:16:24

The answer is yes and here is how using vba


'   the OpenStoredProcedure will then produce the necessary prompts for any existing params
    DoCmd.OpenStoredProcedure szProcName, acViewNormal, acReadOnly

Education is what you have after you've forgotten everything you learned in school

doco
Yak Posting Veteran

77 Posts

Posted - 2010-06-07 : 17:57:47

[code]
CREATE PROCEDURE dbo.udspAccountOwnershipAddress
@tax_year varchar(4) -- tax year
--@nbhd int -- neighborhood
AS
select
p.id as property_id,
p.parcel_number,
p.alt_parcel_nr,
pt.id as ppi_id,
pt.party_id,
pt.address_id,
pt.prop_role_cd,
pt.role_percentage,
pt.eff_from_date,
pt.eff_to_date,
pt.change_reason_cd
into #prop_prop_invlmnt
from AscendWheelerMain..property p INNER JOIN
AscendWheelerMain..party_prop_invlmnt pt ON
p.id = pt.property_id
and pt.eff_to_date is null
and pt.prop_role_cd = 524
and pt.party_id =
( select max( id.party_id )
from AscendWheelerMain..party_prop_invlmnt id
where id.eff_to_date is null
and id.prop_role_cd = pt.prop_role_cd
and id.property_id = pt.property_id )
where
p.eff_to_date is null
order by p.id;
-- --------------------------------------------------------------------------
select
p.property_id,
p.parcel_number,
left( alt_parcel_nr, 16 ) as map_lot,
tca.tca_number as area,
cast( sz.value as decimal(10,2) ) as acreage,
o.org_name,
ad.line_1,
coalesce( ad.line_2, '' ) as line_2,
zip.city,
zip.state,
zip.zipcode,
zip.country
from
tempdb.#prop_prop_invlmnt p LEFT OUTER JOIN
AscendWheelerMain..organization o ON
p.party_id = o.party_id LEFT OUTER JOIN
AscendWheelerMain..prop_valuation pv ON
p.property_id = pv.property_id
and pv.tax_year = @tax_year
and pv.taxable_ind = 'Y' LEFT OUTER JOIN
AscendWheelerMain..tax_code_area tca ON
tca.id = pv.tca_id
and tca.id != 999 LEFT OUTER JOIN
AscendWheelerMain..property_char sz ON
p.property_id = sz.property_id
and sz.tax_year = pv.tax_year
and sz.prop_char_typ_code = 'SIZE' LEFT OUTER JOIN
AscendWheelerMain..address ad ON
p.address_id = ad.id LEFT OUTER JOIN
AscendWheelerMain..zipzip as zip ON
ad.zip_postal_code = zip.zipcode
and ad.city = zip.city
--where zipcode is null
order by p.property_id;

--exec udspAccountOwnershipAddress 2010
[\code]

(Why aren't the code and quote tags working?)

Education is what you have after you've forgotten everything you learned in school

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-08 : 03:39:14

Use slashes and not backslashes in closing tags.

Btw. thank you for coming back and posting your solution

No, you're never too old to Yak'n'Roll if you're too young to die.

bravo
Starting Member

7 Posts

Posted - 2010-06-24 : 16:13:53

Yes you can do that by using VBA like:-

' the OpenStoredProcedure will then produce the necessary prompts for any existing params
DoCmd.OpenStoredProcedure szProcName, acViewNormal, acReadOnly

Nice Forum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources