Re: Different BE database uniformity.

Phil Stanton wrote:

Thanks Salad That looks great. Will try it tonight.
Probably refine it to have all the info for all the DBs in TblCurrent with a pointer to which DB they come from. Should be very easy to compare

Yeah, if you did a Totals query, exluding the table, if you had 5 MDBs then you should have a count of 5 for everything. Anything not 5 would be an exception.

Any thoughts on the relationship part of my question?

I missed that one. Here's some code from OnLine help.

Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set rstProducts = dbsNorthwind.OpenRecordset("Products")
' Print a report showing all the different parts of
' the relation and where each part is stored.
With dbsNorthwind.Relations!CategoriesProducts
Debug.Print "Properties of " & .Name & " Relation"
Debug.Print " Table = " & .Table
Debug.Print " ForeignTable = " & .ForeignTable
Debug.Print "Fields of " & .Name & " Relation"
With .Fields!CategoryID
Debug.Print " " & .Name
Debug.Print " Name = " & .Name
Debug.Print " ForeignName = " & .ForeignName
End With
End With


I think by modifying the code above you could add it to my routine and insert the data into, most likely, a new table.


Thanks again

Phil


"Salad" <oil@xxxxxxxxxxx> wrote in message news:nICdnUYpGO-dSRTVnZ2dnUVZ_qfinZ2d@xxxxxxxxxxxxxxxx

Phil Stanton wrote:

I have 1 FE database which can be linked to a number of BE databases. The BEs are all different Clubs e.g. Sailing Club, Yacht Club, Bridge Club, Family etc.

Problem is if I change anything in the table structure or relationship of one of these clubs, I want to make sure I do it with the other clubs as well.
Has anyone got routine that will check that each BE database has
1) All the tables (even though some will be empty e.g. Bridge club has nothing to do with boats)
2) The fields are all the same size, default values are the same, Indexes are the same etc
3) Relationships are identical

I am struggling to do it manually at the moment and I know it is very error prone

Thanks for any advise

Phil

Hi Phil. Here's a routine I made up. I created two tables; TableCurrent and TableExternal. Three fields; TableName, FieldName, FieldType. You could add FieldSize as well and add that in the Insert statement and insert the Size property as well. Change the External mdb name. Then run. After that you can create some queries to check for differences between these two tables.

Sub CheckFieldDifferences()

Dim dbs As Database
Dim dbsExternal As Database
Dim wsp As Workspace
Dim tdf As TableDef
Dim fldCurrent As Field
Dim fldExternal As Field
Dim strSQL As String

Dim strExternal As String
strExternal = "C:\Test\Db1.MDB" 'CHANGE TO THE EXTERNAL FILE NAME

' Return reference to current database.
Set dbs = CurrentDb
' Return reference to default workspace.
Set wsp = DBEngine.Workspaces(0)
' Return reference to External.mdb.
Set dbsExternal = wsp.OpenDatabase(strExternal)

' Enumerate all TableDef objects in each database.
'Debug.print dbs.Name & ":"
For Each tdf In dbs.TableDefs
'Debug.print tdf.Name
If Left(tdf.Name, 3) <> "tmp" Then
For Each fldCurrent In tdf.Fields
'Debug.print fldCurrent.Name, fldCurrent.Type
strSQL = "Insert Into TableCurrent (TableName, FieldName, FieldType) Values " & _
"('" & tdf.Name & "', '" & fldCurrent.Name & "', " & fldCurrent.Type & ")"
dbs.Execute strSQL
Next
End If
'Exit For
Next tdf
'Debug.print

'Debug.print dbsExternal.Name & ":"
For Each tdf In dbsExternal.TableDefs
'Debug.print tdf.Name
If Left(tdf.Name, 3) <> "tmp" Then
For Each fldExternal In tdf.Fields
'Debug.print fldExternal.Name, fldExternal.Type
strSQL = "Insert Into TableExternal (TableName, FieldName, FieldType) Values " & _
"('" & tdf.Name & "', '" & fldExternal.Name & "', " & fldExternal.Type & ")"
dbs.Execute strSQL
Next
End If
Next tdf
Set dbs = Nothing

dbsExternal.Close
Set dbsExternal = Nothing
MsgBox "Done"

End Sub

Voqa Mosimosi
http://www.youtube.com/watch?v=EQJr1LUmfLM



.

Relevant Pages

  • Re: Is it possible...
    ... Dim dbs As DAO.Database ... Dim strSQL As String ... EXCEL file in the same order as the fields in the permanent table. ...
    (microsoft.public.access.externaldata)
  • Re: Different BE database uniformity.
    ... The BEs are all different Clubs e.g. Sailing Club, Yacht Club, Bridge Club, Family etc. ... Dim dbsExternal As Database ... Dim tdf As TableDef ... Dim strSQL As String ...
    (comp.databases.ms-access)
  • RE: ODBC Order by question
    ... Dim dbs As Database ... Dim strSQL As String ... Dim qdfNew As QueryDef ...
    (microsoft.public.access.modulesdaovba)
  • Re: Crosstab Parameters
    ... Dim dbs As DAO.Database ... Dim rst As DAO.Recordset ... Dim strSql As String ... Set dbs = CurrentDb ...
    (microsoft.public.access.modulesdaovba)
  • Re: Different BE database uniformity.
    ... BEs are all different Clubs e.g. Sailing Club, Yacht Club, Bridge Club, Family etc. ... Dim dbsExternal As Database ... Dim tdf As TableDef ... Dim strSQL As String ...
    (comp.databases.ms-access)