VBA code to run a make table query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report named rptBrian, which is run with a command button
cmdreportBrian. (Access 2003 user).

This report is based on a query named qryBriantodo, which is a subquery of
the query qryBrian1

QryBrian1 is based on two related tables, tblCases and tblnotes. tblnotes
is table that is created with a make-table query named qrynotes.

So, whenever I want to run the report rptBrian, I will first run the
qrynotes table, then answer "yes" to the Access prompt "The existing table
‘tblnotes' will be deleted before you run the query. Do you want to continue
anyway?"

Then, I will run the report rptBrian with the cmdreportBrian command button.


Can anyone advise the VBA command/code that would be required as an onclick
event procedure in the cmdreportBrian command button to both (1) automate
the running of the make-table query ‘qrynotes', and (2) eliminate the need to
answer Yes to the prompt, prior to it then calling up the report rptBrian?

Any help would be greatly appreciated.
 
This code snippet will run the "qrynotes" query without asking you to
confirm the deletion of the tblnotes table.

Dim dbs As DAO.Database
Set dbs = CurrentDb
dbs.Execute "qrynotes"
dbs.Close
Set dbs = Nothing
 
Ken: Here is the code snippet I added; unfortunately, the make table query
does not run, and I get a message "Table "tblnotes" already exists.

Private Sub cmdBrianToDoReport_Click()
On Error GoTo Err_cmdBrianToDoReport_Click

Dim stDocName As String

Dim dbs As DAO.Database

Set dbs = CurrentDb
dbs.Execute "qrynotes"
dbs.Close
Set dbs = Nothing

stDocName = "rpttBrian"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdBrianToDoReport_Click:
Exit Sub

Err_cmdBrianToDoReport_Click:
MsgBox Err.Description
Resume Exit_cmdBrianToDoReport_Click

End Sub

Any additional suggestions you might be able to offer?

Thanks
 
Sorry... yes, we need to delete the table first. I've modified your code to
do this. What the code steps do is turn off the warning box about deleting
the table, and then delete the table; if the table doesn't exist, the error
is ignored. The warnings then are turned back on and the code continues as
before.


Private Sub cmdBrianToDoReport_Click()
On Error GoTo Err_cmdBrianToDoReport_Click

Dim stDocName As String

Dim dbs As DAO.Database

Set dbs = CurrentDb
On Error Resume Next
DoCmd.SetWarnings False
dbs.TableDefs.Delete "tblNotes"
Err.Clear
DoCmd.SetWarnings True
On Error GoTo 0

dbs.Execute "qrynotes"
dbs.Close
Set dbs = Nothing

stDocName = "rpttBrian"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdBrianToDoReport_Click:
Exit Sub

Err_cmdBrianToDoReport_Click:
MsgBox Err.Description
Resume Exit_cmdBrianToDoReport_Click

End Sub

--

Ken Snell
<MS ACCESS MVP>
 
Back
Top