Different BE database uniformity.

[Phil Stanton]

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

[paii, Ron]

Re: Different BE database uniformity.

Any reason you can't combine the back-ends databases? Some table restructure
adding keys to identify the club would allow you to switch without
re-linking to a new back-end.

Otherwise, write some VBA code to make the changes, then run it on each
back-end.

"Phil Stanton" <phil@myfamilyn ame.co.ukwrote in message
news:uYydnQj6Lb EzKRTVnZ2dnUVZ8 gOdnZ2d@posted. plusnet...

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
>
>

[Salad]

Re: Different BE database uniformity.

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 CheckFieldDiffe rences()

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.MD B" 'CHANGE TO THE EXTERNAL FILE NAME

' Return reference to current database.
Set dbs = CurrentDb
' Return reference to default workspace.
Set wsp = DBEngine.Worksp aces(0)
' Return reference to External.mdb.
Set dbsExternal = wsp.OpenDatabas e(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.Nam e & ":"
For Each tdf In dbsExternal.Tab leDefs
'Debug.print tdf.Name
If Left(tdf.Name, 3) <"tmp" Then
For Each fldExternal In tdf.Fields
'Debug.print fldExternal.Nam e, fldExternal.Typ e
strSQL = "Insert Into TableExternal (TableName,
FieldName, FieldType) Values " & _
"('" & tdf.Name & "', '" & fldExternal.Nam e & "', "
& fldExternal.Typ e & ")"
dbs.Execute strSQL
Next
End If
Next tdf
Set dbs = Nothing

dbsExternal.Clo se
Set dbsExternal = Nothing
MsgBox "Done"

End Sub

Voqa Mosimosi

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

[Phil Stanton]

Re: Different BE database uniformity.

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

Any thoughts on the relationship part of my question?

Thanks again

Phil


"Salad" <oil@vinegar.co mwrote in message
news:nICdnUYpGO-dSRTVnZ2dnUVZ_q finZ2d@earthlin k.com...

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 CheckFieldDiffe rences()
>
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.MD B" 'CHANGE TO THE EXTERNAL FILE NAME
>
' Return reference to current database.
Set dbs = CurrentDb
' Return reference to default workspace.
Set wsp = DBEngine.Worksp aces(0)
' Return reference to External.mdb.
Set dbsExternal = wsp.OpenDatabas e(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.Nam e & ":"
For Each tdf In dbsExternal.Tab leDefs
'Debug.print tdf.Name
If Left(tdf.Name, 3) <"tmp" Then
For Each fldExternal In tdf.Fields
'Debug.print fldExternal.Nam e, fldExternal.Typ e
strSQL = "Insert Into TableExternal (TableName, FieldName,
FieldType) Values " & _
"('" & tdf.Name & "', '" & fldExternal.Nam e & "', " &
fldExternal.Typ e & ")"
dbs.Execute strSQL
Next
End If
Next tdf
Set dbs = Nothing
>
dbsExternal.Clo se
Set dbsExternal = Nothing
MsgBox "Done"
>
End Sub
>
Voqa Mosimosi
http://www.youtube.com/watch?v=EQJr1LUmfLM

[Salad]

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("N orthwind.mdb")
Set rstProducts = dbsNorthwind.Op enRecordset("Pr oducts")
' Print a report showing all the different parts of
' the relation and where each part is stored.
With dbsNorthwind.Re lations!Categor iesProducts
Debug.Print "Properties of " & .Name & " Relation"
Debug.Print " Table = " & .Table
Debug.Print " ForeignTable = " & .ForeignTable
Debug.Print "Fields of " & .Name & " Relation"
With .Fields!Categor yID
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@vinegar.co mwrote in message
news:nICdnUYpGO-dSRTVnZ2dnUVZ_q finZ2d@earthlin k.com...
>

>>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 CheckFieldDiffe rences()
>>
> 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.MD B" 'CHANGE TO THE EXTERNAL FILE NAME
>>
> ' Return reference to current database.
> Set dbs = CurrentDb
> ' Return reference to default workspace.
> Set wsp = DBEngine.Worksp aces(0)
> ' Return reference to External.mdb.
> Set dbsExternal = wsp.OpenDatabas e(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.Ty pe & ")"
> dbs.Execute strSQL
> Next
> End If
> 'Exit For
> Next tdf
> 'Debug.print
>>
> 'Debug.print dbsExternal.Nam e & ":"
> For Each tdf In dbsExternal.Tab leDefs
> 'Debug.print tdf.Name
> If Left(tdf.Name, 3) <"tmp" Then
> For Each fldExternal In tdf.Fields
> 'Debug.print fldExternal.Nam e, fldExternal.Typ e
> strSQL = "Insert Into TableExternal (TableName, FieldName,
>>FieldType) Values " & _
> "('" & tdf.Name & "', '" & fldExternal.Nam e & "', " &
>>fldExternal.T ype & ")"
> dbs.Execute strSQL
> Next
> End If
> Next tdf
> Set dbs = Nothing
>>
> dbsExternal.Clo se
> Set dbsExternal = Nothing
> MsgBox "Done"
>>
>>End Sub
>>
>>Voqa Mosimosi
>>http://www.youtube.com/watch?v=EQJr1LUmfLM

>
>
>

[lyle fairfield]

Re: Different BE database uniformity.

Do you think Windows can find your db file noticeably faster than
Access/JET can find your records?

One db will ensure that changes are made and applied consistently across
the five clubs.
One db will ensure that more clubs can be added easily.
One db will allow for the efficient entry and storage of common data.
(Could any person be a member of more than one club?)
Of course, we could write kludges to try to maintain some sort of
uniformity across the dbs.
But should we?
When I write kludges the things I forget to program haunt me at the most
inopportune times.

"Phil Stanton" <phil@myfamilyn ame.co.ukwrote in
news:uYydnQj6Lb EzKRTVnZ2dnUVZ8 gOdnZ2d@posted. plusnet:

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

[Phil Stanton]

Re: Different BE database uniformity.

Hi Lyle

What you say seems logical. Certainly 1 member can be a member of many clubs
( I am).
I would presumably have to add a foreign key in every table to relate to the
correct DB.

The problem with that approach is that unless security is tight, A member of
1 Club can get info on members of all the other Clubs (via the tables if
nothing else) and with the data Protection Act here in the UK it could be
awkward.

Thanks,
Phil

"lyle fairfield" <lylefa1r@yah00 .cawrote in message
news:Xns9AE675A 59C539666664626 1@216.221.81.11 9...

Do you think Windows can find your db file noticeably faster than
Access/JET can find your records?
>
One db will ensure that changes are made and applied consistently across
the five clubs.
One db will ensure that more clubs can be added easily.
One db will allow for the efficient entry and storage of common data.
(Could any person be a member of more than one club?)
Of course, we could write kludges to try to maintain some sort of
uniformity across the dbs.
But should we?
When I write kludges the things I forget to program haunt me at the most
inopportune times.
>
"Phil Stanton" <phil@myfamilyn ame.co.ukwrote in
news:uYydnQj6Lb EzKRTVnZ2dnUVZ8 gOdnZ2d@posted. plusnet:
>

>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

>

[Phil Stanton]

Re: Different BE database uniformity.

Hi Salad

Thanks a bunch. All works a treat. Now just a metter of going through and
putting things right

BTW some of the relationships look like this
{72CB01A8-BF0B-4CD3-9D4D-150D5A1146B1}
and others like
YearPayments ( I have a table called Year and a table called Payments)
and other like
TownNewAddress ( I have a table called Town and a table called Addres, but
no NewAddress)
must be some logic in it

Thanks again

Phil

"Salad" <oil@vinegar.co mwrote in message
news:i-WdnZdgfsYBdhTVn Z2dnUVZ_rvinZ2d @earthlink.com. ..

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("N orthwind.mdb")
Set rstProducts = dbsNorthwind.Op enRecordset("Pr oducts")
' Print a report showing all the different parts of
' the relation and where each part is stored.
With dbsNorthwind.Re lations!Categor iesProducts
Debug.Print "Properties of " & .Name & " Relation"
Debug.Print " Table = " & .Table
Debug.Print " ForeignTable = " & .ForeignTable
Debug.Print "Fields of " & .Name & " Relation"
With .Fields!Categor yID
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@vinegar.co mwrote in message
>news:nICdnUYpG O-dSRTVnZ2dnUVZ_q finZ2d@earthlin k.com...
>>

>>>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 CheckFieldDiffe rences()
>>>
>> 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.MD B" 'CHANGE TO THE EXTERNAL FILE NAME
>>>
>> ' Return reference to current database.
>> Set dbs = CurrentDb
>> ' Return reference to default workspace.
>> Set wsp = DBEngine.Worksp aces(0)
>> ' Return reference to External.mdb.
>> Set dbsExternal = wsp.OpenDatabas e(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.Ty pe & ")"
>> dbs.Execute strSQL
>> Next
>> End If
>> 'Exit For
>> Next tdf
>> 'Debug.print
>>>
>> 'Debug.print dbsExternal.Nam e & ":"
>> For Each tdf In dbsExternal.Tab leDefs
>> 'Debug.print tdf.Name
>> If Left(tdf.Name, 3) <"tmp" Then
>> For Each fldExternal In tdf.Fields
>> 'Debug.print fldExternal.Nam e, fldExternal.Typ e
>> strSQL = "Insert Into TableExternal (TableName,
>>FieldName, FieldType) Values " & _
>> "('" & tdf.Name & "', '" & fldExternal.Nam e & "', " &
>>fldExternal.T ype & ")"
>> dbs.Execute strSQL
>> Next
>> End If
>> Next tdf
>> Set dbs = Nothing
>>>
>> dbsExternal.Clo se
>> Set dbsExternal = Nothing
>> MsgBox "Done"
>>>
>>>End Sub
>>>
>>>Voqa Mosimosi
>>>http://www.youtube.com/watch?v=EQJr1LUmfLM

>>
>>

[lyle fairfield]

Re: Different BE database uniformity.

On Jul 25, 1:10 pm, "Phil Stanton" <p...@myfamilyn ame.co.ukwrote:

Hi Lyle
>
What you say seems logical. Certainly 1 member can be a member of many clubs
( I am).
I would presumably have to add a foreign key in every table to relate to the
correct DB.
>
The problem with that approach is that unless security is tight, A member of
1 Club can get info on members of all the other Clubs (via the tables if
nothing else) and with the data Protection Act here in the UK it could be
awkward.
>
Thanks,
Phil
>
"lyle fairfield" <lylef...@yah00 .cawrote in message
>
news:Xns9AE675A 59C539666664626 1@216.221.81.11 9...
>

Do you think Windows can find your db file noticeably faster than
Access/JET can find your records?

>

One db will ensure that changes are made and applied consistently across
the five clubs.
One db will ensure that more clubs can be added easily.
One db will allow for the efficient entry and storage of common data.
(Could any person be a member of more than one club?)
Of course, we could write kludges to try to maintain some sort of
uniformity across the dbs.
But should we?
When I write kludges the things I forget to program haunt me at the most
inopportune times.

>

"Phil Stanton" <p...@myfamilyn ame.co.ukwrote in
news:uYydnQj6Lb EzKRTVnZ2dnUVZ8 gOdnZ2d@posted. plusnet:

>

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

I think I understand now that you do not use these five dbs on one
computer, but, rather, distribute one or the other to a club
administrator.

[Salad]

Re: Different BE database uniformity.

Phil Stanton wrote:

Hi Salad
>
Thanks a bunch. All works a treat. Now just a metter of going through and
putting things right
>
BTW some of the relationships look like this
{72CB01A8-BF0B-4CD3-9D4D-150D5A1146B1}
and others like
YearPayments ( I have a table called Year and a table called Payments)
and other like
TownNewAddress ( I have a table called Town and a table called Addres, but
no NewAddress)
must be some logic in it
>
Thanks again

Glad it all worked out! As to the odd names...sounds like they're
Access generated.

>
Phil
>
"Salad" <oil@vinegar.co mwrote in message
news:i-WdnZdgfsYBdhTVn Z2dnUVZ_rvinZ2d @earthlink.com. ..
>

>>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("N orthwind.mdb")
>>Set rstProducts = dbsNorthwind.Op enRecordset("Pr oducts")
>>' Print a report showing all the different parts of
>>' the relation and where each part is stored.
>>With dbsNorthwind.Re lations!Categor iesProducts
>>Debug.Print "Properties of " & .Name & " Relation"
>>Debug.Print " Table = " & .Table
>>Debug.Print " ForeignTable = " & .ForeignTable
>>Debug.Print "Fields of " & .Name & " Relation"
>>With .Fields!Categor yID
>>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@vinegar.co mwrote in message
>>>news:nICdnUY pGO-dSRTVnZ2dnUVZ_q finZ2d@earthlin k.com...
>>>
>>>
>>>>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 CheckFieldDiffe rences()
>>>>
>>> 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.MD B" 'CHANGE TO THE EXTERNAL FILE NAME
>>>>
>>> ' Return reference to current database.
>>> Set dbs = CurrentDb
>>> ' Return reference to default workspace.
>>> Set wsp = DBEngine.Worksp aces(0)
>>> ' Return reference to External.mdb.
>>> Set dbsExternal = wsp.OpenDatabas e(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.Nam e & ":"
>>> For Each tdf In dbsExternal.Tab leDefs
>>> 'Debug.print tdf.Name
>>> If Left(tdf.Name, 3) <"tmp" Then
>>> For Each fldExternal In tdf.Fields
>>> 'Debug.print fldExternal.Nam e, fldExternal.Typ e
>>> strSQL = "Insert Into TableExternal (TableName,
>>>>FieldName , FieldType) Values " & _
>>> "('" & tdf.Name & "', '" & fldExternal.Nam e & "', " &
>>>>fldExternal .Type & ")"
>>> dbs.Execute strSQL
>>> Next
>>> End If
>>> Next tdf
>>> Set dbs = Nothing
>>>>
>>> dbsExternal.Clo se
>>> Set dbsExternal = Nothing
>>> MsgBox "Done"
>>>>
>>>>End Sub
>>>>
>>>>Voqa Mosimosi
>>>>http://www.youtube.com/watch?v=EQJr1LUmfLM
>>>
>>>

>

[Phil Stanton]

Re: Different BE database uniformity.

Hi Lyle

Sorry, I should hav made that clear. Basically it a Generic Club Db with a
number of Clubs who all have the same front end, and of course their own
individual back end.

The menu structure does not show forms or reports not relevant to that Club,
so the Bridge club does not see any of the forms or reports to do with
boats.

Equally I have a table which is "read" on the opening of each form which
hides irrelevant fields or changes the caption of a field so for example on
the YachtClub Db the caption for a particular date field is Joining Date,
while on my family & Friends Db the caption appears as Wedding Date.

Thanks again

Phil

fairfield" <lyle.fairfield @gmail.comwrote in message
news:dc4c10bc-861d-4435-b18f-a3819bc985c9@x4 1g2000hsb.googl egroups.com...

On Jul 25, 1:10 pm, "Phil Stanton" <p...@myfamilyn ame.co.ukwrote:

>Hi Lyle
>>
>What you say seems logical. Certainly 1 member can be a member of many
>clubs
>( I am).
>I would presumably have to add a foreign key in every table to relate to
>the
>correct DB.
>>
>The problem with that approach is that unless security is tight, A member
>of
>1 Club can get info on members of all the other Clubs (via the tables if
>nothing else) and with the data Protection Act here in the UK it could be
>awkward.
>>
>Thanks,
>Phil
>>
>"lyle fairfield" <lylef...@yah00 .cawrote in message
>>
>news:Xns9AE675 A59C53966666462 61@216.221.81.1 19...
>>

Do you think Windows can find your db file noticeably faster than
Access/JET can find your records?

>>

One db will ensure that changes are made and applied consistently
across
the five clubs.
One db will ensure that more clubs can be added easily.
One db will allow for the efficient entry and storage of common data.
(Could any person be a member of more than one club?)
Of course, we could write kludges to try to maintain some sort of
uniformity across the dbs.
But should we?
When I write kludges the things I forget to program haunt me at the
most
inopportune times.

>>

"Phil Stanton" <p...@myfamilyn ame.co.ukwrote in
>news:uYydnQj6L bEzKRTVnZ2dnUVZ 8gOdnZ2d@posted .plusnet:

>>

>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

>
I think I understand now that you do not use these five dbs on one
computer, but, rather, distribute one or the other to a club
administrator.

[Keith Wilby]

Re: Different BE database uniformity.

"Phil Stanton" <phil@myfamilyn ame.co.ukwrote in message
news:9dadnRCV-qTu4RbVRVnyjgA@ posted.plusnet. ..

Hi Lyle
>
Sorry, I should hav made that clear. Basically it a Generic Club Db with a
number of Clubs who all have the same front end, and of course their own
individual back end.
>

Why not have an empty BE as your design master and distibute that whenever
you make a change? You could either import the data manually or have a bit
of code do it. Seems a bit simpler than having code modify many back ends.

Regards,
Keith.

https://www.keithwilby.com

[Phil Stanton]

Re: Different BE database uniformity.

That seems a very sensible idea Keith. Why didn't I think of it?

Thanks

Phil


"Keith Wilby" <here@there.com wrote in message
news:488d70cf$1 _1@glkas0286.gr eenlnk.net...

"Phil Stanton" <phil@myfamilyn ame.co.ukwrote in message
news:9dadnRCV-qTu4RbVRVnyjgA@ posted.plusnet. ..

>Hi Lyle
>>
>Sorry, I should hav made that clear. Basically it a Generic Club Db with
>a number of Clubs who all have the same front end, and of course their
>own individual back end.
>>

>
Why not have an empty BE as your design master and distibute that whenever
you make a change? You could either import the data manually or have a
bit of code do it. Seems a bit simpler than having code modify many back
ends.
>
Regards,
Keith.
www.keithwilby.com

[Keith Wilby]

Re: Different BE database uniformity.

"Phil Stanton" <phil@myfamilyn ame.co.ukwrote in message
news:n_ydnT2R4K QcERDVnZ2dnUVZ8 t_inZ2d@posted. plusnet...

That seems a very sensible idea Keith. Why didn't I think of it?
>
Thanks
>

No problem Phil, to be honest I thought I might have been missing something!

Regards,
Keith.

[Tony Toews [MVP]]

Re: Different BE database uniformity.

"Phil Stanton" <phil@myfamilyn ame.co.ukwrote:

>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.

I use Compare'Em
http://home.gci.net/~mike-noel/Compa.../CompareEM.htm.

Not perfect but decent for the task.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/