Save DAO MySQL QueryDef to a Temp Query and Output Results to Excel (1 Viewer)

Santiago

New member
Local time
Today, 01:04
Joined
Mar 18, 2019
Messages
6
Hello good people! :D


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:
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:


Code:
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:
Code:
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 :D
 

Santiago

New member
Local time
Today, 01:04
Joined
Mar 18, 2019
Messages
6
I get Run-time error '3251': Operation is not supported for this type of obeject.


And it shows me to this line:


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

Cronk

Registered User.
Local time
Today, 19:04
Joined
Jul 4, 2013
Messages
2,770
...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

Code:
debug.print strSQL
and after you have run your code, paste the SQL string into a new query.
 

June7

AWF VIP
Local time
Today, 00:04
Joined
Mar 9, 2014
Messages
5,424
See answer provided in referenced cross-post thread.
 

Santiago

New member
Local time
Today, 01:04
Joined
Mar 18, 2019
Messages
6
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

Code:
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!:D
 

Santiago

New member
Local time
Today, 01:04
Joined
Mar 18, 2019
Messages
6
June7 code worked like a charm thank you!



Code:
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 !:D
 

Users who are viewing this thread

Top Bottom