读一个写一个肯定很慢了 试试这样INSERT INTO Table1 SELECT * FROM Table2 in "c:\db.mdb" WHERE ……
同意楼上 直接用SQL 的 INSERT 插入语句
insert into 表1 select * from 表2 where 表2.某字段 not in (select 某字段 from 表1) 一剑双铞
这样试一试(Adodc2.Recordset.Fields(...)也会占用不少时间): Dim rsFields as Fields set rsFields=Adodc2.Recordset.Fields...... .Fields("Pname") = rsFields("Pname") ......set rsFields=Nothing
phyleepf(土匪) 的办法是可行的。1。select * from 表2 where 表2.某字段 IN (select 某字段 from 表1) 检查重复记录,并提示是否覆盖。2。insert into 表1 select * from 表2 where 表2.某字段 not IN (select 某字段 from 表1)
最简单方法 没有的纪录插入 Insert into table1 a SELECT * FROM [;database=c:\DataBase2.mdb].table2 b where b.[主键] not in (select [主键] from table1) 有的纪录插入 Insert into table1 a SELECT * FROM [;database=c:\DataBase2.mdb].table2 b where b.[主键] in (select [主键] from table1) 查询出有的纪录 SELECT * FROM [;database=c:\DataBase2.mdb].table2 b where b.[主键] in (select [主键] from table1)
TO zyg0(影子(如影随行) 能不能把代码写详细些啊(2个ACCESS数据库都是有密码的),我试了老报错.谢谢
newStrRs = "insert into [" + okPath + ";pwd=pwd].[Product] select * from Product where Product.Pserial in (select Pserial from [" + dbPath + ";pwd=123].[Product])" newCon.Execute newStrRs
newStrRs = "insert into [" + dbPath + ";pwd=pwd].[Product] select * from Product where Product.Pserial not in (select Pserial from [" + dbPath + ";pwd=123].[Product])" newCon.Execute newStrRs当导1.MDB的数据去2.MDB时,如果1.MDB中的表Product的字段Pserial有与2.MDB中的表Product的字段Pserial相同的记录,则这些记录被导出到,一个临时生成的MDB文件里.而不同的记录则都导入到2.MDB中. 但是现在在执行 newStrRs = "insert into [" + dbPath + ";pwd=pwd].[Product] select * from Product where Product.Pserial not in (select Pserial from [" + dbPath + ";pwd=123].[Product])" newCon.Execute newStrRs 报错:"由于将在索引、主关键字、或关系中创建重复的值,请求对表的改变没有成功.改变该字段中的或包含重复数据的字段中的数据,删除索引或重新定义索引以允许重复的值并再试一次." 怎么会这样啊.
strConUpdate = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Trim(Text1.Text) & ";Persist Security Info=False;Jet OLEDB:Database password=123"
Adodc2.ConnectionString = strConUpdate
Adodc2.RecordSource = strRs
Adodc2.RefreshbUpdate = True
For i = 0 To Adodc2.Recordset.RecordCount - 1 strRs = "select top 1 * from PUB"
Adodc1.ConnectionString = strCon '更新目标为主数据库!
Adodc1.RecordSource = strRs
Adodc1.Refresh
With Adodc1.Recordset
.AddNew
.Fields("Pname") = Adodc2.Recordset.Fields("Pname")
.Fields("Pserial") = Adodc2.Recordset.Fields("Pserial")
.Fields("Pprice") = Adodc2.Recordset.Fields("Pprice")
.Fields("Pdate") = Adodc2.Recordset.Fields("Pdate")
.Fields("Pno") = Adodc2.Recordset.Fields("Pno")
.Fields("Pvalidate") = Adodc2.Recordset.Fields("Pvalidate")
.Fields("Pcharge") = Adodc2.Recordset.Fields("Pcharge")
.Fields("P") = Adodc2.Recordset.Fields("P")
.Fields("Pcall") = Adodc2.Recordset.Fields("Pcall")
.Fields("Cname") = Adodc2.Recordset.Fields("Cname")
.Fields("Cage") = Adodc2.Recordset.Fields("Cage")
.Fields("Ccareer") = Adodc2.Recordset.Fields("Ccareer")
.Fields("Ctrade") = Adodc2.Recordset.Fields("Ctrade")
.Fields("Cincome") = Adodc2.Recordset.Fields("Cincome")
.Fields("Cjob") = Adodc2.Recordset.Fields("Cjob")
.Fields("Cmedia") = Adodc2.Recordset.Fields("Cmedia")
.Fields("Cuse") = Adodc2.Recordset.Fields("Cuse")
.Fields("Cplace") = Adodc2.Recordset.Fields("Cplace")
.Fields("Ccompany") = Adodc2.Recordset.Fields("Ccompany")
.Fields("Cphon") = Adodc2.Recordset.Fields("Cphon")
.Fields("Cadre") = Adodc2.Recordset.Fields("Cadre")
.Fields("Cmobile") = Adodc2.Recordset.Fields("Cmobile")
.Fields("Cemail") = Adodc2.Recordset.Fields("Cemail")
.Fields("Cservice") = Adodc2.Recordset.Fields("Cservice")
.Fields("Meter") = Adodc2.Recordset.Fields("Meter")
.Fields("Mphon") = Adodc2.Recordset.Fields("Mphon")
.Fields("Madre") = Adodc2.Recordset.Fields("Madre")
.Fields("Mplace") = Adodc2.Recordset.Fields("Mplace")
.Fields("Mperson") = Adodc2.Recordset.Fields("Mperson")
.Fields("Mauthorizor") = Adodc2.Recordset.Fields("Mauthorizor")
' .Fields("Tmemo") = Adodc2.Recordset.Fields("Tmemo")
.Update
End With
End If
Adodc2.Recordset.MoveNext
Next
试试这样INSERT INTO Table1
SELECT * FROM Table2 in "c:\db.mdb"
WHERE ……
直接用SQL 的 INSERT 插入语句
一剑双铞
Dim rsFields as Fields
set rsFields=Adodc2.Recordset.Fields......
.Fields("Pname") = rsFields("Pname")
......set rsFields=Nothing
VB完成把ACCESS中10000条记录,导出到另一个ACCESS文件中,速度比较慢,导入的同时还要检查是否有相同的数据,遇到有相同记录时,还得提示,是否覆盖,选是覆盖,选否保留.15000条记录在IBM T41上要花费20分钟的时间.
求教各位高手,有好的办法吗?使速度变快些,谢谢:)
只要是一个"编号"的字段相同就算相同.
检查重复记录,并提示是否覆盖。2。insert into 表1 select * from 表2 where 表2.某字段 not IN (select 某字段 from 表1)
可以使用sql插入方法,sql语句可以50条提交一次
sql语句之间用;格开,然后提交connection执行就可以.
我测试了一下,前者使用10秒,后者是6秒.
没有的纪录插入
Insert into table1 a
SELECT * FROM [;database=c:\DataBase2.mdb].table2 b where b.[主键] not in (select [主键] from table1)
有的纪录插入
Insert into table1 a
SELECT * FROM [;database=c:\DataBase2.mdb].table2 b where b.[主键] in (select [主键] from table1)
查询出有的纪录
SELECT * FROM [;database=c:\DataBase2.mdb].table2 b where b.[主键] in (select [主键] from table1)
能不能把代码写详细些啊(2个ACCESS数据库都是有密码的),我试了老报错.谢谢
我按照 Begin2008(重头再来) 的办法,但是现在老出错啊,我的代码如下: CreateLocalDatabase (okPath)
newStrRs = "insert into [" + okPath + ";pwd=pwd].[Product] select * from Product where Product.Pserial in (select Pserial from [" + dbPath + ";pwd=123].[Product])"
newCon.Execute newStrRs
newStrRs = "insert into [" + dbPath + ";pwd=pwd].[Product] select * from Product where Product.Pserial not in (select Pserial from [" + dbPath + ";pwd=123].[Product])"
newCon.Execute newStrRs当导1.MDB的数据去2.MDB时,如果1.MDB中的表Product的字段Pserial有与2.MDB中的表Product的字段Pserial相同的记录,则这些记录被导出到,一个临时生成的MDB文件里.而不同的记录则都导入到2.MDB中.
但是现在在执行
newStrRs = "insert into [" + dbPath + ";pwd=pwd].[Product] select * from Product where Product.Pserial not in (select Pserial from [" + dbPath + ";pwd=123].[Product])"
newCon.Execute newStrRs
报错:"由于将在索引、主关键字、或关系中创建重复的值,请求对表的改变没有成功.改变该字段中的或包含重复数据的字段中的数据,删除索引或重新定义索引以允许重复的值并再试一次."
怎么会这样啊.