我做好了一个通用的导入窗体,分追加导入跟全新导入(先清空数据库,然后再导入):SjyTblName设定为公共变量的数据源表名,XlsName设定为公共变量的导入表表名.
1:追加导入:
Sql2 = "INSERT into " & SjyTblName & " SELECT * FROM tblouttable"
If MsgBox("您确定需要追加数据吗?", vbYesNo + vbInformation, Forms!usysfrmLogin.Caption) = vbYes Then
If Dir(CurrentProject.Path + "\批量导入模版\" & XlsName) = "" Then
MsgBox "批量导入模版文件夹中不存在" & XlsName & "文件!", vbCritical, "提示"
Exit Sub
Else
If fExistTable("tblOuttable") = True Then '如果存在这个表,则删除
DoCmd.RunSQL "drop table tblOuttable"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblouttable", CurrentProject.Path & "\批量导入模版\" & XlsName, True
DoCmd.RunSQL Sql2
MsgBox ("数据追加成功!")
Forms!usysfrmMain!frmChild.SourceObject = FrmName
Else
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblouttable", CurrentProject.Path & "\批量导入模版\" & XlsName, True
DoCmd.RunSQL Sql2
MsgBox ("数据追加成功!")
Forms!usysfrmMain!frmChild.SourceObject = FrmName
End If
DoCmd.SetWarnings True
End If
End If
2:全新导入:
Sql1 = "delete * from " & SjyTblName
Sql2 = "INSERT into " & SjyTblName & " SELECT * FROM tblouttable"
If MsgBox("您确定需要初始化所有数据后进行导入吗?", vbYesNo + vbInformation, Forms!usysfrmLogin.Caption) = vbYes Then
DoCmd.RunSQL Sql1
Forms!usysfrmMain!frmChild.SourceObject = FrmName
If Dir(CurrentProject.Path + "\批量导入模版\" & XlsName) = "" Then
MsgBox "批量导入模版文件夹中不存在" & XlsName & "文件!", vbCritical, "提示"
Exit Sub
Else
If fExistTable("tblOuttable") = True Then '如果存在这个表,则删除
DoCmd.RunSQL "drop table tblOuttable"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblouttable", CurrentProject.Path & "\批量导入模版\" & XlsName, True
DoCmd.RunSQL Sql2
MsgBox ("数据已经全新导入成功!")
Forms!usysfrmMain!frmChild.SourceObject = FrmName
Else
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblouttable", CurrentProject.Path & "\批量导入模版\" & XlsName, True
DoCmd.RunSQL Sql2
MsgBox ("数据已经全新导入成功!")
Forms!usysfrmMain!frmChild.SourceObject = FrmName
End If
DoCmd.SetWarnings True
End If
End If