Access 2000 X-ref?

[Fjordur]

Hi,
is there a free/cheap way of obtaining a kind of x-ref of an Access
application? Stated otherwise: I need to clean up my base, delete unused
queries, forms...
Also I want to rename some objects, and I'm not sure if the "Name
AutoCorrect" feature works 100%.

OTOH, I know there are some products out there that claim to do that kind of
jobs but those I found are rather expensive for a one-time cleanup and
besides they don't seem to be able to do someting as simple as
- draw a tree or table of which object uses wich object
- rename objects

Suggestions?

 

[Albert D.Kallal]

There is the database documenter built into ms-access. It really only does a
printout of very piece of code, table, forms etc.

You can find this via

tools->Analyze->Documenter

Play around with the above..as it does produce a lot of documentation.

of which object uses which object

The problem with the above is that ms-access has a good runtime expression
service. What this means is that OFTEN code in ms-access will be ambiguities
until runtime....

eg:

strForm = "frmCustomers"
strField = "LastName"

msgbox "The last name of the persons name in form Customers is = " &
forms(strForm)(strField).

The above code references the form "frmCustomers", and also references the
control on the form called "LastName". How is a documentation system like
Rational Rose going to show you that the above code is dependent on the that
form..and will fail if you delete the form? So, due to the resolving of A
LOT OF OBJECTS at runtime, there is no tool out there that really will
document dependences since you would actually have to run and interpret the
code.

So, one of the "great" feature of ms-access is that you have a expression
service. You can build a where clause two ways for a form.

1)
strWhere = "LastName = forms!frmCustomers!LastName"

or, the why I use:

strWhere = "LastNAme = '" & forms!FormCustomers!LastName & "'"

Note in then the 2nd case we actually resolve the string to a LEGAL sql
where clause that can be used to open a form.

However, with the ms-access expression service, we actually can use the
first syntax..and ms-access will figure out the string for us..and even add
the quotes around it (or # if a date...or nothing if a number).

So, due to the wide use of the expression service..you can't tell what code
references what to any significant degree that would allow you to delete
code.

However, access 2003 does try it's best to show dependences on a object...at
least with built in objects like queries, reports and forms (if you right
click on any object in the main database window...you can view
dependencies - this is a new feature for a2003..but still have the above
limitations in code as mentioned above).


Also, if you have not yet..I STRONGLY suggest you grab the mztools here:

http://www.mztools.com/

These tools are nice for your own code, but when jumping into others
code...it is INVALUABLE. Of course, when you are in the code editor, we all
know that placing your cursor on a sub (or function name), and hitting
shift-f2 will "jump" you to that routine. If you hit ctrl-shift-f2...you
un-cork back the way you came. This effectively allows you to "ski" through
your code...jumping from code calls to code calls.

.. However, the mztools will actually TELL YOU what OTHER routines
use the CURRENT routine you are in. So, you can instantly view all dependant
code routines that call the current routine you are in. This is simply a
must have when diving into uncharted territories called "other peoples
code!!"

good luck...and grab the mztools...as they are life saver when looking at
other peoples code....

 

[Fjordur]

There is the database documenter built into ms-access. It really only does a
printout of very piece of code, table, forms etc.
Play around with the above..as it does produce a lot of documentation.

Way too much, and (to me, now) mostly useless

The problem with the above is that ms-access has a good runtime expression
service. What this means is that OFTEN code in ms-access will be ambiguities
until runtime....

Sure, but OTOH it would be nice to have the explicit (non-coded)
dependencies. As I know where I have coded dependencies (this is my
development, and there are not so many of them), that would help a lot.

So, one of the "great" feature of ms-access is that you have a expression
service. You can build a where clause two ways for a form.
1)strWhere = "LastName = forms!frmCustomers!LastName"
or, the why I use:
strWhere = "LastNAme = '" & forms!FormCustomers!LastName & "'"
Note in then the 2nd case we actually resolve the string to a LEGAL sql
where clause that can be used to open a form.
However, with the ms-access expression service, we actually can use the
first syntax..and ms-access will figure out the string for us..and even add
the quotes around it (or # if a date...or nothing if a number).

Didn't know syntax 1.
What's the advantage of it? apart from saving the typing of & and quotes?

Also, if you have not yet..I STRONGLY suggest you grab the mztools here:
http://www.mztools.com/

Does not seem to do Access X-refs / dependencies, or did I miss it? It seems
100% code oriented.

These tools are nice for your own code, but when jumping into others
code...it is INVALUABLE. Of course, when you are in the code editor, we all
know that placing your cursor on a sub (or function name), and hitting
shift-f2 will "jump" you to that routine. If you hit ctrl-shift-f2...you
un-cork back the way you came. This effectively allows you to "ski" through
your code...jumping from code calls to code calls.
. However, the mztools will actually TELL YOU what OTHER routines
use the CURRENT routine you are in. So, you can instantly view all dependant
code routines that call the current routine you are in. This is simply a
must have when diving into uncharted territories called "other peoples
code!!"

I don't have much VB code now but that's interesting, thanks.

 

[Fjordur]

Hi,
is there a free/cheap way of obtaining a kind of x-ref of an Access
application? Stated otherwise: I need to clean up my base, delete unused
queries, forms...
Also I want to rename some objects, and I'm not sure if the "Name
AutoCorrect" feature works 100%.

OTOH, I know there are some products out there that claim to do that kind of
jobs but those I found are rather expensive for a one-time cleanup and
besides they don't seem to be able to do someting as simple as
- draw a tree or table of which object uses wich object
- rename objects

Found nothing yet that shows unused objects; help still appreciated.
BTW, I found "Osage Inside Access"
http://www.topshareware.com/Osage-Inside-Access-2000-download-14032.htm an
add-in, seems very professionnaly build, that will show, for each object,
which object(s) used it or are used by it.

 

[Albert D.Kallal]

Didn't know syntax 1.

What's the advantage of it? apart from saving the typing of & and quotes?

Well, in fact, I consider it a dis advantage, as then your code relies on
the
expression service.

If you build a bunch of code to parse out values, or prompts, then you don't
have to rely on the expression services.

For example, you might go:

strSql = " update table ....bla bal bal some long compilex sql statment
goes here"

strWhere = " Where CustomerID = " & forms!Customers!CustomerID

currentdb.Execute strSql & strWhere

When you use the execute method of the JET data engine you do NOT have the
expression services available (you only have JET sql available). It runs
faster, and further you could more easily convert the code to run with sql
server.

if you use

strWhere = " Where CustomerID = forms!Customers!CustomerID"

currentdb.Execute strSql & strWhere

The above will fail...since you don't have use of the expression
service for you.

However you could use the ms-access command of

docmd.RunSql (strSql & strWhere)

The runsql method of ms-access does use the expression service, and thus
you can use VBA functions etc. in your sql.

I always preferred the .execute method..and often it is MUCH faster then
the docmd...as the whole thing is NOT wrapped in a transaction..and you
don't
get any prompts about "you are about to update xxx reocrds.....bla bla". The
..execute method avoids all this extra junk (it does not even calculate how
many records will be updated inside of that transaction...just eliminating
that part is faster.......no transaction here).

Further, as mentioned, if you build the strings your self, then you can use
pass-thru quires to sql server, and 100% by-pass the JET engine. If your
code relies on the expression services..then you can't use pass-through
quires in ms-access. So, if your code everwhere relies on the
expression service, then convrting to sql server will be harder if you
are trying to acheive max performance.

Does not seem to do Access X-refs / dependencies, or did I miss it? It
seems
100% code oriented.

correct...mztools only helps for code.

So, you are down to 3rd party tools, or a2003 which will show dependences
(it does force you to turn on track-auto name correct --- which I then turn
off right after viewing dependences).

 

[John Vinson]

is there a free/cheap way of obtaining a kind of x-ref of an Access
application? Stated otherwise: I need to clean up my base, delete unused
queries, forms...
Also I want to rename some objects, and I'm not sure if the "Name
AutoCorrect" feature works 100%.

I question whether it works as much as 10% - it's horribly buggy IME.

Check out:

Free:
http://www3.bc.sympatico.ca/starthere/findandreplace
Find and Replace: http://www.rickworld.com
Speed Ferret: http://www.moshannon.com
Total Access Analyzer: http://www.fmsinc.com

John W. Vinson[MVP]

 

[Fjordur]

quotes?
Well, in fact, I consider it a dis advantage, as then your code relies on
the expression service.

I agree, from both the portability and style point of view (I don't like
implicit feature, so I'd rather write the & operator)

(...)
Further, as mentioned, if you build the strings your self, then you can use
pass-thru quires to sql server, and 100% by-pass the JET engine. If your
code relies on the expression services..then you can't use pass-through
quires in ms-access. So, if your code everwhere relies on the
expression service, then convrting to sql server will be harder if you
are trying to acheive max performance.

Right, too

So, you are down to 3rd party tools, or a2003 which will show dependences
(it does force you to turn on track-auto name correct --- which I then turn
off right after viewing dependences).

Funny that no tool seems to focus on UNused objects...
Thanks for the help