vba

[Guest]

Hi,

Please excuses my ignorance -

I am currently running a macro in A2000, which comprises 10 make-table
queries. The problem is that everytime the macro gets to one of the queries
it asks me to confirm that the existing table should be deleted.

My question(s) is:

How do I stop it confirming that I should delete the table.

I have tried unchecking the Record changes/Document deletions/Action queries
which only seems to stop it asking if I want to run the query.

If anyone is good enough to help me, could you please be as basic on the
terminology front because I am very inexperienced with VBA.

 

[Ken Snell \(MVP\)]

Use the SetWarnings macro action to turn off the confirmation message
prompts.

 

[John W. Vinson]

Hi,

Please excuses my ignorance -

I am currently running a macro in A2000, which comprises 10 make-table
queries. The problem is that everytime the macro gets to one of the queries
it asks me to confirm that the existing table should be deleted.

My question(s) is:

How do I stop it confirming that I should delete the table.

I have tried unchecking the Record changes/Document deletions/Action queries
which only seems to stop it asking if I want to run the query.

If anyone is good enough to help me, could you please be as basic on the
terminology front because I am very inexperienced with VBA.

My first question is... WHY are you doing this?

In most databases, MakeTable queries are *very* rarely necessary. If you're
assuming that you must make a new table in order to create a Form, or a
Report, or to export data, that assumption is incorrect; you can (and usually
should) do all of these things from Select queries.

What you can do in your Macro is put a line

Setwarnings True

before running the queries, and be sure to do

Setwarnings False

afterward (or you'll lose all warning messages for all errors).

But I *really* think you should reconsider what you're doing. There are almost
certainly better ways!

John W. Vinson [MVP]

 

[Guest]

I'm with John on this. Make table queries should never be used (well not
never, bot for practicle purposes in an application, never)
The better technique would be to create the 10 tables. Set the field types
and lengths correctly (one of the things make table queries don't do).
Then when you need to do whatever it is you are doing now, run delete
queries on all the tables, then use append queries to reload them.
Also, I suggest the use of the CurrentDb.Execute method to run Action
queries. First, you don't have to worry about turning warnings on and off,
the execute method does not trigger them. Second, it is the fastest way to
execute an Action query. The Excute method does not go through the Access
User Interface, it goest directly to Jet. That is way it is faster and
doesn't trigger the warnings. Here is an example:

With CurrentDb
.Execute("Delete * From FirstTAble;"), dbFailOnError
.Execute("qappFirstTable"), dbFailOnError
...
...
.Execute("Delete * From LastTAble;"), dbFailOnError
.Execute("qappLastTable"), dbFailOnError
End With

 

[David W. Fenton]

Use the SetWarnings macro action to turn off the confirmation
message prompts.

Well, I think that's a really bad idea.

I recently had a very old application of mine break when converted
from A97 to A2K3 because A2K3 resolves form control references
differently than A97. The control references were in the saved
QueryDefs and they pulled the right values in A97 but came out as a
zero-length string in A2K3.

Now, the problem was that I was running a bunch of these queries in
a row with SetWarnings turned off (yes, I was not too clever in my
Access programming 10 years ago). The problematic query would have
shown an error if SetWarnings had been ON (because the destination
fields in the append cannot contain ZLS's), but it wasn't. The
result was that my client lost several days of invoice payment
record detail (the payments were recorded, but the payment dates and
check numbers were lost, because of the control references that
weren't resolving the same).

So, I would basically say:

Never use DoCmd.RunSQL or DoCmd.OpenQuery or a macro to execute a
query, unless you can afford to have the confirmation messages come
up.

To fix the problem, I converted the problem queries to be
CurrentDB.Execute, and resolved the control references to actual
values before constructing the SQL string.

I know that for a macro coder that's probably beyond their
abilities, but, well, I don't know a better solution.

Here's a quick-and-dirty ExecuteSQL function that could be called
with RunCode from a macro:

Public Function ExecuteSQL(strSQL As String, _
Optional db As DAO.Database) As Long
On Error GoTo errHandler
Dim bolSetDB as Boolean

If (db Is Nothing) Then
Set db = CurrentDB()
bolSetDB = True
End If

db.Execute strSQL, dbFailOnError
ExecuteSQL = db.RecordsAffected

exitRoutine:
If bolSetDB Then
Set db = Nothing
End If
Exit Function

errHandler:
'MsgBox Err.Number & ": " & Err.Description, _
vbCritical, "Error in ExecuteSQL()"
Resume exitRoutine
End Function

This function returns the numbers of records that are
updated/deleted/appended.

One could alter it to return some kind of negative value if, for
instance, an error occured. This would be done in the errHandler
before the Resume command:

ExecuteSQL = -9999

You could choose any negative number other than -1 (though you could
use that if you liked, I wouldn't, since it's the value of True).

You would call this with Runcode and supply one argument, the SQL
string (which could be the name of a saved query).

 

[Guest]

Thanks David.

That's a lot of info to absorb, I will get started.

Thanks for taking the time to answer.

 

[Guest]

My first question is... WHY are you doing this?

In most databases, MakeTable queries are *very* rarely necessary. If you're
assuming that you must make a new table in order to create a Form, or a
Report, or to export data, that assumption is incorrect; you can (and usually
should) do all of these things from Select queries.

What you can do in your Macro is put a line

Setwarnings True

before running the queries, and be sure to do

Setwarnings False

afterward (or you'll lose all warning messages for all errors).

But I *really* think you should reconsider what you're doing. There are almost
certainly better ways!

John W. Vinson [MVP]

John,

I'm sure there are more efficient ways of performing this. However, main
table in the database contains 2,104,260. When I use select queries, which
normally have pivot-tables running off them, it seems to take a lot more time
to update the tables. Sometimes I've even ran out of memory.

Thanks for your help though. I'll start rethinking.

Sam Carson

 

[Guest]

That's a good idea, I didn't realise you could automate the deletion of
contents of a table. Thanks for the sample code, that should get me started.

Sam Carson

 

[John W. Vinson]

I'm sure there are more efficient ways of performing this. However, main
table in the database contains 2,104,260. When I use select queries, which
normally have pivot-tables running off them, it seems to take a lot more time
to update the tables. Sometimes I've even ran out of memory.

Well, this may be one of those special cases where make-table queries are
appropraiate, then! Apologies if I came over as "preachy".

If you're creating tables for "scratch" use - create some tables, run the
pivots, print some reports, and then you don't need the tables any more - you
should certainly create the tables in a separate "scratch" database (.mdb
file) that you create in code for the purpose and then delete. Tony Toews has
a page describing this technique:

http://www.granite.ab.ca/access/temptables.htm

John W. Vinson [MVP]