Search code examples
sqlvbams-access

Showing a Query to Excel in VBA Access

I have this table:

Table

And I did the next query that works for sure:

SELECT tbl_Type.Id_Type, 
tbl_Type.Info, 
tbl_Type.Id_Table_Three_Plus_Info, 
tbl_Type.DateX

FROM tbl_Type

WHERE (((tbl_Type.DateX)=[Write Date (dd/dd/yyy)]));

As you see in the query in the WHERE part there's an input of the column DateX.

Now I want to use same procedure but using a form for the input, the code to do that is this:

Private Sub btn_Action_Click()
    On Error Resume Next
    
    'DoCmd.SetWarnings False
    
    Dim Fecha As String
    
    Fecha = _
    "SELECT tbl_Type.Id_Type, tbl_Type.Info, tbl_Type.Id_Table_Three_Plus_Info, tbl_Type.DateX FROM tbl_Type WHERE tbl_Type.DateX = txt_Date.value;"

    CurrentDb.CreateQueryDef ([Nom],Fecha) As QueryDef
    
    DoCmd.RunSQL Fecha
    'DoCmd.Save Fecha, "s"
    
    txt_Date = Null
    
End Sub

First, I want to know if it is well performed the input in the content of the String Fecha. As you see I'm giving that action when the button btn_Action is clicked and capturing the input for DateX with a Text Field txt_Date I would say that the query is working but I don't know how to see that, in that order I proceed to pretend to save that query, which is something desired too. I've seen in another question that for do that is used CurrentDb.CreateQueryDef I tried DoCmd.Save but I think that isn't the case. With CurrentDb.CreateQueryDef I'm having a syntax error. Am I missing something?

Please, if more details are needed, still is a silly question or things like that let me now, to do the correct procedure!

Solution

  • Consider simply saving a parameterized query and then in VBA bind your form value to parameter using QueryDef object. MS Access SQL maintains the PARAMETERS clause to set named placeholders. Below outputs parameterized query results to Excel workbook.

    SQL (save below as a query, Ribbon > Create > Query Design > SQL View)

    PARAMETERS DateParam Datetime;
SELECT t.Id_Type, 
       t.Info, 
       t.Id_Table_Three_Plus_Info, 
       t.DateX    
FROM tbl_Type t    
WHERE (((t.DateX)=[DateParam]));

    VBA (calls query, bind parameters, export to new Excel workbook)

    Private Sub btn_Action_Click()
On Error Goto ErrHandle
    Dim xl_app As Object, xl_wb As Objcect
    Dim qdef As QueryDef, rst As Recordset

    ' REFERENCE SAVED QUERY
    Set qdef = CurrentDb.QueryDef("mySavedQuery")

    ' BIND PARAMETER
    qdef!DateParam = txt_Date

    ' SET qdef TO RECORDSET
    Set rst = qdef.OpenRecordset()

    ' EXPORT TO EXCEL
    Set xl_app = CreateObject("Excel.Application")
    Set xl_wb = xl_app.Workbooks.Add()

    With xl_wb.Sheets(1)
       ' COLUMNS
       For i = 1 To rst.Fields.Count
          .Cells(1, i) = rst.Fields(i - 1).Name
       Next i 

       ' DATA
      .Range("A2").CopyFromRecordset rst
    End With

    xl_app.Visible = True

    txt_Date = Null

ExitHandle:
   rst.Close()
   Set rst = Nothing: Set qdef = Nothing
   Set xl_wb = Nothing: Set xl_app = Nothing
   Exit Sub

ErrHandle:
   Msgbox Err.Number & " - " & Err.Description, vbCritical, "RUNTIME ERROR"
   Resume ExitHandle
End Sub