Finding the primary key of a form's underlying record source 3

[ALSav]

I am attempting to write some code that will return the name of the primary key field(s) of a form's underlying table/record source and also the table's name.

Reason:
I'm trying to create a log file which will store changes made to a number of fields in the database. These fields all come from separate tables and I already have a log for each individual table but would quite like to pool the data in one file. Hence the need to know which table is involved and the names of it's primary key field(s).

Any ideas?

 

[TAMSIN]

Hi,

Don't if this is quite what you want, but maybe you can modify it to get the results you need. I put this in the On Click Event of a button on a form, and it puts up messages showing the tablename and fields which make up the primary key. Not sure what you'd do if your form was based on a query rather than directly on a table.

Private Sub Command4_Click()
Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim idx As Index
MsgBox "table name is " & Me.RecordSource
Set db = CurrentDb()
Set tdf = db.TableDefs(Me.RecordSource)
   
For Each idx In tdf.Indexes
    If idx.Primary Then
        For Each fld In idx.Fields
            MsgBox "primary key is " & fld.Name
        Next
    End If
Next

End Sub

HTH
Tamsin

 

[MichaelRed]

It's rather contrived, but you can get the RecordSource for the form. If it a table, then the controls with just a fieldname as the controlsource are form the table. For controls w/ a more complex controlsource (I think theese will all have "=" as the first charof the control source), you must parse the controlsource to determine the actual "table".

For Forms where the Recordsource is NOT a table, you will need to delve MUCH deeper.

Check to see if the recordsource is a Table, Query, or SQL statement.

To accomplish this:

SQL Statements always start and end with quotation marks.

If it not an SQL Statement then Itterate through the querydefs collection, attempting to match the recordsource to a name. If you find a match, the querydef's SQL property will be the SQL Statement.

If the recordsource is NOT an SQL statement or a querydef, it must (or at least should be) a table. Itterate through the Tabledefs, checking for a match. When (if?) found, the Tabledef is the control source. The Tabledef properties (Field.Name) are the field names.

If the record source is not an SQL Statement, A querydef or a tabledef, there is a problem.

If the recordsource is a table def, each BOUND controls controlsource should be a field (from the recordsource), so your log just uses the recordsource and controlsource names.

If the recordsource is an SQL statement or a querydef, you need to recursively parse the SQL statenment to identify the fieldnames and their recordsources until you have bored down to tables and fieldnames. This sound harder than it is, nostly because really complex queries do not usually support updating recordsets, so in general, even where forms recordsources are SQL statements of queries, the info comes form one table anyway.

Your database may not do this, but in some cases, forms are based on 'temporary' recordsets. In these instances, the recordsource is a table - however looking at the process, the recordset is cleared, an Update or append query re-populates the recordset, the form is shown (possibly some edits are made to the {temporary recordset}) and - perhaps the recordset is used to update one or more tables. In these instances, nothing above will give you any clue as to the actual transaction(s) is not available.

For a more or less simplistic approach, the IsDirty property of the controls checked in the beforeupdate event does provide the hooks to be able to log the recordsource, controlsource, usename, formname, date/time ... etc. You could just log this info, and only attempt the more difficult traciing on demand. The advantage here would be that you can start to capture each transaction/change more quickly, while working out the more difficult 'tracing' to table records at a more leisurely pace.

A caution regarding all of the above. The transaction log will grow MUCH faster than the basic data tables. You need to START with a plan to prune the transaction log either by 'archiving' old entries (seperate database please!) or just deleting 'old' records.
If it is, the various fieldnames and their sources are included, so you need to find out each source (From Clause) and each fieldname (be especially careful of 'alaises' {e.g. "As" clauses}). Persue each of the sources recursively until every field is resolved to a specifc table/field or is found to be a calculation.

Another caution. Any/every sophisticated user can change data in Ms. Access tables w/o the use of forms. Since Ms. Access does not have any 'triggers' at the table level, the 'transaction log' CANNOT be used as an absoloute guarntee that all changes are identified.




MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over