不同的BE数据库一致性 [英] Different BE database uniformity.
问题描述
我有1个FE数据库，可以链接到许多BE数据库。
BE都是不同的俱乐部，例如帆船俱乐部，游艇俱乐部，桥牌俱乐部，
家庭等
问题是如果我改变表结构或关系的任何内容
其中一个俱乐部，我想确保我和其他俱乐部一起使用
well。
有没有人有常规检查每个BE数据库已经
1）所有的桌子（即使有些桌子是空的，例如Bridge club有
与船只无关）
2）字段都是相同的大小，默认值是相同的，索引
是相同的等
3）关系是相同的
我现在正在努力手动操作，我知道这是非常错误
容易发生
感谢您的任何建议
Phil
你有什么理由不能组合后端数据库？一些表重组
添加键以识别俱乐部将允许您切换而不需要重新链接到新的后端。
否则，写一些VBA代码进行更改，然后在每个
后端运行它。
" Phil Stanton" < ph ** @ myfamilyname.co.ukwrote in message
news：uY ************************** ****@posted.plusn et ...
我有1个FE数据库，可以链接到多个BE数据库。
BE都是不同的俱乐部，例如帆船俱乐部，游艇俱乐部，桥牌俱乐部，
家庭等
问题是如果我改变表结构或关系的任何内容
其中一个俱乐部，我想确保我和其他俱乐部一起使用
well。
有没有人有常规检查每个BE数据库已经
1）所有的桌子（即使有些桌子是空的，例如Bridge club有
与船只无关）
2）字段大小相同，默认值相同，
索引
是相同的等
3）关系是相同的
我现在正在努力手动完成它，我知道这是非常b $ b
错误
俯卧
感谢您的任何建议
Phil
Phil Stanton写道：
我有1个FE数据库，可以链接到多个BE数据库。
BE都是不同的俱乐部，例如帆船俱乐部，游艇俱乐部，桥牌俱乐部，
家庭等
问题是如果我改变表结构或关系的任何内容
其中一个俱乐部，我想确保我和其他俱乐部一起使用
well。
有没有人有常规检查每个BE数据库已经
1）所有的桌子（即使有些桌子是空的，例如Bridge club有
与船只无关）
2）字段都是相同的大小，默认值是相同的，索引
是相同的等
3）关系是相同的
我现在正在努力手动操作，我知道这是非常错误
容易发生
感谢您的任何建议
Phil
你好菲尔。这是我编写的例程。我创建了两个表;
TableCurrent和TableExternal。三个领域; TableName，FieldName，
FieldType。您也可以添加FieldSize并在Insert
语句中添加它，并插入Size属性。更改外部mdb
名称。然后跑。之后你可以创建一些查询来检查这两个表之间的差异。
Sub CheckFieldDifferences（）
Dim dbs作为数据库
Dim dbs外部作为数据库
昏暗wsp作为工作区
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" ''更改外部文件名
''返回对当前数据库的引用。
设置dbs = CurrentDb
''返回对默认工作区的引用。
设置wsp = DBEngine.Workspaces（0）
''返回对External.mdb的引用。
设置dbsExternal = wsp.OpenDatabase（strExternal）
''枚举每个数据库中的所有TableDef对象。
''Debug.print dbs.Name& "："
For each tdf in dbs.TableDefs
''Debug.print tdf.Name
If Left（tdf.Name ，3）<" tmp"然后
For each fldCurrent in tdf.Fields
''Debug.print fldCurrent.Name，fldCurrent.Type
strSQL =" Insert Into TableCurrent（TableName，
FieldName，FieldType）值 &安培; _
"（''"& tdf.Name&"''，''"& fldCurrent.Name&"''，"
& fldCurrent.Type&"）"
dbs.Execute strSQL
Next
End If
''退出
下一步tdf
''Debug.print
''Debug.print dbsExternal .Name& "："
For each tdf in dbsExternal.TableDefs
''Debug.print tdf.Name
If Left（tdf.Name ，3）<" tmp"然后
For each fldExternal in tdf.Fields
''Debug.print fldExternal.Name，fldExternal.Type
strSQL =" Insert Into TableExternal（TableName，
FieldName，FieldType）值 &安培; _
"（''"& tdf.Name&"''，''"& fldExternal.Name&"''，"
& fldExternal.Type&"）"
dbs.Execute strSQL
Next
End If
下一个tdf
设置dbs =没什么
dbsExternal.Close
设置dbsExternal = Nothing
MsgBox完成
结束子
Voqa Mosimosi
http://www.youtube.com/watch?v=EQJr1LUmfLM
谢谢沙拉看起来很棒。今晚会尝试一下。
可能需要优化它以获得TblCurrent中所有数据库的所有信息，并指向它们来自哪个数据库的
指针。应该很容易比较
关于我的问题的关系部分的任何想法？
再次感谢
Phil
" Salad" < oi*@vinegar.com在留言中写道
新闻：nI ***************************** *@earthlink.co m ...
Phil Stanton写道：
>我有1 FE数据库可以链接到许多BE数据库。
BE都是不同的俱乐部，例如帆船俱乐部，游艇俱乐部，桥牌俱乐部，家庭等。
问题是如果我改变其中一个俱乐部的桌面结构或关系，我想制作我确定我和其他俱乐部一起做好了。
有没有人能够检查每个BE数据库是否有例子1）所有表格（即使有些表格是空的，例如Bridge club
与船只无关）
2）字段大小相同，默认值相同，
索引相同等等
3）关系相同
我现在正在努力手动完成它并且我知道它非常容易出错
感谢您的任何建议
菲尔
你好菲尔。这是我编写的例程。我创建了两个表; TableCurrent
和TableExternal。三个领域; TableName，FieldName，FieldType。你
也可以添加FieldSize并在Insert语句中添加它，
也插入Size属性。更改外部mdb名称。然后
运行。之后你可以创建一些查询来检查这两个表之间的差异
。
Sub CheckFieldDifferences（）
Dim dbs作为数据库
Dim dbs外部作为数据库
昏暗wsp作为工作区
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" ''更改外部文件名
''返回对当前数据库的引用。
设置dbs = CurrentDb
''返回对默认工作区的引用。
设置wsp = DBEngine.Workspaces（0）
''返回对External.mdb的引用。
设置dbsExternal = wsp.OpenDatabase（strExternal）
''枚举每个数据库中的所有TableDef对象。
''Debug.print dbs.Name& "："
For each tdf in dbs.TableDefs
''Debug.print tdf.Name
If Left（tdf.Name ，3）<" tmp"然后
For each fldCurrent in tdf.Fields
''Debug.print fldCurrent.Name，fldCurrent.Type
strSQL =" Insert Into TableCurrent（TableName，FieldName，
FieldType）值 &安培; _
"（''"& tdf.Name&"''，''"& fldCurrent.Name&"''，"&
fldCurrent.Type&"）"
dbs.Execute strSQL
Next
End If
''退出
下一步tdf
''Debug.print
''Debug.print dbsExternal .Name& "："
For each tdf in dbsExternal.TableDefs
''Debug.print tdf.Name
If Left（tdf.Name ，3）<" tmp"然后
For each fldExternal in tdf.Fields
''Debug.print fldExternal.Name，fldExternal.Type
strSQL =" Insert Into TableExternal（TableName，FieldName，
FieldType）值 &安培; _
"（''"& tdf.Name&"''，''"& fldExternal.Name&"''，"&
fldExternal.Type&"）"
dbs.Execute strSQL
Next
End If
下一个tdf
设置dbs =没什么
dbsExternal.Close
设置dbsExternal = Nothing
MsgBox完成
结束子
Voqa Mosimosi
http://www.youtube.com/watch?v=EQJr1LUmfLM
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
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" <ph**@myfamilyname.co.ukwrote in message
news:uY******************************@posted.plusn et...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 veryerror
prone
Thanks for any advise
Phil
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
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" <oi*@vinegar.comwrote in message
news:nI******************************@earthlink.co m...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
PhilHi 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
这篇关于不同的BE数据库一致性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
