Save DAO MySQL QueryDef to a Temp Query and Output Results to Excel

[Santiago]

Hello good people!


So I'm in the middle of this Access project where I'm changing every form to connect directly to a MySQL server instead of using local tables. I have been able to change everything correctly up to now. In one of my forms a use a botton to pass everything that has been filtered by the user to a new Excel file. That worked great with local tables with this code:

Private Sub Command27_Click()
'Error Handler
On Error GoTo errHandler

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

'Error Handler
errHandler:
MsgBox "Error."
exitHandler:
End Sub

But now since the RecordSource is empty because I'm connecting directly to MySQL tables its not working so I tried this:

Private Sub Command28_Click()
    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

End Sub

No luck there! :banghead:


I use this on load form to connect to the database and pull the records needed for the form and everything loads fine and I'm even able to filter the form:

Private Sub fillSubForm()
'Set Form Recordset
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    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 " _
& "ORDER BY tbl1Facturas.Fecha;"



        Set rs = db.OpenRecordset(SQL, dbOpenDynaset, dbPessimistic)
        Set Me.Child13.Form.Recordset = rs

I literally have no more ideas on how to approach this. All help is appreciated

 

[June7]

No luck means what - error message, wrong result, nothing happens?

Your export code creates (or tries to) a query in the remote db then the OutputTo looks in the local db for the query and can't find it.

Cross post https://stackoverflow.com/questions...and-output-results-to-excel/55214244#55214244

 

[Santiago]

I get Run-time error '3251': Operation is not supported for this type of obeject.


And it shows me to this line:

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

 

[Cronk]

...every form to connect directly to a MySQL server

Does this mean that you have linked the tables in MySQL in your database.? Can you open the tables from within Access?


In Access SQL is a reserved word. Change the variable SQL to strSQL.


To check if your SQL is valid and returning the dataset you want, in your code, insert the line

debug.print strSQL

and after you have run your code, paste the SQL string into a new query.

 

[June7]

See answer provided in referenced cross-post thread.

 

[Santiago]

Does this mean that you have linked the tables in MySQL in your database.? Can you open the tables from within Access?


In Access SQL is a reserved word. Change the variable SQL to strSQL.


To check if your SQL is valid and returning the dataset you want, in your code, insert the line

debug.print strSQL

and after you have run your code, paste the SQL string into a new query.

No. It means there are no linked tables and I'm connecting directly to the server from my forms. So the Access file is a Front End and MySQL Server is my Back End database.


I'll check what the string is printing in a bit.



Thanks for the help!

 

[Santiago]

See answer provided in referenced cross-post thread.

Saw it working on it now! Will post results soon! Thank you!

 

[Santiago]

June7 code worked like a charm thank you!

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

Thank you !