Using Access to Export in Excel, Parameterized Query

1

I'm using the Access database, I need to export a query to excel.

My problem is as follows:

I am using a parameterized query in Access for the access itself to export to excel.

When I make the call to export, it opens the screen to enter the parameter. I wanted to pass this parameter via code)

 private void btnExportarParaExcel_Click(object sender, EventArgs e)
    {
        saveFileDialog1.Title = "Salvar Arquivo";
        saveFileDialog1.Filter = "Excel|.xlsx";
        saveFileDialog1.FilterIndex = 0;
        saveFileDialog1.FileName = "Match_" + DateTime.Now.ToString("ddMMyyyy");
        saveFileDialog1.DefaultExt = ".xlsx";
        saveFileDialog1.InitialDirectory = @frmPrincipal.caminhoDoSistema;
        saveFileDialog1.RestoreDirectory = true;

        DialogResult resultado = saveFileDialog1.ShowDialog();

        if (resultado == DialogResult.OK)
        {
            String mesAno = frmPrincipal.mesAno;
            mesAno = mesAno.Substring(3, 4) + mesAno.Substring(0, 2);
            var accessApp = new Access.Application();
            try
            {
                accessApp.OpenCurrentDatabase(@frmPrincipal.caminhoDoSistema + @"\Database\" + mesAno + @"\prjRecon.accdb", false);
                accessApp.DoCmd.SetParameter("companhia", frmPrincipal.companhia);
                accessApp.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acExport, Access.AcSpreadSheetType.acSpreadsheetTypeExcel12Xml, "Match_LancamentosContabeis2", @saveFileDialog1.FileName);
                MessageBox.Show("Arquivo salvo com sucesso!", "Sucesso", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (COMException ex)
            {

                MessageBox.Show("Erro:" + "\n" + ex.ToString());
            }
            accessApp.CloseCurrentDatabase();
            accessApp.Quit();
        }
        else
        {
            MessageBox.Show("Operação cancelada");
        }
    }
    
asked by anonymous 08.12.2016 / 19:52

1 answer

0

The most practical way would be to use temporary variables.

Try the following:

  • Based on your query use something like the image you submitted. Change the% criterion% to [What Date? (dd/mm/yy)]
  • In your code include the following line [TempVars]![MeuCriterio]
  • In this way, you can pass the criterion via code to the query and you will not have to fill in the window during the runtime.

        
    22.12.2016 / 22:39