By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,972 Members | 1,115 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,972 IT Pros & Developers. It's quick & easy.

SetWarnings False is not doing the job. Other VBA command?

P: n/a
I am now using the Execute command to run SQL for action queries. When I
had them as saved queries, I would use DoCmd.SetWarnings False to allow the
queries to overwrite existing tables. when I use this code:

CurrentDb().Execute strMySql', dbFailOnError

I still get warnings that the table already exists.

Is there another command that I am supposed to use?
Nov 12 '05 #1
Share this Question
Share on Google+
21 Replies


P: n/a
Try DoCmd.RunSQL strMySql instead.
"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in message
news:IOHOb.84405$nt4.128688@attbi_s51...
I am now using the Execute command to run SQL for action queries. When I
had them as saved queries, I would use DoCmd.SetWarnings False to allow the queries to overwrite existing tables. when I use this code:

CurrentDb().Execute strMySql', dbFailOnError

I still get warnings that the table already exists.

Is there another command that I am supposed to use?

Nov 12 '05 #2

P: n/a
You're not getting a warning that you're about to do something, you're
getting an error message because the query can't do what you're asking it to
do. I assume the query is a make table query and the table already exists.
The SetWarnings statement isn't needed for the syntax you are currently
using. The easiest thing to do may just be to trap the error and ignore it.
This may be considered a "data error", if so, you'll have to trap it in the
form's Error event. If it's not, then you can trap it in the present
routine.

--
Wayne Morgan
Microsoft Access MVP
"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in message
news:IOHOb.84405$nt4.128688@attbi_s51...
I am now using the Execute command to run SQL for action queries. When I
had them as saved queries, I would use DoCmd.SetWarnings False to allow the queries to overwrite existing tables. when I use this code:

CurrentDb().Execute strMySql', dbFailOnError

I still get warnings that the table already exists.

Is there another command that I am supposed to use?

Nov 12 '05 #3

P: n/a
<<Is there another command that I am supposed to use?>>

You're on the right track with the Execute Method...don't use RunSQL...trust
me.

When running Make-Table queries with the Execute Method, you should use the
following technique:

Sub MakeMyTable
Dim dbs as DAO.Database

Set dbs=Currentdb()
With dbs
On Error Resume Next
.TablesDefs.Delete "MyTable"
On Error Goto 0 'or Goto Handler
.Execute "qryCreatingMyTable",dbFailonError
End With
End Sub

Nov 12 '05 #4

P: n/a
"DCM Fan" <dc****@aol.comSPNOAM> wrote in message
news:20***************************@mb-m05.aol.com...
<<Is there another command that I am supposed to use?>>

You're on the right track with the Execute Method...don't use RunSQL...trust me.

When running Make-Table queries with the Execute Method, you should use the following technique:
Sub MakeMyTable

Dim dbs as DAO.Database

Set dbs=Currentdb()
With dbs
On Error Resume Next
.TablesDefs.Delete "MyTable"
On Error Goto 0 'or Goto Handler
.Execute "qryCreatingMyTable",dbFailonError
End With
End Sub


Thank You. This works great - although TableDefs has only one "s" :)
Nov 12 '05 #5

P: n/a
"Robert" <ro**********@nospam-unforgettable.com> wrote:
Try DoCmd.RunSQL strMySql instead.


I respectfully disagree. docmd.runsql has several disadvantages as others have
pointed out.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #6

P: n/a
"Wayne Morgan" <co***************************@hotmail.com> wrote:
You're not getting a warning that you're about to do something, you're
getting an error message because the query can't do what you're asking it to
do. I assume the query is a make table query and the table already exists.
The SetWarnings statement isn't needed for the syntax you are currently
using. The easiest thing to do may just be to trap the error and ignore it.


Not quite. If you ignore the message the query won't run.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #7

P: n/a
On Mon, 19 Jan 2004 03:04:08 GMT, "Colleyville Alan"
<ae***********@nospam.comcast.net> wrote:
I am now using the Execute command to run SQL for action queries. When I
had them as saved queries, I would use DoCmd.SetWarnings False to allow the
queries to overwrite existing tables. when I use this code:

CurrentDb().Execute strMySql', dbFailOnError

I still get warnings that the table already exists.

Is there another command that I am supposed to use?


Set Warnings works with RunSQL (and Action Queries) and notifies the
user that the action will modify the table data. It is NOT the same as
an Error message, which is what you are getting.
CurrentDB.Execute does not give any warnings, and the dbFailOnError is
probably what is creating the error message.
There is a problem with your SQL.

I notice you are using the () in your code. Not necessary.
And what is the single quote doing by itself after the strMySQL?
If strMySQL is a string variable holding the SQL, no quotes around it
are needed.
Are you trying to run a Make Table query?
rom the message, it seems you want to make a table
using a name that already exists.
Perhaps you should post the strMySQL code with a very short indication
of what you wish to obtain.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.
Nov 12 '05 #8

P: n/a
DFS

"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in message
news:IOHOb.84405$nt4.128688@attbi_s51...
I am now using the Execute command to run SQL for action queries. When I
had them as saved queries, I would use DoCmd.SetWarnings False to allow the queries to overwrite existing tables. when I use this code:

CurrentDb().Execute strMySql', dbFailOnError

I still get warnings that the table already exists.

Is there another command that I am supposed to use?

Alan,

Before exec-ing the query, you should check for and delete the table that
you're probably trying to SELECT INTO.
Nov 12 '05 #9

P: n/a
<fr***@example.invalid> wrote in message
news:ol********************************@4ax.com...

Set Warnings works with RunSQL (and Action Queries) and notifies the
user that the action will modify the table data. It is NOT the same as
an Error message, which is what you are getting.
CurrentDB.Execute does not give any warnings, and the dbFailOnError is
probably what is creating the error message.
There is a problem with your SQL.

I notice you are using the () in your code. Not necessary.
Ok - I am taking it from posters to CDMA who, I assume, are more
knowledgable than me.
And what is the single quote doing by itself after the strMySQL?
If strMySQL is a string variable holding the SQL, no quotes around it
are needed.
Apparently a stray keystroke - the single quote is not actually in my code.
Are you trying to run a Make Table query? Yes
rom the message, it seems you want to make a table
using a name that already exists.
Yep
Perhaps you should post the strMySQL code with a very short indication
of what you wish to obtain.


Well, I am now deleteing the old table first so it is working. Though it
does take a long time to delete the tables! I was expecting a second or two
and it is taking 12-15 secs (maybe I am impatient and it is actually less,
but it is much longer than I thought). Perhaps it is hitting an error and
with On Error Resume it is taking awhile to get through it all. But it *is*
working! YEAH!!!
Nov 12 '05 #10

P: n/a

"DFS" <no****@nospam.com> wrote in message
news:10*************@corp.supernews.com...

"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in message
news:IOHOb.84405$nt4.128688@attbi_s51...
I am now using the Execute command to run SQL for action queries. When I had them as saved queries, I would use DoCmd.SetWarnings False to allow

the
queries to overwrite existing tables. when I use this code:

CurrentDb().Execute strMySql', dbFailOnError

I still get warnings that the table already exists.

Is there another command that I am supposed to use?

Alan,

Before exec-ing the query, you should check for and delete the table that
you're probably trying to SELECT INTO.


Thanks, I am now doing that (thanks to advice from CDMA), but it is taking
several seconds to delete 6 tables. I could delete them by hand about as
quickly!
Should this code take 8 - 12 seconds to run?!?!?!?

On Error Resume Next
.TableDefs.Delete "CatList_Master_Benchmarks"
.TableDefs.Delete "CatList_Sel_List_Bnch"
.TableDefs.Delete "Perf_Master_Benchmarks"
.TableDefs.Delete "Perf_Master_Funds"
.TableDefs.Delete "Perf_Sel_List_Bnch"
.TableDefs.Delete "Perf_Sel_List_Funds"
On Error GoTo 0 'or Goto Handler
Nov 12 '05 #11

P: n/a
Thanks Tony,

I was assuming that they didn't want to know about the table already
existing, just create it if it doesn't, but I may be wrong.

--
Wayne Morgan
MS Access MVP
"Tony Toews" <tt****@telusplanet.net> wrote in message
Not quite. If you ignore the message the query won't run.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

Nov 12 '05 #12

P: n/a
ae***********@nospam.comcast.net (Colleyville Alan) wrote in
<IOHOb.84405$nt4.128688@attbi_s51>:
I am now using the Execute command to run SQL for action queries.
When I had them as saved queries, I would use DoCmd.SetWarnings
False to allow the queries to overwrite existing tables. when I
use this code:

CurrentDb().Execute strMySql', dbFailOnError

I still get warnings that the table already exists.

Is there another command that I am supposed to use?


You need to handle the errors that can occur if there is an error.

Another way to fix this is to remove dbFailOnError, but that means
that you never know for certain if the SQL you wanted executed
really succeeded or not. For instance, if some of the records were
not updated, you'll never know.

If your SQL is a MakeTable, then you need to delete the table
before running your SQL. However, then you need to be sure the
table exists before you try to delete it. A discussion of various
ways of coding a TableExists() function and issues of error
handling and the values of True/False are discussed in this thread:

http://groups.google.com/groups?selm...%40news.tpi.pl

I recommend the implementation in this Microsoft Knowlege Base
article:

http://support.microsoft.com/default...b;en-us;210598

It works for all Access objects and works on the error handler
principle. Actually, I've implemented it differently than that
article does it -- my code is at the end of my post, after my
signature.

A non-error-based approach (which might be faster) is to use SQL to
look up the table name in MSysObjects, a Jet system table. Since
it's SQL, it's probably very fast, though perhaps the time it takes
to open the recordset might offset the time it takes to raise an
error in the function below.

Basically, though, I'd strongly recommend *against* the use of On
Error Resume Next in any context, as I once had an application
where I used it and it didn't go out of scope properly, resulting
in lost errors. As I said in the Google thread cited above, I never
use i except one line at a time, like this:

On Error Resume Next
[whatever you're doing that might raise an error]
On Error Resume 0

I'm also philosophically opposed to using it, in general, because
it assumes that you know exacly which errors your line of code can
raise and you want to ignore absolutely all such errors. I'm not
that omniscient so I prefer to write code that avoids raising any
foreseen errors and explicitly handles any unforeseen ones.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Function DoesObjectExist(strObjectType As String, _
strObjectName As String, Optional db As Database) As Boolean
' Microsoft
' based on Microsoft KB article 210598
On Error GoTo errHandler
Dim strFindObject As String
Dim cnt As Container
Dim strMsg As String

If db Is Nothing Then Set db = CurrentDb()
If strObjectType = "Macros" Then strObjectType = "Scripts"

Select Case strObjectType
Case "Tables"
strFindObject = db.TableDefs(strObjectName).Name
Case "Queries"
strFindObject = db.QueryDefs(strObjectName).Name
Case "Forms", "Modules", "Reports", "Scripts"
Set cnt = db.Containers(strObjectType)
strFindObject = cnt.Documents(strObjectName).Name
Set cnt = Nothing
Case Else
strMsg = "Object Name """ & strObjectType & _
""" is an invalid "
strMsg = strMsg & " argument to function DoesObjectExist!"
MsgBox strMsg, vbCritical, "DoesObjectExist"
End Select
DoesObjectExist = Len(strFindObject) > 0

exitRoutine:
Exit Function

errHandler:
Select Case Err.Number
Case 3265
' object does not exist
Case Else
MsgBox Err.Number & ": " & Err.Description, vbCritical, _
"Error in function DoesObjectExist()"
End Select
Resume exitRoutine
End Function
Nov 12 '05 #13

P: n/a
co***************************@hotmail.com (Wayne Morgan) wrote in
<53*****************@newssvr22.news.prodigy.com> :
I was assuming that they didn't want to know about the table
already existing, just create it if it doesn't, but I may be
wrong.


To do that, you have to remove the dbFailOnError argument.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #14

P: n/a
fr***@example.invalid wrote in
<ol********************************@4ax.com>:
On Mon, 19 Jan 2004 03:04:08 GMT, "Colleyville Alan"
<ae***********@nospam.comcast.net> wrote:
I am now using the Execute command to run SQL for action queries.
When I had them as saved queries, I would use DoCmd.SetWarnings
False to allow the queries to overwrite existing tables. when I
use this code:

CurrentDb().Execute strMySql', dbFailOnError

I still get warnings that the table already exists.

Is there another command that I am supposed to use?

[]
I notice you are using the () in your code. Not necessary.


Not necessary, but it's a good reminder that CurrentDB() is a
function.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #15

P: n/a
ae***********@nospam.comcast.net (Colleyville Alan) wrote in
<paKOb.82750$sv6.193887@attbi_s52>:
<fr***@example.invalid> wrote in message
news:ol********************************@4ax.com.. .

Set Warnings works with RunSQL (and Action Queries) and notifies
the user that the action will modify the table data. It is NOT
the same as an Error message, which is what you are getting.
CurrentDB.Execute does not give any warnings, and the
dbFailOnError is probably what is creating the error message.
There is a problem with your SQL.

I notice you are using the () in your code. Not necessary.


Ok - I am taking it from posters to CDMA who, I assume, are more
knowledgable than me.


There's a good reason to use it. CurrentDB() is a function, and
functions return data. If you don't use the (), you might tend to
think that CurrentDB is an *object* and that you'd get the exact
same object each time you use it (you don't -- you get a reference
to the same object, but the reference pointer itself is different
each time you call it).

It's a small thing, but I find this kind of attention to the
details of coding is important for me to always keep in mind
exactly what I'm doing.

[]
Perhaps you should post the strMySQL code with a very short
indication of what you wish to obtain.


Well, I am now deleteing the old table first so it is working.
Though it does take a long time to delete the tables! I was
expecting a second or two and it is taking 12-15 secs (maybe I am
impatient and it is actually less, but it is much longer than I
thought). Perhaps it is hitting an error and with On Error Resume
it is taking awhile to get through it all. But it *is* working!
YEAH!!!


I wouldn't use On Error Resume Next in this context, as it may hide
a different kind of error. I explain this in another post.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #16

P: n/a
DFS

"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in message
news:rdKOb.85228$nt4.129862@attbi_s51...

"DFS" <no****@nospam.com> wrote in message
news:10*************@corp.supernews.com...

"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in message
news:IOHOb.84405$nt4.128688@attbi_s51...
I am now using the Execute command to run SQL for action queries.
When
I had them as saved queries, I would use DoCmd.SetWarnings False to
allow the
queries to overwrite existing tables. when I use this code:

CurrentDb().Execute strMySql', dbFailOnError

I still get warnings that the table already exists.

Is there another command that I am supposed to use?

Alan,

Before exec-ing the query, you should check for and delete the table that you're probably trying to SELECT INTO.


Thanks, I am now doing that (thanks to advice from CDMA), but it is taking
several seconds to delete 6 tables. I could delete them by hand about as
quickly!
Should this code take 8 - 12 seconds to run?!?!?!?

On Error Resume Next
.TableDefs.Delete "CatList_Master_Benchmarks"
.TableDefs.Delete "CatList_Sel_List_Bnch"
.TableDefs.Delete "Perf_Master_Benchmarks"
.TableDefs.Delete "Perf_Master_Funds"
.TableDefs.Delete "Perf_Sel_List_Bnch"
.TableDefs.Delete "Perf_Sel_List_Funds"
On Error GoTo 0 'or Goto Handler


No, it shouldn't require that long. Maybe your Resume Next is causing the
problem - you should be trapping and examining the errors rather than
skipping over them.

Nov 12 '05 #17

P: n/a
What's the lifetime of these tables? If it is just one session with your
application, or even if it is longer, you might create them in a second
database and just Kill that database file and create another. That's a trick
sometimes used to reduce bloat from temporary tables, but it might work for
your purposes, too.

Larry Linson
Microsoft Access MVP
"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in message
news:rdKOb.85228$nt4.129862@attbi_s51...

"DFS" <no****@nospam.com> wrote in message
news:10*************@corp.supernews.com...

"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in message
news:IOHOb.84405$nt4.128688@attbi_s51...
I am now using the Execute command to run SQL for action queries.
When
I had them as saved queries, I would use DoCmd.SetWarnings False to
allow the
queries to overwrite existing tables. when I use this code:

CurrentDb().Execute strMySql', dbFailOnError

I still get warnings that the table already exists.

Is there another command that I am supposed to use?

Alan,

Before exec-ing the query, you should check for and delete the table that you're probably trying to SELECT INTO.


Thanks, I am now doing that (thanks to advice from CDMA), but it is taking
several seconds to delete 6 tables. I could delete them by hand about as
quickly!
Should this code take 8 - 12 seconds to run?!?!?!?

On Error Resume Next
.TableDefs.Delete "CatList_Master_Benchmarks"
.TableDefs.Delete "CatList_Sel_List_Bnch"
.TableDefs.Delete "Perf_Master_Benchmarks"
.TableDefs.Delete "Perf_Master_Funds"
.TableDefs.Delete "Perf_Sel_List_Bnch"
.TableDefs.Delete "Perf_Sel_List_Funds"
On Error GoTo 0 'or Goto Handler

Nov 12 '05 #18

P: n/a
bo*****@localhost.not (Larry Linson) wrote in
<jj***************@nwrddc02.gnilink.net>:
What's the lifetime of these tables? If it is just one session
with your application, or even if it is longer, you might create
them in a second database and just Kill that database file and
create another. That's a trick sometimes used to reduce bloat from
temporary tables, but it might work for your purposes, too.


Something else that I intended to say in my long post on figuring
out if a table exists was that I generally will never use a
MakeTable query in a production application. Why have the overhead
of creating the whole table when instead you could just append
records and not have to worry about it? Deleting all records in an
existing table is extremely fast.

But, like you said, either way, MakeTable or Append, the table
involved should definitely *not* be in the front end.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #19

P: n/a
"Wayne Morgan" <co***************************@hotmail.com> wrote:
I was assuming that they didn't want to know about the table already
existing, just create it if it doesn't, but I may be wrong.


Well,to me the action query would create the table and then add the relevant records.
If the table already existed then it would fail and not add the records. Furthermore
older records would exist which would lead to much confusion by users. Assuming they
even noticed anything.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #20

P: n/a
"Larry Linson" <bo*****@localhost.not> wrote in message
news:jj***************@nwrddc02.gnilink.net...
What's the lifetime of these tables? If it is just one session with your
application, or even if it is longer, you might create them in a second
database and just Kill that database file and create another. That's a trick sometimes used to reduce bloat from temporary tables, but it might work for your purposes, too.

Larry Linson
Microsoft Access MVP


The tables that were taking forever to kill one evening were taking no time
the next day. Maybe a memory corruption or something.

As far as these tables go, originally I did not plan on building any tables.
I simply wanted to query data into a recorset, load it into an array, and
write the info from the array into a spreadsheet. I don't know SQL much
(sometimes I can read the code, but not write it) so I did QBE and copied
the SQL code into VBA. But when I did the QBE, I used MakeTable queries to
make the intermediate tables so the next query could use the table I just
created. Some of these queries link 5 tables together and while others
might be able to combine several queries into one or two, I am trying to
keep them relatively simple.

If I had the time and patience, I'd probably find a way to avoid the
tables altogether, but I have a lot of stuff to get done in a short time and
so I'm kinda making this up as I go along. If I can demo what I want the
system to do, I think we can get a real programmer to do it and not a
part-time hack like me. So for now, the MakeTable queries stay.
Nov 12 '05 #21

P: n/a
dX********@bway.net.invalid (David W. Fenton) wrote:
I'm also philosophically opposed to using it, in general, because
it assumes that you know exacly which errors your line of code can
raise and you want to ignore absolutely all such errors. I'm not
that omniscient so I prefer to write code that avoids raising any
foreseen errors and explicitly handles any unforeseen ones.


Absolutely agreed. When working with the code I deliberately cause whatever error
I'm trying to handle and put Select Case err.number in my error handling routine
along with my actions and resume next.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #22

This discussion thread is closed

Replies have been disabled for this discussion.