求VB中多记录集的数据传递问题。

[sckdgzchs]

1、我的数据库为ACCESS格式的数据库，库名为data，连接对象为database。其中有两个表，一个是byk表，另一个是sbb表。现在我想能过两个记录集完成将byk表中的数据全部插入到sbb中。这两个表主要是字段长度不一致。
2、现在我的代码如下：
Dim rssbb As New ADODB.Recordset
Dim rsbyk As New ADODB.Recordset
rsbyk.Open "select * from byk ", database, adOpenKeyset, adLockBatchOptimistic, 1
rssbb.Open "select * from sbb ", database, adOpenForwardOnly, adLockBatchOptimistic, 1
Do While Not rsbyk.EOF
Do While rssbb.EOF
rssbb.AddNew
rssbb.Fields("xh").Value = "123" 'Trim(rsbyk.Fields("xh").Value)
rssbb.Fields("ksh").Value = Trim(rsbyk.Fields("ksh").Value)
rssbb.Update
Loop
rsbyk.MoveNext
Loop
MsgBox "成功"
3、问题：现在程序不报错但sbb表中没有数据插入。
4、请各位大侠帮忙。一旦解决马上给分。

[newtown555]

Dim rssbb As New ADODB.Recordset 
Dim rsbyk As New ADODB.Recordset 
rsbyk.Open "select * from byk ", database, adOpenDynamic, adLockOptimistic, 1 
rssbb.Open "select * from sbb ", database, adOpenDynamic, adLockOptimistic, 1 
Do While Not rsbyk.EOF 
    rssbb.AddNew 
    rssbb.Fields("xh").Value = "123" 'Trim(rsbyk.Fields("xh").Value) 
    rssbb.Fields("ksh").Value = Trim(rsbyk.Fields("ksh").Value) 
    rssbb.Update 
    rsbyk.MoveNext 
Loop 
MsgBox "成功" 

[饮水需思源]

Dim rsbyk As New ADODB.Recordset
dim i as long
dim strsql as string
rsbyk.Open "select * from byk ", database, adOpenKeyset,adlockreadonly
for i=1 to rsbyk.recordcount
strsql=" insert into sbb(xh,ksh) values('123','"& trim(rsbyk!ksh) &"')"
database.execute strsql
rsbyk.movenext
next i
rsbyk.close
MsgBox "成功"

[sckdgzchs]

回一楼，通不过
提示为有行数超过多行，有挂起的。

[sckdgzchs]

回二楼
提示为字段太小面不能接受所要添加的数据的数量，试着插入或粘贴较少的数据。

[newtown555]

你的表结构是什么样的,如果有列没有默认值,又不插入数据的话,插入就会失败

[lcsfxs]

下面是部分代码,仅供参考,是把adojnb表插入adojna表
Dim lngCount As Integer
Dim a, b, i As Integer
Dim excel As String

With ADOjnb
If .State = adStateOpen Then
Set .ActiveConnection = Nothing
.Close
End If

.ActiveConnection = ADOsdb
.CursorLocation = adUseClient '这里一定要写aduseclient，我试了另外几种都不行
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open "SELECT * From db order by 序号", Options:=adCmdText
End With


If sfile = "" Then
MsgBox "您选择的EXCEL表不存在，请重新导入！", vbInformation, "抱歉！"
Exit Sub
End If

a = 1
For i = 1 To 50
a = InStr(a, sfile, "\")
If a = 0 Then
excel = Mid(sfile, b) & sheet
Exit For
End If
a = a + 1
b = a
Next

With ADOjna
If .RecordCount > 0 Then
.MoveFirst
.Find "bm ='" & excel & "'"
If .EOF = False Then
If .Fields("bm").Value = excel Then
MsgBox "该表已经被添加，请您选择另一张表！", vbInformation, "抱歉！"
Exit Sub
End If
End If
End If
End With


ADOjnb.MoveFirst

Do Until ADOjnb.EOF



ADOjna.AddNew



ADOjna![序号] = IIf(IsNull(ADOjnb![序号]), "", ADOjnb![序号])
ADOjna![客户名称] = IIf(IsNull(ADOjnb![用户姓名]), "", ADOjnb![用户姓名])
ADOjna![地区或受理局] = IIf(IsNull(ADOjnb![地区名称]), "", ADOjnb![地区名称])

ADOjna![固定电话] = IIf(IsNull(ADOjnb![上网帐号]), "", ADOjnb![上网帐号])
ADOjna![联系电话] = IIf(IsNull(ADOjnb![联系电话]), "", ADOjnb![联系电话])
ADOjna![登记编号] = IIf(IsNull(ADOjnb![BSS业务流水号]), "", ADOjnb![BSS业务流水号])
ADOjna![送货地址] = IIf(IsNull(ADOjnb![地址]), "", ADOjnb![地址])

ADOjna![宽带类型] = IIf(IsNull(ADOjnb![宽带类型]), "", ADOjnb![宽带类型])
ADOjna![受理时间] = IIf(IsNull(ADOjnb![受理时间]), "", ADOjnb![受理时间])
ADOjna![受理工号] = IIf(IsNull(ADOjnb![受理工号]), "", ADOjnb![受理工号])
ADOjna![机型] = IIf(IsNull(ADOjnb![机型]), "", ADOjnb![机型])

ADOjna![机型序列号] = IIf(IsNull(ADOjnb![机型序列号]), "0", ADOjnb![机型序列号])
ADOjna![显示器] = IIf(IsNull(ADOjnb![显示器]), "", ADOjnb![显示器])
ADOjna![显示器序列号] = IIf(IsNull(ADOjnb![显示器序列号]), "0", ADOjnb![显示器序列号])
ADOjna![送货时间] = IIf(IsNull(ADOjnb![送货时间]), "1999-1-1", ADOjnb![送货时间])

ADOjna![安装情况] = IIf(IsNull(ADOjnb![安装情况]), "", ADOjnb![安装情况])
ADOjna![原由] = IIf(IsNull(ADOjnb![原由]), "", ADOjnb![原由])
ADOjna![完工时间] = IIf(IsNull(ADOjnb![完工时间]), "", ADOjnb![完工时间])
ADOjna![安装工程师] = IIf(IsNull(ADOjnb![安装工程师]), "", ADOjnb![安装工程师])

ADOjna![满意度] = IIf(IsNull(ADOjnb![满意度]), "", ADOjnb![满意度])
ADOjna![地区] = IIf(IsNull(ADOjnb![地区]), "", ADOjnb![地区])
ADOjna![其它] = IIf(IsNull(ADOjnb![其它]), "", ADOjnb![其它])
ADOjna![bm] = IIf(IsNull(excel), "", excel)
ADOjna![excel] = IIf(IsNull(Mid(sfile, b)), "", Mid(sfile, b))
ADOjna![sheet] = IIf(IsNull(sheet), "", sheet)

If ADOjnb![机型] = "g2006cn" Or ADOjnb![机型] = "g2016cn" Or ADOjnb![机型] <> "" Then
ADOjna![zjs] = 1
Else
ADOjna![zjs] = 0
End If

If ADOjnb![显示器] = "hpvs17e" Or ADOjnb![显示器] = "hpmx705" Or ADOjnb![显示器] = "hpmx705e" Or ADOjnb![显示器] <> "" Then
ADOjna![xsqs] = 1
Else
ADOjna![xsqs] = 0
End If


ADOjna.UpdateBatch
ADOjnb.MoveNext


Loop

With ADOjna
If .State = adStateOpen Then
Set .ActiveConnection = Nothing
.Close
End If

.ActiveConnection = ADOsdb
.CursorLocation = adUseClient '这里一定要写aduseclient，我试了另外几种都不行
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open "SELECT * From dnhzb order by 序号", Options:=adCmdText
End With

[Tiger_Zhao]

将长的字段值赋给短的字段值应该根据长度用 Left 函数截短。比如

database.Execute "INSERT INTO sbb SELECT xh, Left(ksh,3) FROM byk"

[newtown555]

其实,如果你不对数据进行特别处理的话,用下面的方法直接插入多条记录会好一些

database.Execute "insert into sbb (xh,ksh) select trim(xh),trim(ksh) from byk",,1