Make-Table Query

[Jamie Dickerson]

I have 3 Make-Table Queries that I would like to run automatically after
closing a form. Would I use the DoCmd (RunSQL) for this? If so where would I
begin writting the code? I assume in the OnClose event of the Form. Am I on
the right path?

 

[KARL DEWEY]

Why not use the OnClose event of the Form to call a macro to run the 3 queries?

 

[Jamie Dickerson]

As an Access Novice I do not know a thing about Macros, but I have learned
quite a bit about coding. If a macro would be easier I am willing to give it
a try but I would require help.

 

[Dale Fye]

Personally, I prefer the Execute method, and I would probably use the forms
close event. It could be as simple as the following:

Private sub Form_Close()

On Error goto ProcError

currentdb.querydefs("query1").Execute dbfailonerror
currentdb.querydefs("query2").execute dbfailonerror
currentdb.querydefs("query3").execute dbfailonerror

Exit Sub

ProcError:
debug.print err.number & vbcrlf & err.description
resume next

End Sub

In reality, I normally call a user defined function when I want to execute
an action query. The function accepts the name of the query to run or the
SQL string, and has an error handler that will write error messages to my
error log, display it to the screen, or both. It also returns a boolean
value indicating whether the action was successful, which I can then use to
determine what to do as the next step in my code.

As Karl mentioned, you could also execute a macro, put I prefer the control
that my method provides.

 

[Jeff Boyce]

Although Access offers the Make-Table query, a lot of times it gets used
where other options are viable.

What will having three new tables every time you close the form allow
you/your users to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I have 3 Make-Table Queries that I would like to run automatically after
closing a form. Would I use the DoCmd (RunSQL) for this? If so where would I
begin writting the code? I assume in the OnClose event of the Form. Am I on
the right path?

I'm inclined to "unask" the question. Routine MakeTable queries are VERY rare
in most Access databases.

What are these queries? What will you do with the new tables when they're
created? What can you do with the new tables that cannot be done with a simple
select query?

The one case where make table queries are useful is if you have a report based
on a very complex query where you have a *DEMONSTRATED* (not presumed!)
performance issue or Query Too Complex error. Even then you would want to
create the temp table in a second "scratch" backend database created for that
purpose.

 

[KARL DEWEY]

Create the macro in design view.
Add 'OpenQuery' as action with the query name as argrument.
Repeat for other two queries.
Close macro naming it.
Open form in design view. Right click the form, scroll down and select
Properties.
Scroll down to OnClose and use the pull down to select the name of the macro
you created.
Save.