我有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 www.youtube/watch?v=EQJr1LUmfLM
谢谢沙拉看起来很棒。今晚会尝试一下。 可能需要优化它以获得TblCurrent中所有数据库的所有信息，并指向它们来自哪个数据库的 指针。应该很容易比较 关于我的问题的关系部分的任何想法？ 再次感谢 Phil " Salad" < oi*@vinegar在留言中写道 新闻：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 www.youtube/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 PhilPhil 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 www.youtube/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*@vinegarwrote 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. TheBEs 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 ofone of these clubs, I want to make sure I do it with the other clubs aswell.Has anyone got routine that will check that each BE database has1) All the tables (even though some will be empty e.g. Bridge clubhas nothing to do with boats)2) The fields are all the same size, default values are the same,Indexes are the same etc3) Relationships are identicalI am struggling to do it manually at the moment and I know it is veryerror proneThanks for any advisePhilHi 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 www.youtube/watch?v=EQJr1LUmfLM
更多推荐
不同的BE数据库一致性
发布评论