|
|
本帖最后由 xmyjk 于 2012-2-1 16:38 编辑
wcymiss 发表于 2012-1-29 22:29 
原来，除了cat.tables的table.name可以取得表名，cnn.OpenSchema也可以取表名啊。而且速度比cat.tabl ...
仅针对取表名和字段名的方法进行讨论，集合了42楼赵老师的意见再次又整理了下：
呵呵，我昨晚翻查了一晚的VB相关论坛和ADO的对象，发现现在几个论坛主流的运用ADO或ADOX去获取表名以及字段名的做法有些片面，现在也和大家分享下我整理的，也请各位指教。
1.关于使用ADO对象的.OpenSchema方法获取表名和字段名
几个主流论坛，关于使用.OpenSchema方法获取表名和字段名的做法是，先运用.openschema(adSchemaTables)，获取表名，然后进行.Execute执行SQL语句，获取Recordset，然后读取FIELDS，其实不然在，如下图，
，.openschema(adSchemaColumns)就可以获取表名以及字段名，视乎没必要用.EXECUTE。整体代码如下，可以测试附件，附件的运行为了区分度，都是让汇总程序执行了20次，运行结果体现，速度还是不错的：
[code=vb]
Sub OPENCL()
Dim d As New Dictionary, i%
Dim myFile As String, mypath As String, bm As String
Dim cnn As ADODB.Connection
Dim rst1 As ADODB.Recordset
mypath = ThisWorkbook.Path & "\数据源\"
Application.ScreenUpdating = False
myFile = Dir(ThisWorkbook.Path & "\数据源\*.xls")
Do While myFile <> ""
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & mypath & myFile
Set rst1 = cnn.openschema(adSchemaColumns)
With rst1
Do While Not .EOF
bm = rst1!table_name
If d.Exists(bm) = False Then
Set d(bm) = New Dictionary
End If
d(bm)(CStr(rst1!COLUMN_NAME)) = 0
.MoveNext
Loop
End With
myFile = Dir
Loop
Dim arr
arr = d.Keys
For i = 0 To UBound(arr)
'Debug.Print arr(i), Join(d(arr(i)).Keys, ","), d(arr(i)).Count
Next
Erase arr
Application.ScreenUpdating = True
Set d = Nothing
rst1.Close
Set rst1 = Nothing
cnn.Close
Set cnn = Nothing
End Sub
[/code]
2.关于使用ADOX获取表名和字段名
几个主流论坛，都是使用ADOX进行.ActiveConnection，然后从.Tables对象集合里面去获取表名，然后再去做ADO的.Connection，然后就.exectue去获取字段名。请大家看下图。
，其实，用ADOX进行连接后，获取的.tables对象集合里面，已经包含了.columns的对象集合，这个集合是什么呢，就是我们需要的字段名。所以没必要再次建立ADO连接，然后执行sql语句去获取字段名。测试了下结果，速度上和原来execute不想上下，估计是.columns的对象集合也比较庞大，调用起来比较耗费效率。
整体代码如下：
[code=vb]
Sub ADOXTB()
Dim cat As adox.Catalog, i%
Dim d As New Dictionary
Dim myFile As String, mypath As String, bm As String
Dim tb As Table, cl As Column
mypath = ThisWorkbook.Path & "\数据源\"
Application.ScreenUpdating = False
myFile = Dir(ThisWorkbook.Path & "\数据源\*.xls")
Do While myFile <> ""
Set cat = New adox.Catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & mypath & myFile
For Each tb In cat.Tables
With tb
bm = CStr(.Name)
If d.Exists(bm) = False Then
Set d(bm) = New Dictionary
End If
For Each cl In .Columns
d(bm)(cl.Name) = 0
Next
End With
Next
myFile = Dir
Loop
Dim arr
arr = d.Keys
For i = 0 To UBound(arr)
'Debug.Print arr(i), Join(d(arr(i)).Keys, ","), d(arr(i)).Count
Next
Set cat = Nothing
Set d = Nothing
Erase arr
Application.ScreenUpdating = True
End Sub
[/code]
3.如果从单纯获取表名的方法来对比
两种方法都能取到表名。且效率都很高，但是，用.openschema(adSchemaTables)的好处就是，后面再执行SQL语句的时候，不用再次连接，因为CNN.OPEN已经做好了，可以直接使用了。
[code=vb]
Sub adox()
Dim cat As adox.Catalog
Dim d As New Dictionary
Dim myFile As String, mypath As String
Dim tb As Table
mypath = ThisWorkbook.Path & "\数据源\"
Application.ScreenUpdating = False
myFile = Dir(ThisWorkbook.Path & "\数据源\*.xls")
Do While myFile <> ""
Set cat = New adox.Catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & mypath & myFile
For Each tb In cat.Tables
d(CStr(tb.Name)) = 0
Next
Set tb = Nothing
myFile = Dir
Loop
'Debug.Print Join(d.Keys, ",")
Set d = Nothing
Set cat = Nothing
Application.ScreenUpdating = True
End Sub
Sub openschema()
Dim d As New Dictionary
Dim myFile As String, mypath As String
Dim cnn As ADODB.Connection
Dim rst1 As ADODB.Recordset
mypath = ThisWorkbook.Path & "\数据源\"
Application.ScreenUpdating = False
myFile = Dir(ThisWorkbook.Path & "\数据源\*.xls")
Do While myFile <> ""
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & mypath & myFile
Set rst1 = cnn.openschema(adSchemaTables)
With rst1
Do While Not .EOF
d(CStr(rst1!table_name)) = 0
.MoveNext
Loop
End With
myFile = Dir
Loop
'Debug.Print Join(d.Keys, ",")
rst1.Close
cnn.Close
Set rst1 = Nothing
Set cnn = Nothing
Application.ScreenUpdating = True
Set d = Nothing
End Sub
[/code]
附件上传了，大家都可以试看看，四个方式的比较。
最后，第1点和第2点的取表头方式，都和传统的FIELDS集合中取的表头的顺序不同（即与原表表头不同），因为系统有进行了排序，因此，实践操作中，可以先把关键字段放入字典，后续循环的时候，排除该关键字即可，如果是很乱序的多表表头，应该不会有什么影响。
还是比较推荐用.openschema(adSchemaColumns)方式取获取不同字段多表表头的，效率很高，且与表的链接已经做好了，下一步如果要执行什么SQL代码，也可以直接用。
总之，存在即合理，微软设计了ADOX对象来针对数据结构的操作，肯定是有独到的地方的，虽然表现出来ADOX的效率貌似那么不尽人意，但是他还有很多其他功能值得我们去探究的。
VBA的世界真的很广，随便研究下都能研究出很多的。呵呵呵。最后上传一下找到的一个ADO和ADOX对象的完全手册，有兴趣的同志，也可以下载研究下。
对了，最最最后，分享下ADO一个学习资料的网址，里面还有很多方法事件对象的示例。http://doc.51windows.net/ado/?url=/ado/mdmthopenschema.htm。另外只是方法讨论，代码还没考虑楼楼上那些表名判断的问题。
最后，请各位高手多多指正。谢谢。
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看，没有账号？免费注册
x
评分
-
1
查看全部评分
-
|
楼主: zhaogang1960
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2012-1-31 12:38
楼主
