CODEWITHSUNDEEP
ms-accessvbams-access-2010
lunar8nrg
lunar8nrg

Reputation: 111

Run query in the background

I have a database which I'm working on. It is composed of split database, both front and multiple links to backend tables.

I am working on a report which is composed of 15 different sub-reports. I have a form which allows me to input start date and end date for the report. There's a button which generates the final report. The problem is when I want to generate the report, I would have to re-run each of the different make-table queries for each of the sub-reports. The issue with this is that there would be 2 warnings for each query, one to delete my table and another for rows added to the table.

I researched online and found this code to run the Execute command which will remove all the warnings. I'm new to VB but I figured I'll give it a try and I get the following run-time error "3078: The MS Access database engine cannot find the input table or query". I checked the query name and it matches so I'm not sure why I'm getting this error. I've only tried one of the 15 queries so I can make sure it works. Once I get this to work, my other question is would combining all these into 15 execute commands work in the module?

Private Sub PS_Report_Date_AfterUpdate()

Dim dbs As DAO.Database
Dim lngRowsAffected As Long
Dim lngRowsDeleted As Long
Dim sql$

sql = "[qry_Maui_Division_KWH_Produced]"

Set dbs = CurrentDb

' Execute runs both saved queries and SQL strings
dbs.Execute sql, dbFailOnError

' Get the number of rows affected by the Action query.
' You can display this to the user, store it in a table, or trigger an action
' if an unexpected number (e.g. 0 rows when you expect > 0).
lngRowsAffected = dbs.RecordsAffected

dbs.Execute "DELETE FROM tbl_Maui_Division_KWH_Produced WHERE Bad", dbFailOnError
lngRowsDeleted = dbs.RecordsAffected    
End Sub

SQL Code: 

SELECT 
tbl_MPP_DailyGenerationReport.DateLog, 
[MPP_Daily_Gross_Gen_kWh]+[Total_Gross_kWh] AS Maui_Gross_kWh_Produced,
[Total_Aux]+[Total_Aux_kWh] AS Maui_Gross_Aux_kWh_Produced, [MPP_Daily_Gross_Gen_kWh]-[Total_Aux]+[Total_Net_kWh] AS Maui_Net_kWh_Produced, 
Round(([Total_MBTU_Burned]*1000000)/([MPP_Daily_Gross_Gen_kWh]+[Total_Gross_kWh]),0) AS Maui_Gross_BTU_kWh, 
Round([Total_MBTU_Burned]*1000000/([MPP_Daily_Gross_Gen_kWh]-[Total_Aux]+[Total_Net_kWh]),0) AS Maui_Net_BTU_kWh, 
Round(([MPP_Daily_Gross_Gen_kWh]+[Total_Gross_kWh])/[Total_Barrels_Burned],0) AS Maui_Gross_kWh_BBL, 
Round(([MPP_Daily_Gross_Gen_kWh]-[Total_Aux]+[Total_Net_kWh])/[Total_Barrels_Burned],0) AS Maui_Net_kWh_BBL 

INTO tbl_Maui_Division_KWH_Produced

FROM ((tbl_MPP_DailyGenerationReport 
INNER JOIN tbl_KPP_DailyGenerationReport 
ON tbl_MPP_DailyGenerationReport.DateLog = tbl_KPP_DailyGenerationReport.DateLog) 
INNER JOIN tbl_MPP_Aux_DailyGenerationReport 
ON tbl_MPP_DailyGenerationReport.DateLog = tbl_MPP_Aux_DailyGenerationReport.DateLog) 
INNER JOIN qry_Maui_Total_Fuel_Burned 
ON tbl_MPP_DailyGenerationReport.DateLog = qry_Maui_Total_Fuel_Burned.DateLog
WHERE (((tbl_MPP_DailyGenerationReport.DateLog)=[Forms]![Power Supply Reports]![PS_Report_Date]));​

Upvotes: 0

Views: 4746

Answers (1)

neuralgroove
neuralgroove

Reputation: 580

This will run your queries without warnings:

Private Sub PS_Report_Date_AfterUpdate()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qry_Maui_Division_KWH_Produced"
    DoCmd.RunSQL "DELETE FROM tbl_Maui_Division_KWH_Produced WHERE Bad"
    DoCmd.SetWarnings True
End Sub

Upvotes: 1

Related Questions