主题：[分享]动态更新表结构，有错误，修改中...请帮忙

以下内容为程序代码:

1 Dim constr As String="Provider=Microsoft.ACE.OLEDB.12.0;User ID=admin;Data Source=D:\data.accdb;Persist Security Info=False" '标准数据库
2 Dim constr1 As String ="Provider=Microsoft.ACE.OLEDB.12.0;User ID=admin;Data Source=D:\da.accdb;Persist Security Info=False" '目标数据库
3 Dim rdb As new adodb.Connection '标准
4 Dim rdb1 As new adodb.Connection '目标
5 Dim cat As new Adox.CataLog '标准
6 Dim cat1 As new Adox.CataLog '目标
7 rdb.open(constr ) '打开标准数据库
8 rdb1.open(constr1 ) ' 打开目录数据库
9 cat.ActiveConnection =rdb
10 cat1.ActiveConnection =rdb1
11 ''同步表
12 try
13 output.Show("同步表,个数" & Cat.Tables.Count )
14 For i As Integer = 0 To Cat.Tables.Count - 1
15 If Cat.Tables(i).Type = "Table" Then
16 Dim TName As String
17 Dim TbBz As New Adox.Table
18 TbBz =Cat.Tables(i)
19 TName = TbBz.Name
20 '检查目标数据库中是否存在此Table
21 Dim TExits As Integer = 0 '表存在标记
22 For j As Integer = 0 To Cat1.Tables.Count-1 '检查是否存在表
23 If Cat1.Tables(j).Name = TName Then
24 TExits =1
25 Exit For
26 End If
27 Next
28 If TExits = 0 Then '创建表
29 Dim Tb1 As New Adox.Table
30 tb1.Name = Tname
31 Cat1.Tables.Append(Tb1)
32 End If
33 Dim TbMb As Adox.Table
34 TbMb = cat1.Tables(TName) '目标表
35 Output.Show("同步表:" & TbBz.Name & " 列数:" & TbBz.Columns.count )
36 For k As Integer = 0 To TbBz.Columns.count - 1 '遍历列
37 Dim CBz As Adox.Column
38 CBz = cat.Tables(i).Columns(k) '标准列
39 Dim kexits As Integer = 0
40 Output.Show("检查此列:" & TName & CBz.Name & "-" & Cbz.Type & "-" & Cbz.DefinedSize)
41 For m As Integer = 0 To TbMb.Columns.count -1 '查找是否存在此列
42 If TbMb.Columns(m).Name = CBz.Name Then
43 kexits =1
44 Exit For
45 End If
46 Next m
47 If kexits = 1 Then '存在此列
48 Continue For
49 Else '不存在此列
50 output.Show("创建列-" & "表名称:" & TName & "-列名称:" & Cbz.Name & "-列类型:" & Cbz.Type)
51 If Cbz.Type=202 Or Cbz.Type=200 Then
52 TbMb.Columns.Append(CBz.Name,Cbz.Type,Cbz.DefinedSize)
53 ElseIf Cbz.Type=131 Then
54 Dim Col1 As New ADOX.Column
55 col1.Name = CBz.Name
56 col1.Type = CBz.Type
57 col1.NumericScale = CBz.NumericScale
58 col1.Precision = CBz.Precision
59 TbMb.Columns.Append(col1)
60 Else
61 TbMb.Columns.Append(CBz.Name,Cbz.Type)
62 End If
63 End If
64 Next k
65
66 End If
67 Next
68
69 '===================
70 '同步视图
71
72
73
74 '清除 目标数据库的 VIEW 集合
75 output.Show("删除View,view个数" & CAT1.Views.Count )
76 For i As Integer = 0 To CAT1.Tables.Count - 1
77 If Cat1.Tables(i).Type = "View" Then
78 Output.Show("View-delete-" & Cat1.Tables(i).Name)
79 cat1.Views.Delete(Cat1.Tables(i).Name)
80 'Cat1.Views.delete( Cat1.Views(i))
81 Output.Show("View-deleted")
82 End If
83
84 Next i
85 '重新向目标数据库中添加VIEW.
86 For i As Integer = 0 To Cat.Views.Count -1
87 Output.Show("View_adding" & Cat.VIews(i).Name)
88 Dim vName As String = Cat.VIews(i).Name
89 Dim cmd As New ADODB.Command
90 cmd=Cat.views(i).Command
91 Dim cmd1 As new ADODB.Command
92 cmd1.CommandText = cmd.CommandText '目标 = 标准
93 cat1.Views.Append(vName,cmd1)
94 Next
95 '
96 '删除过程
97 output.Show("Pro_Delete,个数" & CAT1.Procedures.Count )
98 For i As Integer = 0 To CAT1.Procedures.Count - 1
99 Output.Show("Procedures-delete-" & Cat1.Procedures(i).Name)
100 cat1.Procedures.Delete(Cat1.Procedures(i).Name)
101 Output.Show("Procedures-deleted")
102 Next i
103 '重建过程
104 For i As Integer = 0 To Cat.Procedures.Count -1
105 Output.Show("Procedures_adding" & Cat.Procedures(i).Name)
106 Dim vName As String = Cat.Procedures(i).Name
107 Dim cmd As New ADODB.Command
108 cmd=Cat.Procedures(i).Command
109 Dim cmd1 As new ADODB.Command
110 cmd1.CommandText = cmd.CommandText '目标 = 标准
111 cat1.Procedures.Append(vName,cmd1)
112 Next
113 'Clear UP
114 cat= Nothing
115 cat1= Nothing
116 rdb.close
117 rdb1.close
118 output.Show("执行成功!")
119 Catch ex As Exception
120 cat= Nothing
121 cat1= Nothing
122 rdb.close
123 rdb1.close
124 End Try
125