vb中使用sql语句查询access中的t1表,有条件将记录导出到excel中去
写成sql语句,请考虑执行效率,数据可能有2k都有可能,一条一条记录执行的话慢死了access路径:app.path & "\sale.mdb"
excel路径: app.path & "\sale.xsl"
Z$ = " from MeBack where f1>10 and f2=5"
sql = "select * INTO OPENDATASOURCE('microsoft.Jet.OLEDB.4.0','Data Source=""" & xPath & """;User ID=Admin;Password=;Extended Properties=Excel 8.0')..." & "[sheet1$]"
sql = sql & Z$
http://community.csdn.net/Expert/topic/4790/4790025.xml?temp=.1682398
写成sql语句,请考虑执行效率,数据可能有2k都有可能,一条一条记录执行的话慢死了access路径:app.path & "\sale.mdb"
excel路径: app.path & "\sale.xsl"
Z$ = " from MeBack where f1>10 and f2=5"
sql = "select * INTO OPENDATASOURCE('microsoft.Jet.OLEDB.4.0','Data Source=""" & xPath & """;User ID=Admin;Password=;Extended Properties=Excel 8.0')..." & "[sheet1$]"
sql = sql & Z$
http://community.csdn.net/Expert/topic/4790/4790025.xml?temp=.1682398
Dim i As Long
Dim xPath As String
bb = MsgBox("真的要删除这些记录?", 4 + 256, "尖锋销售系统")
If bb = 7 Then Exit Sub
'创建并保存excel
Dim VBExcel As Object, xBook As Object, xSheet As Object, j%
Set VBExcel = CreateObject("excel.application")
Set xBook = VBExcel.Workbooks.Add
Set xSheet = xBook.Worksheets(1)
'先判断保存备份数据的文件夹存在不
Dim fso As New FileSystemObject
If fso.FolderExists(App.Path & "\backupDelete") = False Then
fso.CreateFolder (App.Path & "\backupDelete")
End If
xPath = App.Path & "\backupDelete\" & Replace(CStr(Now), ":", "_") & tableNameDLTDLT & ".xls"
VBExcel.Workbooks(1).SaveAs xPath
Text1 = xPath
VBExcel.Workbooks(1).Close
VBExcel.Quit
Set VBExcel = Nothing
'MsgBox "Excel创建成功" 这里有可能是人为原因让excel访问不到
'把要删除的数据备份到新建的excel中
Dim startDate As String, endDate As String, DateField As String
startDate = DTPicker1.Value & " 00:00:00"
endDate = DTPicker2.Value & " 23:59:59"
Select Case tableNameDLTDLT
Case "FinishedDeal"
DateField = "dealDate"
Case "inList"
DateField = "inDate"
Case "ClientReturn"
DateField = "returnTime"
Case "MeBack"
DateField = "backDate"
End Select
Dim newSheet As String
newSheet = "sheet1"
Dim sql As String
Dim mmm As String
mmm = "select number from " & tableNameDLTDLT & " where " & DateField & " between #" & CDate(startDate) & "# and #" & CDate(endDate) & "#"
'On Error Resume Next
sql = "insert INTO [Excel 8.0;Database=" & xPath & "].[" & newSheet & "$](A)('" & mmm & "')"
Text2 = sql
Call ADOEXCEL(sql) '这个函数没问题
MsgBox "恭喜恭喜"
高人看看啊