数据导入和导出的问题。高手请进!!!!急!!! 我把整个数据库里的数据导出到excel中时,所有的日期型数据和货币型数据都变成了错误的数据。(这些数据也是数字型的。)当从excel中导入到access时就会报错。请问怎么解决呢。要保持所有导出的数据都是和原来的数据一样的。谢谢! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 还有一个问题就是,我从excel中导入数据时怎么第一条导入不了呢?总是少了第一条数据。 代码?你可以用sql server的dts做一次,然后保存bas来研究一下 我的代码如下:我用的access数据库呀!Private Sub Command1_Click()'将Excel中的数据导入到Access数据表中Dim cn As New ADODB.ConnectionDim Con As New ADODB.ConnectionDim rs As New RecordsetDim rs1 As New RecordsetDim teacher_rs As New RecordsetDim teacher_rs1 As New RecordsetDim a As StringDim b As IntegerDim b1 As Integer Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=system.dll;Persist Security Info=False" cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source=mm.dll;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'" cn.Open Con.Open '设法取出数据表中的最后的编号值 rs1.Open "select * from mm ", Con, adOpenKeyset, adLockOptimistic If rs1.RecordCount <> 0 Then On Error GoTo hello1 rs1.MoveLast If rs1.Fields("编号") <> "" Then Text2.Text = rs1.Fields("编号") End If Elsehello1: End If '将取出的数值赋给b b = Int(Text2.Text) '将excel中的编号与b相加形成新编号,然后进行插入操作 rs.Open "select * From [Sheet1$]", cn, 1, 1 If rs.EOF = True Then Exit Sub End If While Not rs.EOF Con.Execute "Insert Into mm Values('" & rs(0) + b & "','" & rs(1) & "','" & rs(2) & "')" 'Con.Execute "Insert Into teacher Values('" & rs(0) + b & "','" & rs(1) & "','" & rs(2) & "','" & rs(3) & "','" & rs(4) & "','" & rs(5) & "','" & rs(6) & "','" & rs(7) & "','" & rs(8) & "','" & rs(9) & "','" & rs(10) & "','" & rs(11) & "','" & rs(12) & "','" & rs(13) & "','" & rs(14) & "','" & rs(15) & "','" & rs(16) & "','" & rs(17) & "','" & rs(18) & "','" & rs(19) & "','" & rs(20) & "','" & rs(21) & "','" & rs(22) & "','" & rs(23) & "','" & rs(24) & "','" & rs(25) & "','" & rs(26) & "','" & rs(27) & "')" rs.MoveNext Wendrs.Closers1.CloseSet rs = Nothing '设法取出数据表中的最后的编号值 teacher_rs1.Open "select * from teacher ", Con, adOpenKeyset, adLockOptimistic If teacher_rs1.RecordCount <> 0 Then On Error GoTo hello11 teacher_rs1.MoveLast If teacher_rs1.Fields("编号") <> "" Then Text2.Text = teacher_rs1.Fields("编号") End If Elsehello11: End If '将取出的数值赋给b b1 = Int(Text2.Text) '将excel中的编号与b相加形成新编号,然后进行插入操作 teacher_rs.Open "select * From [Sheet2$]", cn, 1, 1 If teacher_rs.EOF = True Then Exit Sub End If While Not teacher_rs.EOF 'Con.Execute "Insert Into mm Values('" & rs(0) + b & "','" & rs(1) & "','" & rs(2) & "')" Con.Execute "Insert Into teacher Values('" & teacher_rs(0) + b & "','" & teacher_rs(1) & "','" & teacher_rs(2) & "','" & teacher_rs(3) & "','" & teacher_rs(4) & "','" & teacher_rs(5) & "','" & teacher_rs(6) & "','" & teacher_rs(7) & "','" & teacher_rs(8) & "','" & teacher_rs(9) & "','" & teacher_rs(10) & "','" & teacher_rs(11) & "','" & teacher_rs(12) & "','" & teacher_rs(13) & "','" & teacher_rs(14) & "','" & teacher_rs(15) & "','" & teacher_rs(16) & "','" & teacher_rs(17) & "','" & teacher_rs(18) & "','" & teacher_rs(19) & "','" & teacher_rs(20) & "','" & teacher_rs(21) & "','" & teacher_rs(22) & "','" & teacher_rs(23) & "','" & teacher_rs(24) & "','" & teacher_rs(25) & "','" & teacher_rs(26) & "','" & teacher_rs(27) & "','" & teacher_rs(28) & "')" teacher_rs.MoveNext Wendteacher_rs.Closeteacher_rs1.CloseSet teacher_rs = Nothingcn.CloseSet cn = NothingCon.Closea = MsgBox("数据导入完毕!", vbInformation, "提示")End Sub listbox 属于哪个组件 如何打印MSFlexGrid中的内容? 关于RemoteData刷新数据的问题 关于ACTIVE REPORT的问题!急! 如何让文件选择对话框只先目录即可 (100分救助)如何实现在证件打印的时候,既可以打出背景图片,又可以打印出文字. TextBox的奇怪现象 关于ado访问数据库的问题,谢谢 媒体????? 高手来帮我看下这段代码行不? now_row% = ActiveCell.Row这一句中的 % 是什么意思啊? 一个关于数据库保存的问题,在线等啊。。。。
总是少了第一条数据。
我用的access数据库呀!
Private Sub Command1_Click()
'将Excel中的数据导入到Access数据表中
Dim cn As New ADODB.Connection
Dim Con As New ADODB.Connection
Dim rs As New Recordset
Dim rs1 As New Recordset
Dim teacher_rs As New Recordset
Dim teacher_rs1 As New Recordset
Dim a As String
Dim b As Integer
Dim b1 As Integer
Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=system.dll;Persist Security Info=False"
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source=mm.dll;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
cn.Open
Con.Open
'设法取出数据表中的最后的编号值
rs1.Open "select * from mm ", Con, adOpenKeyset, adLockOptimistic
If rs1.RecordCount <> 0 Then
On Error GoTo hello1
rs1.MoveLast
If rs1.Fields("编号") <> "" Then
Text2.Text = rs1.Fields("编号")
End If
Else
hello1:
End If '将取出的数值赋给b
b = Int(Text2.Text)
'将excel中的编号与b相加形成新编号,然后进行插入操作
rs.Open "select * From [Sheet1$]", cn, 1, 1
If rs.EOF = True Then
Exit Sub
End If
While Not rs.EOF
Con.Execute "Insert Into mm Values('" & rs(0) + b & "','" & rs(1) & "','" & rs(2) & "')"
'Con.Execute "Insert Into teacher Values('" & rs(0) + b & "','" & rs(1) & "','" & rs(2) & "','" & rs(3) & "','" & rs(4) & "','" & rs(5) & "','" & rs(6) & "','" & rs(7) & "','" & rs(8) & "','" & rs(9) & "','" & rs(10) & "','" & rs(11) & "','" & rs(12) & "','" & rs(13) & "','" & rs(14) & "','" & rs(15) & "','" & rs(16) & "','" & rs(17) & "','" & rs(18) & "','" & rs(19) & "','" & rs(20) & "','" & rs(21) & "','" & rs(22) & "','" & rs(23) & "','" & rs(24) & "','" & rs(25) & "','" & rs(26) & "','" & rs(27) & "')"
rs.MoveNext
Wendrs.Close
rs1.Close
Set rs = Nothing '设法取出数据表中的最后的编号值
teacher_rs1.Open "select * from teacher ", Con, adOpenKeyset, adLockOptimistic
If teacher_rs1.RecordCount <> 0 Then
On Error GoTo hello11
teacher_rs1.MoveLast
If teacher_rs1.Fields("编号") <> "" Then
Text2.Text = teacher_rs1.Fields("编号")
End If
Else
hello11:
End If '将取出的数值赋给b
b1 = Int(Text2.Text)
'将excel中的编号与b相加形成新编号,然后进行插入操作
teacher_rs.Open "select * From [Sheet2$]", cn, 1, 1
If teacher_rs.EOF = True Then
Exit Sub
End If
While Not teacher_rs.EOF
'Con.Execute "Insert Into mm Values('" & rs(0) + b & "','" & rs(1) & "','" & rs(2) & "')"
Con.Execute "Insert Into teacher Values('" & teacher_rs(0) + b & "','" & teacher_rs(1) & "','" & teacher_rs(2) & "','" & teacher_rs(3) & "','" & teacher_rs(4) & "','" & teacher_rs(5) & "','" & teacher_rs(6) & "','" & teacher_rs(7) & "','" & teacher_rs(8) & "','" & teacher_rs(9) & "','" & teacher_rs(10) & "','" & teacher_rs(11) & "','" & teacher_rs(12) & "','" & teacher_rs(13) & "','" & teacher_rs(14) & "','" & teacher_rs(15) & "','" & teacher_rs(16) & "','" & teacher_rs(17) & "','" & teacher_rs(18) & "','" & teacher_rs(19) & "','" & teacher_rs(20) & "','" & teacher_rs(21) & "','" & teacher_rs(22) & "','" & teacher_rs(23) & "','" & teacher_rs(24) & "','" & teacher_rs(25) & "','" & teacher_rs(26) & "','" & teacher_rs(27) & "','" & teacher_rs(28) & "')"
teacher_rs.MoveNext
Wendteacher_rs.Close
teacher_rs1.Close
Set teacher_rs = Nothingcn.Close
Set cn = Nothing
Con.Close
a = MsgBox("数据导入完毕!", vbInformation, "提示")
End Sub