如何用ADO将连接的数据库中所有的表复制转换成到一个空的ACCESS2000数据库中去？

[gagarou]

源数据库的ADO连接我已经可以了，如CNN1，接下来如何获得表的数目，表的名字，又如何INSERT到指定的ACCESS2000中去呢？

[gagarou]

怎么，我的问题，都没人解答？没人这样用过吗？

[qingming81]

'请看看Visdata中的Copy数据模块吧。

Public gwsMainWS As Workspace 'main workspace object

Function CopyData(rFromDB As Database, rToDB As Database, rFromName As String, rToName As String) As Integer
On Error GoTo CopyErr

Dim recRecordset1 As Recordset, recRecordset2 As Recordset
Dim i As Integer
Dim nRC As Integer
Dim fld As Field

'open both recordsets
Set recRecordset1 = rFromDB.OpenRecordset(rFromName)
Set recRecordset2 = rToDB.OpenRecordset(rToName)
gwsMainWS.BeginTrans
While recRecordset1.EOF = False
recRecordset2.AddNew
'this loop copies the data from each field to
'the new table
' For Each fld In recRecordset1.Fields
For i = 0 To recRecordset1.Fields.Count - 1
Set fld = recRecordset1.Fields(i)
recRecordset2(fld.Name).Value = fld.Value
Next
recRecordset2.Update
recRecordset1.MoveNext
nRC = nRC + 1
'this test will commit transactions every 1000 records
If nRC = 1000 Then
gwsMainWS.CommitTrans
gwsMainWS.BeginTrans
nRC = 0
End If
Wend
gwsMainWS.CommitTrans

CopyData = True
Exit Function

CopyErr:
gwsMainWS.Rollback
ShowError
CopyData = False
End Function

[qingming81]

http://expert.csdn.net/Expert/topic/2599/2599859.xml?temp=.3183405

'查看表
'引用微软 ADO Ext.2.7 for dll and Security

Sub OpenConnection()

Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= c:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.mdb;"
Set cat.ActiveConnection = cnn
Debug.Print cat.Tables(0).Type
'此处可循环读出表的name,type等属性
End Sub

'查看字段
Sub ViewFields()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
Dim cat As New ADOX.Catalog

' Open the Connection
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.mdb;"

' Open the catalog
Set cat.ActiveConnection = cnn

' Set the Source for the Recordset
Set rst.Source = cat.Views("AllCustomers").Command

' Retrieve Field information
rst.Fields.Refresh
For Each fld In rst.Fields
Debug.Print fld.name & ":" & fld.Type
'显示所有的字段的名称和类型
Next

End Sub












http://expert.csdn.net/Expert/topic/2365/2365155.xml?temp=6.572902E-03
怎样实现像odbc那样列出数据库名，然后列出表名？？？

sql server 2000:
列数据库名：

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=master"
conn.Open
sql = "SELECT name FROM sysdatabases order by name"
rs.Open sql, conn
cmbDBName.Clear
If Not (rs.BOF And rs.EOF) Then
Do While Not rs.EOF
cmbDBName.AddItem rs.Fields("name")
rs.MoveNext
Loop
cmbDBName.ListIndex = 0
End If
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

列表名：
Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Dim sql As String
Dim dt As String
Dim i As Integer

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=AA"
conn.Open

Set rs = conn.OpenSchema(adSchemaTables)


Do While Not rs.EOF
If rs.Fields("TABLE_TYPE") = "TABLE" Then
Grid1.AddItem "表名：" & rs.Fields("TABLE_NAME")
Grid1.AddItem "字段名" & vbTab & "类型" & vbTab & "长度"
sql = "select top 1 * from " & rs.Fields("TABLE_NAME")
rs1.Open sql, conn
For i = 0 To rs1.Fields.Count - 1
Select Case rs1.Fields(i).Type
Case 3
dt = "int"
Case 202
dt = "nvarchar"
Case 203
dt = "ntext"
Case 4
dt = "real"
Case 129
dt = "char"
Case 131
dt = "numeric"
Case 200
dt = "varchar"
Case 135
dt = "datetime"
Case 2
dt = "smallint"
Case 17
dt = "tinyint"
Case Else
dt = rs1.Fields(i).Type
MsgBox dt & "" & rs.Fields("Table_name")
End Select
Grid1.AddItem rs1.Fields(i).Name & vbTab & dt & vbTab & rs1.Fields(i).DefinedSize
Next
rs1.Close
Grid1.AddItem ""
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set rs1 = Nothing
conn.Close
Set conn = Nothing

[gagarou]

声明一下，源数据是foxpro2.6 for dos的，用dsn连接的！