CODEWITHSUNDEEP
mysqlvbams-accessdao
SupBe
SupBe

Reputation: 103

Save DAO QueryDef to Temporary Query and Output Results to 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.

Here is the code for that:

    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

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

All help welcome ! :P

Upvotes: 1

Views: 892

Answers (1)

June7
June7

Reputation: 21370

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.

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

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.

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

Upvotes: 1

Related Questions