'EXCEL导入ACCESS模块声明
'调用ExportExportExcelSheetToAccess"sSheetName", "sExcelPath", "AccessTable", "sAccessDBPath"
'还要引用Microsoft DAO3.6 Objects Library
Private Sub ExportExcelSheetToAccess(sSheetName As String, sExcelPath As String, sAccessTable As String, sAccessDBPath As String)
Dim db As Database
Dim rs As Recordset Set db = OpenDatabase(App.Path & sExcelPath, True, False, "Excel 8.0")
'Call db.Execute("select * into [;database=" & App.Path & sAccessDBPath & "]." & sAccessTable & " from [" & sSheetName & "$] ")
Call db.Execute("Delete * from [;database=" & App.Path & sAccessDBPath & "]." & sAccessTable & " ")
Call db.Execute("Insert Into [;database=" & App.Path & sAccessDBPath & "]." & sAccessTable & " Select * FROM [" & sSheetName & "$]")
End Sub以上为数据导入模块。
很多数据都正常,只是有少数数据导入为空,请问是何原因。数据库是用来做统计的,绝对不允许有数据为空,否则会出大乱子的。谢谢大家了。帮我分析分析原因在线等
'调用ExportExportExcelSheetToAccess"sSheetName", "sExcelPath", "AccessTable", "sAccessDBPath"
'还要引用Microsoft DAO3.6 Objects Library
Private Sub ExportExcelSheetToAccess(sSheetName As String, sExcelPath As String, sAccessTable As String, sAccessDBPath As String)
Dim db As Database
Dim rs As Recordset Set db = OpenDatabase(App.Path & sExcelPath, True, False, "Excel 8.0")
'Call db.Execute("select * into [;database=" & App.Path & sAccessDBPath & "]." & sAccessTable & " from [" & sSheetName & "$] ")
Call db.Execute("Delete * from [;database=" & App.Path & sAccessDBPath & "]." & sAccessTable & " ")
Call db.Execute("Insert Into [;database=" & App.Path & sAccessDBPath & "]." & sAccessTable & " Select * FROM [" & sSheetName & "$]")
End Sub以上为数据导入模块。
很多数据都正常,只是有少数数据导入为空,请问是何原因。数据库是用来做统计的,绝对不允许有数据为空,否则会出大乱子的。谢谢大家了。帮我分析分析原因在线等
这是连接字符串说明
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
"HDR=Yes;" indicates that the first row contains columnnames, not data
"IMEX=1;" tells the driver to always read "intermixed" data columns as text
TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.