private sub CopyAlltable() Dim cn As ADODB.Connection Dim rs As New ADODB.Recordset Set cn = New ADODB.Connection cn.CursorLocation = adUseClient cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\A.mdb;" Set rs = cnDatabase.OpenSchema(adSchemaTables) Do While Not rs.EOF '将旧数据库中的各个表复制到新数据库中 If rs.Fields("TABLE_TYPE") = "TABLE" Then cn.Execute "select * into "& _ "[C:\newdb.mdb;].[" & rs.Fields("TABLE_NAME") & "]“& _ ” from [" & rs.Fields("TABLE_NAME") & "]" end if rs.MoveNext Loop rs.Close set rs=nothing cn.close set cn=nothing end sub
connection.excute("select * into "目标数据表" in "目标数据库" from "源数据表"") 你这样写vb里会把 目标数据表、目标数据库、源数据表看成变量。 改成这样: ("select * into ""目标数据表"" in ""目标数据库"" from ""源数据表"") 或("select * into 目标数据表 in 目标数据库 from 源数据表")
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cn = New ADODB.Connection cn.CursorLocation = adUseClient
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\A.mdb;"
Set rs = cnDatabase.OpenSchema(adSchemaTables)
Do While Not rs.EOF
'将旧数据库中的各个表复制到新数据库中
If rs.Fields("TABLE_TYPE") = "TABLE" Then
cn.Execute "select * into "& _
"[C:\newdb.mdb;].[" & rs.Fields("TABLE_NAME") & "]“& _
” from [" & rs.Fields("TABLE_NAME") & "]"
end if
rs.MoveNext
Loop
rs.Close
set rs=nothing
cn.close
set cn=nothing
end sub
你这样写vb里会把 目标数据表、目标数据库、源数据表看成变量。
改成这样:
("select * into ""目标数据表"" in ""目标数据库"" from ""源数据表"")
或("select * into 目标数据表 in 目标数据库 from 源数据表")
不存在表:select * into
存在表: insert * into