Have the situation where a database backend is on a number of sites and if changes have to be made to the design structure of the backend database I would like to run a VB generated report to check that all indexes are correct. While this is not an issue with single field indexes, I have issues with multi field indexes.
RuralGuy, unfortunately that is not correct. Compact and Repair does not necessarily reindex an existing, it only rearranges the placement of the index and updates the stored statistics that are properties of the index. That is not quite the same thing.
The only way to be absolutely sure that an index is correct is to remove it and then reassert it. If the table in question participates in relationship with relational integrity enabled, this becomes a serious activity because you ALSO have to whack the relationships.
I don't know of any report you could devise that validates an index. I looked through the help files first before answering this. There are no "validate index" methods associated with index objects. Which means that if they exist, there is no public interface to them.
What I am wanting to do is compare the index's between databases on two different computers after doing a design change on the master. Looking for code that will display the index's fields name, including when there is an index that is of a multi-field type.
I presume one needs to loop through the index field names to get the info. Also need to get sort details and make sure that the fields are reported in the correct order.
Does any one have idea's on the code needed to report this.
----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.mspx
----------
Since the OP is specificly asking for code samples, I was really looking forward to seeing how you do that, as I assumed you delivere source code with your tool. It might be something wrong with the download, as I wasn't able to browse the code.
If you don't deliver sourcecode, I find it understandable that RuralGuy didn't link to it
I haven't had the need to do something like this, yet, so I haven't got any tested code ready, so the below are "air code", either lifted off the net, or just "had a look at", but using DAO, the steps could probably be to loop the indices collection (Indexes), then per each index, loop it's field collection. Here's a little sample found when googling
Code:
Dim db As DAO.Database
Dim tdef As DAO.TableDef
Dim MyIndex As DAO.Index
Dim MyField As DAO.Field
Set db = OpenDatabase("Test.mdb")
Set tdef = db!UserData
For Each MyIndex In tdef.Indexes
Debug.Print MyIndex.Name
For Each MyField In MyIndex.Fields
Debug.Print "contains: " MyField.Name
Next
Next
This, you would probably need to compare with your "master".
In Allen Brownes article about "Relationship Report with extended field information", http://allenbrowne.com/AppRelReport.html, he gives the code to do so. If you check out the explanations on how he loops the indices (Indexes) collection, and defines whether or not this is the first field of the index (can be expanded, I think).
Using ADO, you could check out the OpenSchema method of the connection, perhaps in particular the adSchemaIndexes SchemaEnum. For multifield indices, it will return one record per each field in the index (having the same TABLE_NAME and INDEX_NAME), you can check other properties too, and the ORDINAL_POSITION field, should return it's position within the index. Small sample
Code:
dim cn as adodb.connection
dim rs as adodb.recordset
dim i as long
set cn = new adodb.connection
cn.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<path and name to db.mdb>"
' more connectionstrings
' http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForMicrosoftJet
cn.open
set rs = cn.openschema(adschemaindexes)
' for specific tablename, you could
' set rs = cn.openschema(adschemaindexes,
' array(empty, empty, empty, empty, "YourTable"))
do while not rs.eof
for i = 0 to rs.fields.count-1
if not isnull(rs.fields(l).value) then
debug.print rs.fields(l).name, rs.fields(l).value
end if
next i
debug.print
rs.movenext
loop
Hope any of this can provide a starting point for what you need.
Thanks for the examples given, This has given me someting to work on.
I have been using DAO to get out the field names for the indexs and was endeavouring to have code printout the Multi-Field name along with the sort order for that index.
Wanted to make something that looked like the Access Graphics so I could take it to the other site and us it to make the changes.
