Searching for DB Objects within an ADP

[Matt]

I inhereted a rather extensive MS Access 2000 database (SQLServer 2000
backend)in adp format.

Is there a way to search the Access front end for references to
specific database objects?

Let's say there are 100 macros and I want to know if any of them use a
particular stored procedure. I'd hate to have to manually look in all
100 macros.

Or, suppose I wanted to see if a stored procedure was used as a
recordsource for any number of 100 or so reports. What's the most
efficient way of doing so?

Thanks for any light you can shed!

Matt

[Bradley.-.]

"Matt" <mh******@yahoo.com> wrote in message
news:5f*************************@posting.google.co m

I inhereted a rather extensive MS Access 2000 database (SQLServer 2000
backend)in adp format.

Is there a way to search the Access front end for references to
specific database objects?

Let's say there are 100 macros and I want to know if any of them use a
particular stored procedure. I'd hate to have to manually look in all
100 macros.

Or, suppose I wanted to see if a stored procedure was used as a
recordsource for any number of 100 or so reports. What's the most
efficient way of doing so?

Thanks for any light you can shed!

Matt

Does't SQL server show you the object dependancies? Open up the
Enterprise manager and check out the objects properties....
--
regards,

Bradley

[Larry Linson]

"Bradley.-." wrote

Does't SQL server show you the
object dependancies? Open up the
Enterprise manager and check out
the objects properties....

SQL Server is unaware of Access reports, which were the subject of the
question.

[Bradley.-.]

"Larry Linson" <bo*****@localhost.not> wrote in message
news:_l******************@nwrddc02.gnilink.net

"Bradley.-." wrote

> Does't SQL server show you the
> object dependancies? Open up the
> Enterprise manager and check out
> the objects properties....

SQL Server is unaware of Access reports, which were the subject of the
question.

That'll teach me for skimming the post ;)
--
regards,

Bradley

[Larry Linson]

Yes, Access projects have an AllReports collection which you can "enumerate"
(loop through... you will have to open each Report in the collection in
Design View, however, to examine the RecordSource). And, remember that there
are multiple ways to specify the same thing: a table name, or an SQL
Statement that retrieves the information (which may refer to one or more
tables, and may retrieve all, or just some, of the fields).

I don't have an example for Reports, but here are pertinent statements from
an example that I used to go through the AllModules collection:

Dim obj1 As AccessObject, dbs1 As Object
Dim mod1 As Module, frm1 As Form

Set dbs1 = Application.CurrentProject

For Each obj1 In dbs1.AllModules
If obj1.Name <> "basDesignTimeCode" Then
If obj1.IsLoaded = True Then
... do something
Else
DoCmd.OpenModule obj1.Name
CurrentObjectType = obj1.Type
CurrentObjectName = obj1.Name
... do something
DoCmd.Close acModule, obj1.Name ', acSaveYes
End If
End If
Next obj1

I find it difficult to imagine anyone using Macros in an Access project. I
certainly don't have any examples using Macros -- an Access project is, by
my definition, a developed application and the only Macro I'd think about
using in a developed application would be AutoKeys. But, surely enough,
there is an AllMacros Collection and decent help on how to use it in Access
2002 (if you're using Access 2003, it may be more difficult to find the help
you need).

Larry Linson
Microsoft Access MVP

Larry Linson
Microsoft Access MVP
"Matt" <mh******@yahoo.com> wrote in message
news:5f*************************@posting.google.co m...

I inhereted a rather extensive MS Access 2000 database (SQLServer 2000
backend)in adp format.

Is there a way to search the Access front end for references to
specific database objects?

Let's say there are 100 macros and I want to know if any of them use a
particular stored procedure. I'd hate to have to manually look in all
100 macros.

Or, suppose I wanted to see if a stored procedure was used as a
recordsource for any number of 100 or so reports. What's the most
efficient way of doing so?

Thanks for any light you can shed!

Matt

[Matt]

Thanks, Larry, for that insight. Your solution is not what I was
expecting, but will work just fine for my needs.

I don't have an example for Reports, but here are pertinent statements from
an example that I used to go through the AllModules collection:
Here is the code I used to enumerate the reports and obtain each
report's recordsource (pardon the adaption of your formatting, I
modified it to match our existing code):

Dim oObj As AccessObject
Dim oProject As Object

Set oProject = Application.CurrentProject

For Each oObj In oProject.AllReports
DoCmd.OpenReport oObj.Name, acViewDesign
Debug.Print "Report: " & oObj.Name
Debug.Print Application.Reports(oObj.Name).RecordSource
DoCmd.Close acReport, obj1.Name
Next oObj
I've actually gone a step further and imported this info into a
SQLServer table, so I can query against it using SQL. You've made my
job here much easier!

I find it difficult to imagine anyone using Macros in an Access project. I
certainly don't have any examples using Macros -- an Access project is, by
my definition, a developed application and the only Macro I'd think about
using in a developed application would be AutoKeys. But, surely enough,
there is an AllMacros Collection and decent help on how to use it in Access
2002 (if you're using Access 2003, it may be more difficult to find the help
you need).

I had to jump thru a few hoops, but used similar code to that above
for the macros and was able to obtain the info I needed.

Hopefully without going to far OT, our database was developed here
over the last 4 or 5 years; the previous developer used quite a few
macros, mostly to export the output of stored procedures to Excel
spreadsheets. I've read that the main reason to avoid macros is
because of error handling. Understood. But using these "OutputTo"
macros is pretty straightforward- if they fail there is no "crash",
just a (rather scary, to the user) message box describing the problem.
But I am open to suggestions. If there is a better means of going
about this, please let me know and I will do some further research.

Thanks again for your help,

Matt

[Larry Linson]

I use VBA code... you can do anything in VBA code that you can do in macros
(except AutoKeys), and you can include error handling. I don't argue that
there are no cases and places where macros can be productively used, but I
use code in all cases, for consistency.

In fact, the code I copied was from an assignment where I was
programmatically adding error handling to over 500 procedures in a large ADP
done by someone else who had omitted error handling -- which omission was
causing the client a good deal of trouble.

Larry Linson
Microsoft Access MVP

"Matt" <mh******@yahoo.com> wrote in message
news:5f**************************@posting.google.c om...

Thanks, Larry, for that insight. Your solution is not what I was
expecting, but will work just fine for my needs.

I don't have an example for Reports, but here are pertinent statements from an example that I used to go through the AllModules collection:

Here is the code I used to enumerate the reports and obtain each
report's recordsource (pardon the adaption of your formatting, I
modified it to match our existing code):

Dim oObj As AccessObject
Dim oProject As Object

Set oProject = Application.CurrentProject

For Each oObj In oProject.AllReports
DoCmd.OpenReport oObj.Name, acViewDesign
Debug.Print "Report: " & oObj.Name
Debug.Print Application.Reports(oObj.Name).RecordSource
DoCmd.Close acReport, obj1.Name
Next oObj
I've actually gone a step further and imported this info into a
SQLServer table, so I can query against it using SQL. You've made my
job here much easier!

I find it difficult to imagine anyone using Macros in an Access project. I certainly don't have any examples using Macros -- an Access project is, by my definition, a developed application and the only Macro I'd think about using in a developed application would be AutoKeys. But, surely enough,
there is an AllMacros Collection and decent help on how to use it in Access 2002 (if you're using Access 2003, it may be more difficult to find the help you need).

I had to jump thru a few hoops, but used similar code to that above
for the macros and was able to obtain the info I needed.

Hopefully without going to far OT, our database was developed here
over the last 4 or 5 years; the previous developer used quite a few
macros, mostly to export the output of stored procedures to Excel
spreadsheets. I've read that the main reason to avoid macros is
because of error handling. Understood. But using these "OutputTo"
macros is pretty straightforward- if they fail there is no "crash",
just a (rather scary, to the user) message box describing the problem.
But I am open to suggestions. If there is a better means of going
about this, please let me know and I will do some further research.

Thanks again for your help,

Matt