Table Analyser and compair versions analyser

[Dreamweaver]

Now see, I am a firm believer of not re-inventing the wheel.

I'd be more than happy to contribute to such a project if I had the skills and offer suggestions for anything I thought would also be useful rather than start from scratch.

I do understand that gasman but I need this to do a spacific job and I also want to learn from doing, I'm trying to play catch up at the moment.

 

[Dreamweaver]

Thanks Majp Some of them look interesting that analyser was built in 2005 so is due for an upgrade

 

[MajP]

If you are using DAO there is a ton of information that can be returned. One thing to keep in mind is the Attributes and Properties collection. There is more stuff in those as well.

https://docs.microsoft.com/en-us/of.../desktop-database-reference/field-members-dao

These can be confusing. Some properties are directly exposeable. For example in a tabledef the Name or RecordCount through the tabledef object. Other properties can only be found in the properties collection. When you provide a value to these properties it creates the property in the collection and gives it a name and a value. For example in a tabledef there is a Description box to enter a description of the table. If you put in a Description it creates that property in the collection with a name "Description" and a value of your text. There is not an existing property called Description. If you were to remove the value in the description box it does not assign the description property a value of "" but actually removes the property from the collection.
So to get the table Name you can do it directly
debug.print tdf.Name
To get the Description you would have to do it through the properties (and only if one exists)
debug.print tdf.properties("Description").value

So that is what gets tricky with the properties collection. If you loop the properties you are not going to see every potential property, but only those with a value (and those always exposed).

There may be a lot in the system tables, but I find DAO very easy because it is extremely well documented on the MSDN sites and others.

 

[Dreamweaver]

The program I'm building will compair 2 verions of same datafile so only the properties created at the time of running the analyser are of interest but i would check for extra properties or missing ones between the 2 versions.


I will try rewriting the field section that writes each field to a table


I've posted a load of screenshots here:
https://databasedreams.createaforum...table-analyser-and-compair-versions-analyser/

 

[MajP]

Not sure if this is what you are asking about linking the index to the fields.
I have three indexes in table1. One has two fields

Public Sub DoIndexes()
Const tbl = "table1"
Const objID = 1
Dim CDb As DAO.Database
Dim db As DAO.Database
Dim Idx As Index
Dim tdf As TableDef
Dim Prop As Property
Dim m_RecIndex As DAO.Recordset
Dim fld As Field

Set db = CurrentDb()
Set tdf = db.TableDefs(tbl)


Dim strIDX As String
Dim StrFields As String
For Each Idx In tdf.Indexes
  strIDX = Idx.Name
  StrFields = ""
  For Each fld In Idx.Fields
    If StrFields = "" Then
      StrFields = fld.Name
    Else
      StrFields = StrFields & "; " & fld.Name
    End If
  Next fld
  Debug.Print "Index Name:" & strIDX & "     Fields: " & StrFields
Next Idx
End Sub

Index Name:Budget Remaining Fields: imgID_Fk; Remaining
Index Name:NewKey Fields: budget
Index NamerimaryKey Fields: ID

 

[Dreamweaver]

Yes Majp thats exactly what I've been tryng to do Can't thank you enough Will Play and see if I can get it working with my system thanks again

 

[MajP]

Along with the one already mentioned, here is another one. There is a lot of code that could be reused if you are building something for yourself.

http://mdbdoc.sourceforge.net/

 

[Dreamweaver]

This might explain what I'm doing I have most of the field names just special field like primary key as shown in attached, In the instance of Atblpayables the primary key name is billID
I can adapt your function to return the primay keys fields name now

 

[isladogs]

Gina,
Just got off Skype with Crystal. What a lovely lady.
She wanted to get the problem sorted immediately ....

Gasman
I've just downloaded Crystal's analyser but not yet tried it
When you were speaking to Crystal, did she indicate whether there was an issue in her original code such that it needed updating?

 

[MajP]

the field names just special field like primary key as shown in attached, In the instance of Atblpayables the primary key name is billID

Just to be clear that is not the field name being shown, but the index name. Any of those indices including Primary Key can have multiple fields. An index has a fields collection for each field in the index.