满足某条件的数据库合并问题

237708857

EH高手请帮忙:
两个Access数据库A.accdb和B.accdb，Table1是A.accdb的表，Table2是B.accdb的表。
想利用VBA实现的效果是：将B.accdb Table2表中所有Age大于20的记录储存到数据库A.accdb的表Table1中。
当我运行下面的代码时提示如下错误，请高手帮忙解决，在线等，谢谢！

Sub ConbineData()
Dim cnn As New Connection
Dim rs As New Recordset
Dim Sql As String, spath As String, dt As Date
spath = ThisWorkbook.Path & "\A.accdb"
spath1 = ThisWorkbook.Path & "\B.accdb"
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Jet OLEDB:Database Password=123456; Data Source=" & spath
data1 = "Microsoft.ACE.OLEDB.12.0; Jet OLEDB:Database Password=123456; DATABASE=" & spath1
Sql = "insert into Table1 select * from Table2 in '" & data1 & "'"
rs.Open Sql, cnn, adOpenKeyset, adLockBatchOptimistic  ‘出现错误提示
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

Data.haozip01.zip (293.95 KB, 下载次数: 14)

2012-12-29 00:26 上传

点击文件名下载附件

Data.haozip02.zip (293.95 KB, 下载次数: 13)

2012-12-29 00:26 上传

点击文件名下载附件

Data.haozip03.zip (75.17 KB, 下载次数: 9)

2012-12-29 00:26 上传

点击文件名下载附件

A.accdb Table1                                                            B.accdb Table2
      
Error information

amazeyeli

insert into `d:\......\a.accdb`.table1
select * from `d:\........\b.accdb`.table2 as b
where b.name not in
(select name from `d:\..........\a.accdb`.table1)
and age > 20

237708857

amazeyeli 发表于 2012-12-29 11:13
insert into `d:\......\a.accdb`.table1
select * from `d:\........\b.accdb`.table2 as b
where b.na ...

谢谢 amazeyeli 大侠
但是我参照你的语句写成如下代码时，确出现“查询语法错误，查询句子不够完整”的错误提示。请帮忙检查代码，同时需要说明一点的是A、B这两个数据库都要密码的。万分感谢！
Sub ConbineData()
Dim cnn As New Connection
Dim rs As New Recordset
Dim Sql As String, spath As String, dt As Date
spath = ThisWorkbook.Path & "\A.accdb"
spath1 = ThisWorkbook.Path & "\B.accdb"
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Jet OLEDB:Database Password=123456; Data Source=" & spath
data1 = "Microsoft.ACE.OLEDB.12.0; Jet OLEDB:Database Password=123456; DATABASE=" & spath1
Sql = "insert into '" & spath & "'.Table1 select * from '" & spath1 & "'.Table2 as b where b.Sname not in (select Sname from '" & spath & "'.Table1) and Age > 20"
rs.Open Sql, cnn, adOpenKeyset, adLockBatchOptimistic
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

amazeyeli

``非''
``可以替换成[]

237708857

amazeyeli 发表于 2012-12-30 13:24
``非''
``可以替换成[]

amazeyeli,
谢谢！
``非''
不明白你的意思。

``可以替换成[]
我将SQL语句改成如下后系统提示“密码无效”，这两个数据库都是带有密码的，都是123456，但我不知道应该用语句表示.
Sub ConbineData()
Dim cnn As New Connection
Dim rs As New Recordset
Dim Sql As String, spath As String
spath = ThisWorkbook.Path & "\A.accdb"
spath1 = ThisWorkbook.Path & "\B.accdb"
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Jet OLEDB:Database Password=123456; Data Source=" & spath
data1 = "Database Password=123456; Database=" & spath1
Sql = "insert into [" & spath & "].Table1 select * from [" & spath1 & "].Table2 as b where b.Sname not in (select Sname from [" & spath & "].Table1) and Age > 20"
rs.Open Sql, cnn, adOpenKeyset, adLockBatchOptimistic
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

amazeyeli

vba怎么访问数据库我不懂,呵呵,我只会写sql语句

237708857

amazeyeli 发表于 2012-12-30 20:39
vba怎么访问数据库我不懂,呵呵,我只会写sql语句

真的十分感谢amazeyeli！当我将B.accdb数据库密码去掉后语句就可以正常运行，但加上密码就不行。
哪位高手指点下此例中应该怎样访问带有密码的B.accdb数据库？
谢谢！

amazeyeli

建议到vba版块问下