将DAO QueryDef保存到临时查询并将结果输出到Excel [英] Save DAO QueryDef to Temporary Query and Output Results to Excel

问题描述

所以我有一个Access项目，首先我在其中创建了一个TempQuery，并使用该查询将结果输出到一个效果很好的Excel文件中。

So I have this Access project where at first I created a TempQuery and used that query to output the results to an Excel file which works great.

这里是代码：

    Dim qdf As QueryDef

    DoCmd.DeleteObject acQuery, "qryTemp"
    Set qdf = CurrentDb.CreateQueryDef("qryTemp", Me.Child13.Form.RecordSource)
    DoCmd.OutputTo acOutputQuery, "qryTemp", acFormatXLS, , True

    Exit Sub

我现在想做的是同一件事，但是通过DAO连接使用MySQL数据库，因为我仅将此Access文件用作前端。

What I'm trying to do now is the same thing but using a MySQL database through DAO connection as I'm using this Access file only as Front End.

这是我到目前为止所得到的：

Here's what I got thus far:

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim SQL As String


    Set db = OpenDatabase("", False, False, Globales.ConnString)

    SQL = "SELECT tbl1Facturas.Verificado, tbl1Facturas.Factura, tbl1Facturas.Fecha, tbl5Localidades.NombreLocalidad, tbl6Suplidores.NombreSuplidor, tbl1Facturas.Subtotal, tbl1Facturas.[IVU MUNICIPAL], tbl1Facturas.[IVU ESTATAL], tbl1Facturas.[Total de Compra], tbl1Facturas.[Exento al IVU ESTATAL], tbl1Facturas.[Credito al Subtotal], tbl1Facturas.[Credito IVU Municipal], tbl1Facturas.[Credito IVU ESTATAL], tbl1Facturas.[Metodo de Pago], tbl1Facturas.[ID Metodo Pago], tbl1Facturas.MetodoPago_PDF, tbl1Facturas.Factura_PDF " _
        & "FROM (tbl1Facturas INNER JOIN tbl5Localidades ON tbl1Facturas.Localidad_ID = tbl5Localidades.ID) INNER JOIN tbl6Suplidores ON tbl1Facturas.Suplidor_ID = tbl6Suplidores.ID " _
        & "WHERE MONTH(tbl1Facturas.Fecha) = Month(#" & Me.Text19 & "#) " _
        & "AND YEAR(tbl1Facturas.Fecha) = Year(#" & Me.Text19 & "#) " _
        & "AND tbl1Facturas.Localidad_ID = " & Me.Combo23.Column(0) & " " _
        & "ORDER BY tbl1Facturas.Fecha; "

    Set qdf = db.CreateQueryDef("qryTemp", SQL)

    DoCmd.OutputTo acOutputQuery, "qryTemp", acFormatXLS, , True
    Exit Sub

欢迎所有帮助！ ：P

All help welcome ! :P

推荐答案

修改后的导出代码在远程数据库中创建（或尝试）查询。由于远程数据库是MySQL，我希望尝试使用QueryDefs创建查询失败。即使创建了查询，OutputTo也会在本地数据库中查找该查询，但找不到它。

The revised export code creates (or tries to) a query in the remote db. Since the remote db is MySQL, I expect attempting to create query with QueryDefs fails. Even if the query were created, OutputTo looks in the local db for the query and won't find it.

请考虑以下示例，该示例是从另一个成功导出的Access数据库中提取的：

Consider this example pulling from another Access db which does successfully export:

Dim qdf As DAO.QueryDef
DoCmd.DeleteObject acQuery, "qryTemp"
Set qdf = CurrentDb.CreateQueryDef("qryTemp", "SELECT * FROM sometable  IN '\\servername\path\DBname.accdb'")
DoCmd.OutputTo acOutputQuery, "qryTemp", acFormatXLS, , True

该过程完成后，前端中将保存一个查询对象（尽管它不会立即显示在导航窗格中）。由于创建了对象，因此只需创建一个表并导出该表即可。不幸的是，我看不到如何在QueryDefs或SELECT INTO操作中引用MySQL数据库，因此该对象将在前端创建。可能可以遍历一个记录集，一次将数据写到一个表中。

When the procedure finishes, there is a saved query object in the frontend (although it won't show in Navigation Pane immediately). Since an object is created, could just create a table and export the table. Unfortunately, I can't see how to reference the MySQL database in QueryDefs or SELECT INTO action so the object would be created in frontend. Could probably loop through a recordset and write data to table one record at a time.

我只能看到的另一种选择是使用Excel自动化。打开Excel对象，并使用CopyFromRecordset方法导出到工作表范围。

Only other alternative I can see is to use Excel automation. Open Excel object and export to worksheet range with CopyFromRecordset method.

Dim db As DAO.Database, rs As DAO.Recordset
Dim xl As Excel.Application, wb As Excel.Workbook

Set db = OpenDatabase("", False, False, Globales.ConnString)

Set rs = db.OpenRecordset("SELECT Verificado, Factura, Fecha, NombreLocalidad, NombreSuplidor, Subtotal, [IVU MUNICIPAL], [IVU ESTATAL], [Total de Compra], [Exento al IVU ESTATAL], [Credito al Subtotal], [Credito IVU Municipal], [Credito IVU ESTATAL], [Metodo de Pago], [ID Metodo Pago], MetodoPago_PDF, Factura_PDF " _
    & "FROM (tbl1Facturas INNER JOIN tbl5Localidades ON tbl1Facturas.Localidad_ID = tbl5Localidades.ID) " _
    & "INNER JOIN tbl6Suplidores ON tbl1Facturas.Suplidor_ID = tbl6Suplidores.ID " _
    & "WHERE MONTH(tbl1Facturas.Fecha) = Month(#" & Me.Text19 & "#) " _
    & "AND YEAR(tbl1Facturas.Fecha) = Year(#" & Me.Text19 & "#) " _
    & "AND tbl1Facturas.Localidad_ID = " & Me.Combo23.Column(0) & " " _
    & "ORDER BY tbl1Facturas.Fecha;")

Set xl = CreateObject("Excel.Application")
Set wb = Workbooks.Add
wb.Sheets("Sheet1").Range("A1").CopyFromRecordset rs
xl.Visible = True

这篇关于将DAO QueryDef保存到临时查询并将结果输出到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！