CODEWITHSUNDEEP
databasems-accessstored-procedures
D.R.
D.R.

Reputation: 1219

Stored Procedure in Access DB

I have an MS Access database (.mdb) with a front end of reports built onto it that generates a report to calculate the pay/profitability of an insurance agent. There is a column on the report (TASC bonus) that is not a column in the table. It is apparently calculated by a stored procedure (this is what I've been told). Is there a way to view the stored procedures in an Access database to detemine how this column is calculated? Unfortunately I cannot post the database due to security/privacy issues. I believe the data (and perhaps the stored procedure) in the Access database is extracted from a SQL database and then "massaged" for reports.

I am thinking there must be some sort of db viewer that would allow you to see all the tables and procedures but my Google searching has turned up nothing (and usually I'm pretty good at finding just about anything). I always post here as a last resort when I'm truly stumped!

Any and all help is greatly appreciated. Thanks in advance!

Upvotes: 0

Views: 2034

Answers (2)

David-W-Fenton
David-W-Fenton

Reputation: 23067

Is the data stored in a Jet/ACE MDB/ACCDB or in SQL Server or some other database server? "Stored procedure" is not terminology that's native to Access and while there are certain things in common between some kinds of stored Access queries and what is meant by "stored procedure" in server databases, there's not a lot.

That said, you should look at the Recordsource of the report and post it as an edit to your answer. That will likely indicate where the data is coming from. It's highly unlikely, im my opinion, that the report is getting that data from a server-side stored procedure, but the SQL should provide clues as to where it's coming from.

Upvotes: 0

Howard Bash
Howard Bash

Reputation: 26

You can query the database for the object (stored procedure) definition. The particulars of the query required depends on the database you wish to connect to.

For MS SQL: select object_definition(object_id) from(sys.procedures) where name = '" & sProcName & "'"

For Oracle: select text from user_source where name = upper('" & sProcName & "') order by type, line

Hope that helps to get you there.

Upvotes: 1

Related Questions