Ridding myself of "You are about to add n records to a New Table"

N

Neily

Hi,

I've got a bit of VB code that runs a few DoCmd's. It currently runs an SQL
that takes a section of data from one table and writes it to a new table.
When this section runs, it the default message warning you what you are about
to do comes up.

Is there any way, programattically or otherwise to stop this message coming
up so that the code runs straight through with no messageboxes?

Ta..
 
X

xRoachx

Before your SQL execution, enter the line:

DoCmd.SetWarnings False

Before you exit the function:

DoCmd.SetWarnings True

Always make sure to turn the warnings back on.
 
A

Allen Browne

Another approach is:
dbEngine(0)(0).Execute strSql, dbFailOnError

That has the advantage of notifying you if something goes wrong, but not if
the action completes successfully.
 
N

Neily

Cheers troops,


Allen Browne said:
Another approach is:
dbEngine(0)(0).Execute strSql, dbFailOnError

That has the advantage of notifying you if something goes wrong, but not if
the action completes successfully.
Click to expand...
 
D

David C. Holley

Use the .Execute method of the Database object. When you wrap the
statement in a transaction (quite easy), you have the ability to present
the user with the same warning message and the option to bail out.
Otherwise, it doesn't generate an error message - just errors (if any)
which leaves it up to you as to what you do with it.
 
D

David C. Holley

ARRGGGHHH!!!! I've heard that it CurrentDB() is now preferred over using
the Workspace method of the DBEngine.

Set db = CurrentDB()
db.Execute
db.RecordsEffected
 
A

Allen Browne

David, do you know why it's preferable to use CurrentDb(), and when?

dbEngine(0)(0) stands for:
dbEngine.Workspaces(0).Databases(0)
which refers to the default database that is open in the default workspace.
Since it is already open, it has the advantage of speed (it's already there
to use), and the disadvantage of currency (it may not be completely up to
date, or could have been messed with.)

Whenever you make a call to CurrentDb() creates a *new* object, flushes all
the collections of the current database, and then points the new object to
the database. It has the advantage of being current, but the disadvantage of
being slower. Further, multiple calls to CurrentDb() are not referring to
the same object.

The main speed difference is the time it takes to flush all the collections
of the database. According to michka's tests, this makes CurrentDb() about
5000 times slower than dbEngine(0)(0). In practice, that's largely academic:
if you were using CurrentDb(), you would declare a database variable before
the loop rather than call CurrentDb() repeatedly in the loop.

A more significant difference is newbies use multiple references to
CurrentDb, not understanding that they are different objects. For example,
this doesn't work:
CurrentDb().Execute strSql
Debug.Print CurrentDb().RecordsetAffected

More significant, if you have been modifying the database structure, there's
a good chance that dbEngine(0)(0) will not be up to date with the new
changes, so CurrentDb() is essential for those cases.

There are also cases where dbEngine(0)(0) is not what you expect. When
wizards run, you can end up with the wizard database as dbEngine(0)(0), and
the current database is therefore dbEngine(0)(1). That can also happen while
multiple database objects are open in transactions, i.e. they can rollback
into a different order.

So, in general, CurrentDb() is safer than dbEngine(0)(0). But in a
production database where the data structure is not being modified and
wizards are not running, dbEngine(0)(0) will be fine.

HTH.
 
D

David C. Holley

Not specifically. It was something that I read in an article. I did
learn the hard way about using the CurrentDB() function and the fact
that it creates a new object everytime it was used. It only took me
forever to figure out why the .RecordsEffected property of CurrentDB
kept returning 0 after I invoked the .Execute method. It was only after
I used instantiated an object variable using CurrentDB() that the code
worked as in ...

set db = CurrentDb()
db.Execute [sqlStatement]
Debug.print db.recordsEffected
set db = Nothing
 
D

Dirk Goldgar

David C. Holley said:
Not specifically. It was something that I read in an article. I did
learn the hard way about using the CurrentDB() function and the fact
that it creates a new object everytime it was used. It only took me
forever to figure out why the .RecordsEffected property of CurrentDB
kept returning 0 after I invoked the .Execute method. It was only
after I used instantiated an object variable using CurrentDB() that
the code worked as in ...

set db = CurrentDb()
db.Execute [sqlStatement]
Debug.print db.recordsEffected
set db = Nothing
Click to expand...

You can also do it without defining an explicit object variable, by
using the With construct:

With CurrentDb
.Execute strSQL
Debug.Print .RecordsAffected
End With
 
D

David C. Holley

handn't thought about. I typically only use WITH's if there are numerous
statement to execute. Not just a handlful.

Dirk said:
Not specifically. It was something that I read in an article. I did
learn the hard way about using the CurrentDB() function and the fact
that it creates a new object everytime it was used. It only took me
forever to figure out why the .RecordsEffected property of CurrentDB
kept returning 0 after I invoked the .Execute method. It was only
after I used instantiated an object variable using CurrentDB() that
the code worked as in ...

set db = CurrentDb()
db.Execute [sqlStatement]
Debug.print db.recordsEffected
set db = Nothing
Click to expand...


You can also do it without defining an explicit object variable, by
using the With construct:

With CurrentDb
.Execute strSQL
Debug.Print .RecordsAffected
End With
Click to expand...
 
D

Dirk Goldgar

David C. Holley said:
handn't thought about. I typically only use WITH's if there are
numerous statement to execute. Not just a handlful.
Click to expand...

I tend to use "With" any time I'm going to be referring to an object
more than once in a short section of code, especially if the object
reference needs to be interpreted (e.g., Forms!FormName). That way I
save on whatever time it takes to parse the reference and locate the
object. It probably doesn't make much practical distance, but it feels
tidier to me. I don't think any efficiency considerations would apply,
though, when if the object reference in question is already stored in an
object variable.

I also like using "With Me!ControlName" whenever I'm going to be
referring to multiple properties or methods of the control, because that
way if I ever want to change the name of the control, or copy the same
code for another control, I only have to change the name in one place.
 
Top