form code from document object

[David McDivitt]

I am able to get code from within a form by opening in design mode and
looking at the module property. Unfortunately this requires an MSAccess
application object to be active, and docmd used on the application object.
DAO provides a collections object for a database, and the forms collection
can be accessed through that. I cannot find how to get to specifics of a
form through the document object. I do not need to cycle through all the
controls of a form, just see if certain text is used anywhere in a form. If
going through the application object I can open a form in design view, then
cycle through all controls, sections, properties, etc., examining for text.
I would like to do this just through the document object and not load a
MSAccess application. If found I assume the text I want would look like a
Visual Basic frm file or something like that.

I am doing analysis on a huge number databases, not designed properly, and
we must assume responsibility. My app will cycle through one at a time and
collect info. Unfortunately, when a database is opened with the MSAccess
application object, I cannot always kill startup instructions. I must copy
each one, delete properties and scripts, then open the copy. I would like to
just use DAO, containers, and documents if possible. Thanks

 

[Albert D.Kallal]

I would check the scanner tools that Microsoft came out with for ms-access.

The tool (which includes source) can scan all files (even discover on a
network), and then
report various things about the file (when last used, version etc.).

You can find this tool here:

http://www.microsoft.com/office/ork/2003/journ/accessconvert.htm
(the above access conversion kit does not convert the databases, but
does scan them for you). There is also all kinds of neat code in the
above utility. It would be a VERY GOOD starting point for the
kind of utility you are trying to write.

As for getting at the actual meta data for the forms and by-passing the
objects/collections, I am afraid this is not accessible from ms-access.
However, you CAN EXPORT the meta data as follows (just hit control-g to get
to the command line window, and type in the following:

saveastext acForm,"frmCustomers","c:\frmCustomers.txt"

The above will produce the same meta data that you see with a vb form.

However, I am still at a loss as to why you can't simply walk the forms
collection?


Last, but not least, you are aware the database documenter, and it also can
product a report of all forms, objects, and settings.
(tools->analyses->documenter). Try the documenter on one form to get a feel
as to what this thing produces..

Also, you do NOT have to use automaton, and create a instance of the
ms-access object to look at the collections.

Why not consider just creating a database object?

eg:


Dim db As DAO.Database
Dim strFromDB As String


strFromDB = "c:\my documents\test.mdb"

Set db = OpenDatabase(strFromDB)

MsgBox " tables in target = " & db.TableDefs.Count

db.close

The above did not create a 2nd instance of ms-access, and thus the startup
code is NOT run. You likely will find the above approach much better. Of
course, this 2nd approach means you can't use the saveastext method, as that
would requite automaton. However, I think creating a new database object is
FAR better then creating a new instance of ms-access, since with a new
instance of ms-access, you have the problem of startup coding running, and
the above database object does not cause this to happen.

At the end of the day, I would use the documents collections. Once you got
the documents collections, then you can actually transfer forms, or report
OUT OF the mdb file (presumably into a new temp mdb, and then examine the
forms, reports, or whatever you want. This would avoid the startup
problems..

DoCmd.TransferDatabase acImport, "Microsoft Access", strFromDB, acForm,
"frmTest", "frmTest"

Anyway, I don't see why you have a aversion to using the collections
objects, but you can use the un-documented saveastext, and it is a method of
the application object. Note that the reverse of this command is
loadfromtext.

And, take a look at the migration tool, as it does some very nice reports on
what is used, and then summaries this stuff in a report..

 

[David McDivitt]

Subject: Re: form code from document object
Date: Fri, 17 Jun 2005 14:37:41 -0600

I would check the scanner tools that Microsoft came out with for ms-access.

The tool (which includes source) can scan all files (even discover on a
network), and then
report various things about the file (when last used, version etc.).

You can find this tool here:

http://www.microsoft.com/office/ork/2003/journ/accessconvert.htm
(the above access conversion kit does not convert the databases, but
does scan them for you). There is also all kinds of neat code in the
above utility. It would be a VERY GOOD starting point for the
kind of utility you are trying to write.

As for getting at the actual meta data for the forms and by-passing the
objects/collections, I am afraid this is not accessible from ms-access.
However, you CAN EXPORT the meta data as follows (just hit control-g to get
to the command line window, and type in the following:

saveastext acForm,"frmCustomers","c:\frmCustomers.txt"

The above will produce the same meta data that you see with a vb form.

However, I am still at a loss as to why you can't simply walk the forms
collection?


Last, but not least, you are aware the database documenter, and it also can
product a report of all forms, objects, and settings.
(tools->analyses->documenter). Try the documenter on one form to get a feel
as to what this thing produces..

Also, you do NOT have to use automaton, and create a instance of the
ms-access object to look at the collections.

Why not consider just creating a database object?

eg:


Dim db As DAO.Database
Dim strFromDB As String


strFromDB = "c:\my documents\test.mdb"

Set db = OpenDatabase(strFromDB)

MsgBox " tables in target = " & db.TableDefs.Count

db.close

The above did not create a 2nd instance of ms-access, and thus the startup
code is NOT run. You likely will find the above approach much better. Of
course, this 2nd approach means you can't use the saveastext method, as that
would requite automaton. However, I think creating a new database object is
FAR better then creating a new instance of ms-access, since with a new
instance of ms-access, you have the problem of startup coding running, and
the above database object does not cause this to happen.

At the end of the day, I would use the documents collections. Once you got
the documents collections, then you can actually transfer forms, or report
OUT OF the mdb file (presumably into a new temp mdb, and then examine the
forms, reports, or whatever you want. This would avoid the startup
problems..

DoCmd.TransferDatabase acImport, "Microsoft Access", strFromDB, acForm,
"frmTest", "frmTest"

Anyway, I don't see why you have a aversion to using the collections
objects, but you can use the un-documented saveastext, and it is a method of
the application object. Note that the reverse of this command is
loadfromtext.

And, take a look at the migration tool, as it does some very nice reports on
what is used, and then summaries this stuff in a report..

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

Thanks Albert. There are a lot of nice tools out there, for sure, but I
always create my own tools. As I said, I do not want to load the MSAccess
application object if possible, but prefer to exploit
DAO/database/collection/document. Probably cannot do it.

My only alternate is to create a new database, use copyobject for everything
but the autoexec script, then analyse the copy through the application
object. I tried opening the database as a binary file and changing autoexec
to aut$exec wherever found, but the autoexec still runs.

 

[Paul Overway]

You cannot view/parse module code without an application object, i.e., the
form must be opened in design view. You're on the right track in respect to
walking the document collection.

Looking at your other post, I'd suggest opening the database in code, then
check/set properties to ensure that the database can be opened within the
Access application without running anything...so, you'd have to

1. Check MDE property...bail if equal T (the file may have MDB extension,
but is really MDE)
2. Check AllowBypassKey property...set to true
3. Check StartupForm property...clear if present
4. Rename Autoexec, if present (scan Documents("Scripts") for autoexec)

....then open the database in Access and do your stuff. You should probably
save the state of each of the properties/objects above before you make any
modifications...then restore them after your checks are finished.

'Air code follows

Set db = Opendatabase("somedatabase.mdb")

If db.Properties("MDE") = "T" Then
'Nothing to do
End If

If db.Properties("AllowBypassKey")=False then
'Save current setting
'Change to True
End if

If Len(db.Properties("StartupForm"))>0 Then
'Save current setting
'Change to ""
End if

If Len(db.Containers("Scripts").Documents("Autoexec").Name)>0 Then
'Rename macro
End if

etc.

 

[David McDivitt]

Subject: Re: form code from document object
Date: Fri, 17 Jun 2005 17:48:55 -0400

You cannot view/parse module code without an application object, i.e., the
form must be opened in design view. You're on the right track in respect to
walking the document collection.

Looking at your other post, I'd suggest opening the database in code, then
check/set properties to ensure that the database can be opened within the
Access application without running anything...so, you'd have to

1. Check MDE property...bail if equal T (the file may have MDB extension,
but is really MDE)
2. Check AllowBypassKey property...set to true
3. Check StartupForm property...clear if present
4. Rename Autoexec, if present (scan Documents("Scripts") for autoexec)

...then open the database in Access and do your stuff. You should probably
save the state of each of the properties/objects above before you make any
modifications...then restore them after your checks are finished.

'Air code follows

Set db = Opendatabase("somedatabase.mdb")

If db.Properties("MDE") = "T" Then
'Nothing to do
End If

If db.Properties("AllowBypassKey")=False then
'Save current setting
'Change to True
End if

If Len(db.Properties("StartupForm"))>0 Then
'Save current setting
'Change to ""
End if

If Len(db.Containers("Scripts").Documents("Autoexec").Name)>0 Then
'Rename macro
End if

etc.

I am not able to rename macros through code, through
database/collections/documents. I need to open the database first with DAO
before opening with a new application object. All I can do it seems is use
copyobject, put everything in a temporary database, and analyse the
temporary database.

 

[David McDivitt]

Subject: Re: form code from document object
Date: Mon, 20 Jun 2005 08:57:24 -0500

I am not able to rename macros through code, through
database/collections/documents. I need to open the database first with DAO
before opening with a new application object. All I can do it seems is use
copyobject, put everything in a temporary database, and analyse the
temporary database.

The copyobject method is in the application object, too. I thought it was in
the database object. Oh well. I guess I can insure the bypass key property
is OK and stuff a shift key when opening a current database with the
application object.