Querying form object attributes (2000)

Topic

New Reply

Hi all:

I’m very new to VB and Access, and have a need to query the attributes of objects in Access forms. Is this possible, and how would you recommend I go about it.

A table in the database lists the forms I am concerned with, but that is all I know. I need to determine what objects exist on the form, obtain the object attributes, and save this information to a flat file.

Reply | Quote

Replies

Could you be more specific about what kind of information you’re looking for? You can use VBA code to examine each of the controls on a form and for each control, you can look at the properties. This is essentially what’s being done when you use the database documenter found under Tools–>Analyze. You can certainly query systems tables, but translating the results into something comprehensible would require more than a simple query. And by a “flat file”, do you mean a text file or do you mean a true flat file, that is, something along the lines of an Excel spreadsheet?

Reply | Quote

First the easy part. By ‘flat file’ I mean a text file. I need to extract the information below from the database in two passes, once before a software installation, and once after. I’m in the QA department in need to verify that an installation of our software (which updates the database) did not cause any corruption to the users forms.

From the command line, I need to query a table to get the names of the users custom forms. Now for the hard part, without knowing the structure of the users custom forms, I need to query the database (system tables, I guess), to find all the objects, on each of the users custom forms and retrieve all available attributes, and their values. I need to save this information to a text file. Once again, I need to do this from the command line or via a VB executable.

Reply | Quote

(Edited by MarkD on 01-Jan-03 05:52. Minor bug fix.)

A form must be open to retrieve most form properties. Open forms are found in the Forms collection. Here is example of code that prints all properties of all open forms to Immediate window:

Public Sub PrintOpenFormProps()
On Error GoTo Err_Handler

‘ Form has to be open to be access Form properties

Dim frm As Access.Form
Dim intCount As Integer
Dim n As Integer
Dim strMsg As String

For Each frm In Forms
intCount = frm.Properties.Count
Debug.Print “FORM NAME: ” & frm.Name
For n = 0 To intCount – 1
Debug.Print vbTab & frm.Properties(n).Name & “: ” & _
frm.Properties(n).Value
Next n
Debug.Print
Next frm

Exit_Sub:
Set frm = Nothing
Exit Sub
Err_Handler:
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “PRINT FORM PROPERTY ERROR”
Resume Exit_Sub

End Sub

Note – If more than a few forms are open, you may not be able to read all info in Immediate window. A form also has DOCUMENT properties which can be retrieved from the database Forms container (not to be confused with Forms collection, which includes OPEN forms only). A form does NOT have to be open to retrieve document properties. Sample code that prints all form document properties to a text file:

Public Sub PrintFormDocPropTextFile()
On Error GoTo Err_Handler

‘ Form document properties – database Forms container
‘ Form does not have to be open

Dim db As DAO.Database
Dim doc As DAO.Document
Dim strPath As String
Dim strProjName As String
Dim strFileName As String
Dim i As Integer ‘Doc index loop counter
Dim j As Integer ‘Prop index loop counter
Dim strMsg As String

Set db = CurrentDb
strPath = Access.Application.CurrentProject.Path & “”
strProjName = Access.Application.CurrentProject.Name
‘ Assume db file name ends in “.mdb”:
strProjName = Left(strProjName, Len(strProjName) – 4)
strFileName = strPath & strProjName & “_FormDocProp.txt”

Open strFileName For Output As #1
Print #1, “Project Name: ” & strProjName
Print #1, “Project Folder: ” & strPath
Print #1, “”
For i = 0 To db.Containers(“Forms”).Documents.Count – 1
Set doc = db.Containers(“Forms”).Documents(i)
Print #1, “FORM: ” & doc.Name
For j = 0 To doc.Properties.Count – 1
Print #1, vbTab & doc.Properties(j).Name & “: ” & _
doc.Properties(j).Value
Next j
Print #1, “”
Set doc = Nothing
Next i
Close #1

Exit_Sub:
Set db = Nothing
Set doc = Nothing
Exit Sub
Err_Handler:
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “PRINT FORM DOC PROP TEXTFILE ERROR”
Resume Exit_Sub

End Sub

Note: Text file saved in same folder where database resides. I tested this code within currently open database & works OK. As far as being able to do this from a command line or VB executable, I’ll leave that to you as an exercise (or adventure) in programming. Some code would have to be modified. Obviously you won’t be able to capture open form properties, but you should be able to capture the document properties externally. It might be easiest to do this from another Access database.

HTH

Reply | Quote

(Edited by MarkD on 02-Jan-03 19:47. Deleted attachment, revised zip file attached to new post.)

In further reply, you can print another database’s form document properties to a text file from command line by following these steps. Create a new, blank database. Add standard code module with revised procedure:

Public Sub PrintFormDocPropTextfileEx(ByVal strDbPath As String)
On Error GoTo Err_Handler
‘ strDbPath = full path & filename of external database file

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim doc As DAO.Document
Dim strPath As String
Dim strProjName As String
Dim strFileName As String
Dim i As Integer ‘Doc index loop counter
Dim j As Integer ‘Prop index loop counter
Dim strMsg As String

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(strDbPath)

strPath = Left(strDbPath, InStrRev(strDbPath, “”, , vbBinaryCompare))
strProjName = Right(strDbPath, Len(strDbPath) – InStrRev(strDbPath, “”, , vbBinaryCompare))
‘ Assume db file name ends in “.mdb”:
strProjName = Left(strProjName, Len(strProjName) – 4)
strFileName = strPath & strProjName & “_FormDocProp.txt”

Open strFileName For Output As #1
‘ Use same code here to print file!
Close #1

Exit_Sub:
If Not db Is Nothing Then db.Close
Set ws = Nothing
Set db = Nothing
Set doc = Nothing
Exit Sub
Err_Handler:
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “PRINT FORM DOC PROP TEXTFILE ERROR”
Resume Exit_Sub

End Sub

Then create a new form, with following event procedures:

Private Sub Form_Load()
Dim strDbPath As String
strDbPath = Command()
PrintFormDocPropTextfileEx (strDbPath)
DoCmd.Close acForm, Me.Name

End Sub

Private Sub Form_Close()
Application.Quit
End Sub

Finally, create new macro with one action: OpenForm, specify name of form. Save macro as “AutoExec”. This macro will run automatically when database is opened (use Shift key to open database w/o macro being executed). When database is opened, the form opens, runs procedure, closes, and quits Access. The Command() function returns the argument portion of the command line used to launch Access. Use the /cmd command line option to specify full path of database to be documented; this must be last option on command line. Full path to MSACCESS.EXE must be specified. Include quotation marks. Example:

“C:Program FilesMicrosoft OfficeOffice10MSACCESS.EXE” “C:ACCESSGetDbProp.mdb” /cmd “C:ACCESSNorthwindTest.mdb”

The command line shown above can be run from a shortcut or from the Windows Run dialog.

PS: Revised sample DB attached to new post.

HTH

Reply | Quote

Taking another look at this, modified previous code to export document properties for ALL database objects to text file. This seemed more useful than exporting just form properties. Revised sub loops thru all containers in current or external database and exports specified document properties to text file. As the Tables container contains all saved tables, queries, and SQL statements, the MSysObjects system table is queried to ascertain correct object type for each document in Tables container. Revised code is too lengthy to post, see code module in sample db (A2K format) in attached zip file if interested. To run procedure in current db, use this syntax:

ExportObjDocPropsToTextfile CurrentDB.Name, 1

The first argument is full path of database to be documented, 2nd argument is integer indicating whether current db or another db is being documented. Example of 2nd option (from attd file startup form):

Private Sub Form_Load()

Dim strDbPath As String
strDbPath = Command()
If Len(strDbPath) > 0 Then
ExportObjDocPropsToTextfile strDbPath, 2
End If
DoCmd.Close acForm, Me.Name

End Sub

Note added test to see if command line argument present so sub doesn’t run when you open db w/o command line. You’d run this from command line same way, as the startup form provides the 2nd parameter. Ex (using A2K default path for MSACCESS.EXE):

“C:Program FilesMicrosoft OfficeOfficeMSACCESS.EXE” “C:ACCESSGetDBProp.mdb” /cmd “C:ACCESSNorthwindTest.mdb”

Likewise modified form’s Close event:

Private Sub Form_Close()
If Len(Command()) > 0 Then
Application.Quit
End If
End Sub

This prevents app from quitting when form closed unless opened with command line argument. It probably wouldn’t be difficult to adapt this for a VB 6.0 project, but it seemed simpler just to use an Access .mdb for this purpose.

HTH

Reply | Quote

Mark:

My previous post was in response to your message 209614. I just got your post from earlier today. Will look at your attachment. My answer is probably there.

Reply | Quote

Thanks Mark. I’m very inexperienced at this so please bear with me. How does this give me the properties of the objects on the form, and their attributes?

We give our customer a database. They are allowed to create forms in the database. When we ship them a new release, our installation procedure is responsible for installing the latest version of the database, and migrating the customers forms to the latest release of the database. My job is to check that the migration of the dataforms from the old database to new version of the database did not cause any corruption to the forms the customer created. The thought I had was to our table where the customer is expected to report the custom forms they have created. Then query the properties of each form, AND the objects on these forms for their attributes before and after a product upgrade and make sure that nothing about the customers forms changed.

To be quite honest, I don’t need to what changed, just that something is not the way that it used to be before the upgrade process. The MSysObjects table has a ‘Data’ column. I’m hoping that it contains the details of form objects and their attributes. It would be just fine with me if I could query the ‘data’ and ‘id’ columns of MSysObjects before and after, and complaining if the two are different.

Reply | Quote

Change references to MSysObjects to MSysAccessObjects. That’s the table that has the ‘data’ and ‘id’ columns I was talking about.

Reply | Quote

You will have to explain to me what use it would be to query the MSysAccessObjects system table. The table has 2 fields, ID (Long) and Date (OLE Object), the latter displayed as “Long Binary Data” in datasheet view. Try exporting this table to a text file & see what happens. Not that the results would be meaningful even if it worked. The MSysObjects table identifies each object by type (a form is type -32768) so you could query this table but don’t know if this would serve your purpose.

Other option would be to loop thru each form in database, & in turn loop thru all controls on each form and print form & control properties to text file. Is there some reason you don’t use the built in Documenter (Tools menu, Analyze) for this? It provides more than enough detail, and the report it generates can be exported to a text file (a lengthy one, if there are a lot of forms in db). I don’t think there’s a way to run the Documenter from a command line, you have to open the db to run it.

Having customers create their own “custom forms” & “migrating” these forms to new version of program sounds like a recipe for disaster to me. You’d be better off providing them their own front-end for “custom forms” and any other clever “experiments”. Do these “custom forms” perform data validation, etc?? If a separate front-end was provided you wouldn’t have to worry about comparing the forms before & after upgrade in the first place.

Reply | Quote

Thanks again for the responses. Where does one begin….
My job as a QA automation engineer is to develop an automated mechanism (hence the command line, because it needs to tie into our existing automated strategy) to ensure that forms in an Access DB migrate correctly with each release of our product. Our product is not an access product, it does have a front, and uses access to present host data to the client in forms they are comfortable with. Customers create forms tied to data in a fixed set of tables they are not permitted to modify. They are not allowed to write macros or create modules. Whenever they create a form, they are required to create a record in a specific table so we know that a custom form has been created. It gives them a warm fuzzy feeling to fill out a loan application on that looks exactly the same on the computer as the paper version they’ve been filling out for years. They create a form, scan in an image (actually they pay others to do this for them), place transparent fields where data is entered/displayed, and now they can process the form electronically. The data is retrieved from the host and temporarily stored in this Access DB. The forms they create autofill with certain data from the tables. Data that is entered into the forms is stored in the tables (and eventually pushed onto the host). Forms may contain user defined fields that are based on other fields on the form, or on other custom forms. Supporting tables exist to allow this to work. Access is not being used to store real data, it’s merely being used as a conduit to allow our brain dead kludgy system to appear high tech.

Now back to my issue. Mark, I think you hit what I was looking for, (Charlotte, I know you know this also but I did not make myself very clear). I need to “loop through each form in the database, and in turn loop through all controls on each form”. Correct me if I’m wrong, button, edit boxes, checkboxes, images are all examples of controls, and each has properties associated with it? I call them objects, but controls works just fine. Okay, so how do I write a C/C++ console application that can open an access database, select from a table that contains the names of the users forms, obtain all properties of all objects on this set of forms, and save it to a text file or another database (which the application could create) for later comparison. Again, remember I’m in QA, extract the info, run the upgrade process, extract the info, and compare the before and after data. I would write the application to read command line operators because the upgrade process is an independant task. The command line to provide the name of the datafile to be created or compared.

With regard to MSysAccessObjects. I still don’t know the intended purpose of the table, but it seemed like it might be the repository for object/Control information. That’s why I wanted to query it. As for the data type, I don’t care since I’m interested in a comparison before and after, and all I need to do is throw up a flag if the two are different. So what information does MSysAccessObjects hold. Looping through each control would add the additional overhead of querying the control type, and then getting the property values for controls of that type. If there was a way to obtain in bulk all properties of all controls on a given form, that would be just perfect. I just need to barf if something has changed, the ‘what’ does not matter (at this time). Let somebody else figure that out later.

Reply | Quote

I’ve been following this thread with considerable puzzlement, and I wonder if there isn’t a basic disconnect here. Are you certain that new forms are actually being created in Access? To do this in Access is a very complex task, and would require a great deal of VBA code, and intimate knowledge of the entire Access database. It would also appear to require some sort of user interface to create the form, which is even more complex. I’m not saying it isn’t possible, but I only know of a few companies that would be capable of doing such a development. If that sort of development has been done, why didn’t they develop something that really takes advantage of Access? It almost certainly would have cost less! So pardon my scepticism, but something just doesn’t sound right.

Finally, as an aside, it would be pretty easy to determine who created a given form, as the security model in Access has owner identification in it. Since you must surely be using that model to prevent users from creating query, code, reports, etc, it would be straightforward to prevent users from modifying your forms, or prevent your developers from modifying their forms. So why the worry about modifying user forms – your stand a much larger risk of corruption at a given customer installation than you do from the source you are concerned about. I certainly agree with Charlotte and Mark that this business scenario is a recipe for disaster big time if indeed we have correctly understood the situation.

Reply | Quote

Gramps, welcome to the conversation. I’m going to feel like a total idiot if I’m in the wrong room altogether. I keep making confessions as to the wisdom of the technology I support, but yet we seem to be stuck on why can’t you do it some other way. You are all correct, Microsoft Access 2000 (am I in the correct forum?) is not being used as it might. The tables in the DB are empty, they get populated by our client application when a user elects to enter data into their custom forms. I should not have used the phrase “nothing about the customers forms changed”. I’m not concerned about security. I tried to explain, that my job is to test the darned product, specifically installation testing. The customers forms are getting corrupted during the product upgrade process. During the installation/upgrade, the exising DB is saved, a new one dropped onto the target machine, and an Access Macro is run. The macro imports the users custom forms from the old database, and we’re in business. I need to devise an automated way to extract the definition of the users forms (controls and properties of controls) and verify that they remain the same before and after running the product upgrade setup process.

I’m not sure what sorts of forms you’re thinking of, but ours are very simplistic. Like I said in a previous message, the purpose is simply to allow our users to fill out information on screen that they are used to filling out on paper. The tables simply serve as a conduit to the host database which is actually something called UniData. Before you ask, there is not OBDC mechanism to talk to UniData.

Hope that clarifies the situation some.

Reply | Quote

That does help, but there is still some mystery here. But you are in the right forum (I think). In the first place, importing forms from the old database should never change anything unless you are changing version of the Access database – i.e. from 97 to 2000. We routinely use that process to correct problems with corrupt databases, and in 10 years I have never seen a user form change. The only other possibility I can think of is actual failures on the network being used, but that should give you corrupt databases with regularity.

The second question is how the client builds forms – you indicate that they scan documents in, but Access forms are not bit-mapped objects. Unless they actually put an image control over the entire form, and then plop transparent text boxes with no borders, and do all of the form creation manually, I don’t understand how the forms get created. In addition, the process I described would be subject to all sorts of problems with video card drivers, screen resolutions and so forth.

Third, if you don’t have Access User Security active, how do you keep users from deleting editing your forms, reports, queries and so on? I understand your job as a tester, but the source of form corruption could be any number of things in addition to some sort of issue with your product deployment. I really think you’ve got a mess, and QA is not the answer to solving your problem (in a former life I spent a number of years working with the Bell Labs QA organization).

Finally, you might find the FMS product line to be of value – in particular their Total Access Detective will identify differences between objects or between databases. We use their tools regularly in the development and deployment process and have found them to be invaluable. You have a most interesting situation to deal with.

Reply | Quote

This wouldn’t be enough even if it worked. They create or customize forms? In that case, you also need to be sure any custom queries or tables are migrated, not just work some magic to see what has changed in the forms. As Mark suggests, this approach is sure to lead to problems. If the user can modify the application, then you can’t just look at forms. You need some way to determine if *anything* in the application has been changed. Since you are trying to update the front end at the same time, what are you going to do about necessary changes that conflict with whatever the user might have done to an existing form, report, query , etc.? Whoever decided that this was the way it should be is most definitely NOT an Access developer or they would have known that this approach would lead to disaster sooner or later. Take Mark’s advice and give them their own separate front end that they can play with but lock down the standard front end and don’t allow them to modify it.

Reply | Quote

After poking around the database for a while I may have an adequate solution (no code, just a concept at this time). There is a system table MSysAccessObjects. I suspect the ‘Data’ column of this table contains information about all objects in the database including all forms. The database has a table that identifies all the users custom forms. Now if I could just find a way to correlate these two pieces of information, I’d be in great shape.

I notice the table MSysObjects that contains a Name field that appears to contain a few rows with the names of some custom forms. I’m guessing that’s my correlation.

Now the key question is whether the ‘Data’ column in MSysObjects does in fact contain a codiefied representation of the objects (and their attributes) in my users custom forms. If so, I’m done. I’m not interested in the specifics of each object and it’s attributes. I just want to make sure that they were not altered by the installation of a new release of our software. Where I might get busted is if the codified representation includes references to other objects whose identity may be legitimately altered during the installation process, in which case, my test would result in a false positive.

Cheers, and a happy 2003 to those of you who are already there.

Reply | Quote