可用ADO的OpenSchema方法来获取表结构信息,下面是一个不完善的提示性的示例:'获取所有用户表 Sub GetTableStructured() Dim cn As ADODB.Connection Dim rsTable As ADODB.Recordset Dim rsField As ADODB.Recordset Dim StrConnect As String Dim i As Long
StrConnect = "Provider = SQLOLEDB.1;Persist Security Info = False;" & _ "User ID = sa;Password = MOFZaCW3Ob;Data Source = 192.168.0.179;" & _ "Initial Catalog = TEST" Set cn = New ADODB.Connection 'cn.Mode = adModeShareDenyWrite cn.Open StrConnect
Set rsTable = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE")) While Not rsTable.EOF Debug.Print "TableName : " & rsTable!TABLE_NAME Set rsField = cn.Execute("Select top 1 * From " & rsTable!TABLE_NAME) While Not rsField.EOF For i = 0 To rsField.Fields.count - 1 Debug.Print " FieldName : " & rsField.Fields(i).Name; _ " FieldType : " & rsField.Fields(i).Type Next rsField.MoveNext Wend rsTable.MoveNext Wend以上表的字段名称,类型OpenSchemae都可以获取,以及索引主键等信息,具体参考ADO手册...
rs.open "select * from tab",conn,1,1 for i=0 to rs.fields.count debug.print rs.fields(i).name next
然后连接Access 创建表
如果楼主是想把数据库从SQL转换成Access 是有其他方法的
http://bbs.blueidea.com/viewthread.php?tid=740203&page=
B)用 ADOX 可以取得数据库的表、字段等定义。
C)读取 sysobjects 和 syscolumns 系统表,里面有 SQL Server 关于表、字段的定义。
因为SQLServer数据库中的表可能是变化的:即可能在使用过程中会添加某张表,而这张表在系统使用时能够在本地建立起相同的表。2楼的方法我使用了,但是效果不是太好,因为字段类型在两种数据库中不太相同,望高手能给点思路。
谢谢
Sub GetTableStructured()
Dim cn As ADODB.Connection
Dim rsTable As ADODB.Recordset
Dim rsField As ADODB.Recordset
Dim StrConnect As String
Dim i As Long
StrConnect = "Provider = SQLOLEDB.1;Persist Security Info = False;" & _
"User ID = sa;Password = MOFZaCW3Ob;Data Source = 192.168.0.179;" & _
"Initial Catalog = TEST"
Set cn = New ADODB.Connection
'cn.Mode = adModeShareDenyWrite
cn.Open StrConnect
Set rsTable = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))
While Not rsTable.EOF
Debug.Print "TableName : " & rsTable!TABLE_NAME
Set rsField = cn.Execute("Select top 1 * From " & rsTable!TABLE_NAME)
While Not rsField.EOF
For i = 0 To rsField.Fields.count - 1
Debug.Print " FieldName : " & rsField.Fields(i).Name; _
" FieldType : " & rsField.Fields(i).Type
Next
rsField.MoveNext
Wend
rsTable.MoveNext
Wend以上表的字段名称,类型OpenSchemae都可以获取,以及索引主键等信息,具体参考ADO手册...
for i=0 to rs.fields.count
debug.print rs.fields(i).name
next