如何用VB6.0将excel表中的数据导入到access数据库中 15
PrivateSubCommand1_Click()DimcnAsConnectionDimrsAsRecordsetDimsqlAsStringDimnAsLong,m...
Private Sub Command1_Click()
Dim cn As Connection
Dim rs As Recordset
Dim sql As String
Dim n As Long, m As Long
Set cn = New Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\test.mdb;Persist Security Info=False"
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source= d:\test.xls;Extended Properties='Excel 8.0;HDR=Yes'"
sql = "SELECT * From [Sheet1$] IN " & Chr(34) & App.Path & "\test.xls" & Chr(34) & Chr(32) & Chr(34) & "EXCEL 8.0;" & Chr(34)
Debug.Print sql
Set rs = cn.Execute(sql)
While Not rs.EOF
sql = "insert into tb(id,num,dt) values ('" & rs.Fields(0) & "'," & Val(rs.Fields(1)) & ",#" & rs.Fields(2) & "#)"
cn.Execute sql, n
m = m + n
rs.MoveNext
Wend
Set rs = Nothing
Set cn = Nothing
MsgBox "成功写入数据:" & m
End Sub
以上是我写的代码,数据库和excel表都在D盘下,其中access中表名叫tb,有三个字段分别是id,num,dt。分别是字符型,数据型,日期型的。现在想把excel中的数据导入这个表,程序运行的时候显示错误:对象打开时不允许操作。请各位高手解答一下,帮我实现此功能。如果答案帮解决问题的话可以加分。复制的就别来了 展开
Dim cn As Connection
Dim rs As Recordset
Dim sql As String
Dim n As Long, m As Long
Set cn = New Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\test.mdb;Persist Security Info=False"
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source= d:\test.xls;Extended Properties='Excel 8.0;HDR=Yes'"
sql = "SELECT * From [Sheet1$] IN " & Chr(34) & App.Path & "\test.xls" & Chr(34) & Chr(32) & Chr(34) & "EXCEL 8.0;" & Chr(34)
Debug.Print sql
Set rs = cn.Execute(sql)
While Not rs.EOF
sql = "insert into tb(id,num,dt) values ('" & rs.Fields(0) & "'," & Val(rs.Fields(1)) & ",#" & rs.Fields(2) & "#)"
cn.Execute sql, n
m = m + n
rs.MoveNext
Wend
Set rs = Nothing
Set cn = Nothing
MsgBox "成功写入数据:" & m
End Sub
以上是我写的代码,数据库和excel表都在D盘下,其中access中表名叫tb,有三个字段分别是id,num,dt。分别是字符型,数据型,日期型的。现在想把excel中的数据导入这个表,程序运行的时候显示错误:对象打开时不允许操作。请各位高手解答一下,帮我实现此功能。如果答案帮解决问题的话可以加分。复制的就别来了 展开
2个回答
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询