Delete message - Can is selectively be turned off?

[N. Graves]

Thanks for take time to read my question!!

I'm using code that will automatically delete rows of data in a field
and of course when you do this Access will prompt you that you are
about to delete x number of rows.

Most of the time I want this message be displayed. Can I use code
that will delete specified rows and not give the message?

Here is the code that I'm currently using:

Dim SQLDelete As String

SQLDelete = "delete from Equipment where EquipID = " & Me.List_Device

DoCmd.RunSQL SQLDelete

Thanks

N. Graves

[Allen Browne]

Hi Tony

As you know, either will work, but CurrentDb upates all the collections of
the current database (usually dbEngine(0)(0)) , creates a new object, and
points it to the current database.

dbEngine(0)(0) is therefore better because:
1. It is thousands of times more efficient, since it does not have to flush
the collections, and the object already exists.

2. It survives more than one line, e.g. the next line might be:
MsgBox dbEngine(0)(0). RecordsAffected & " deleted."

OTOH, CurrentDb() is better because:
1. It returns correct results if any fields/tables changed recently.

2. The current database might *not* be dbEngine(0)(0). Examples:
- after running a wizard,
- you work with transactions when more than one Database is open (which
happens automatically if you are working with RecordsetClone) .

So, my personal practice is:
- Use CurrentDb() during where tables/fields are being created/modified.
- Use dbEngine(0)(0) for executing queries (other than DDL queries).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tony Toews" <tt****@teluspl anet.net> wrote in message
news:a8******** *************** *********@4ax.c om...

"Allen Browne" <Al*********@Se eSig.Invalid> wrote:

However, you won't know if anything went wrong with the action query. The
following method is better, as it gives a message only if there is a
problem:
dbEngine(0)(0). Execute strSQL, dbFailOnError

Just curious. Why not currentdb.execu te etc?

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

[N. Graves]

David, Thanks for you information.

I'm getting a compile error for the user-defive type. My inexperience
will not let me continue could you please post this information for
me.

N. Graves
On Mon, 28 Jun 2004 21:25:56 GMT, "David W. Fenton"
<dX********@bwa y.net.invalid> wrote:

"Dave" <[e-mail] > wrote in
news:cb******* ***@sparta.btin ternet.com:

try wrapping the DoCmd like this

Application.Set Option "Confirm Record Changes", False
Application.Set Option "Confirm Document Deletions", False
Application.Set Option "Confirm Action Queries", False
DoCmd.RunSQL SQLDelete
Application.Set Option "Confirm Record Changes", True
Application.Set Option "Confirm Document Deletions", True
Application.Set Option "Confirm Action Queries", True
i use all three lines to turn everything off when distributing
run-times as i dont want uses to have to fiddle around

you will probably find that you dont need all three lines and just

Application.Set Option "Confirm Action Queries", False
DoCmd.RunSQL SQLDelete
Application.Set Option "Confirm Action Queries", True

will do

Why in the world use RunSQL and all those extra switches when in
99.999% of cases, Executing the SQL directly will do the job without
needing to fiddle with warnings and options?

If you want something as simple as DoCmd.RunSQL, try this:

Public Function ExecuteSQL(strS QL As String, _
Optional db As Database) As Boolean
On Error GoTo errHandler

If db Is Nothing Then
Set db = CurrentDB() ' or DBEngine(0)(0)
End If
db.Execute strSQL, dbFailOnError

exitRoutine:
Exit Function

errHandler:
MsgBox "There was an error executing your SQL string: " _
& vbCrLf & vbCrLf & Err.Number & ": " & Err.Description , _
vbExclamation, "Error in mdlDWF.ExecuteS QL()"
Debug.Print "SQL Error: " & strSQL
Resume exitRoutine
End Function

You can then call that as:

ExecuteSQL strSQL

The db argument is useful if you're already caching a global db
variable and want to re-uses it.

[David W. Fenton]

N. Graves <ng*****@REMOVE yahoo.com> wrote in
news:a4******** *************** *********@4ax.c om:

I'm getting a compile error for the user-defive type. My
inexperience will not let me continue could you please post this
information for me.

As I wrote to you in email (many people, myself included, consider
it impolite to both post and email; if you do it, you should
indicate that you're doing so in your message. Had you done so I
would have responded only in the newsgroup, not in private email),
you surely are using A2K or later and are falling victim to MS's
ridiculously stupid decision to not use DAO as the default data
access interface.

To fix the problem, while viewing a code module, go to the TOOLS
menu and choose REFERENCES. Search for Microsoft DAO 3.6 Object
Library and check it off. If you're not using ADO, then uncheck it,
as it's not needed (and for many things, Access provides interfaces
to ADO objects like CurrentConnecti on via the Application objet).

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

[N. Graves]

David thanks you so much for taking the to answer both the newsgroups
post and my email.

I was not trying to be rude but thought I was probably not the only
person that could learn from you knowledge is why I posted it back in
this group.

This group is so great and you folks that answer these questions are
great. I have used it quite a bit lately and I did not mean to rude.
I apologize and will not do both again.

Thanks again for you responses. They are working perfectly!

N. Graves

On Wed, 07 Jul 2004 01:10:26 GMT, "David W. Fenton"
<dX********@bwa y.net.invalid> wrote:

N. Graves <ng*****@REMOVE yahoo.com> wrote in
news:a4******* *************** **********@4ax. com:

I'm getting a compile error for the user-defive type. My
inexperience will not let me continue could you please post this
information for me.

As I wrote to you in email (many people, myself included, consider
it impolite to both post and email; if you do it, you should
indicate that you're doing so in your message. Had you done so I
would have responded only in the newsgroup, not in private email),
you surely are using A2K or later and are falling victim to MS's
ridiculously stupid decision to not use DAO as the default data
access interface.

To fix the problem, while viewing a code module, go to the TOOLS
menu and choose REFERENCES. Search for Microsoft DAO 3.6 Object
Library and check it off. If you're not using ADO, then uncheck it,
as it's not needed (and for many things, Access provides interfaces
to ADO objects like CurrentConnecti on via the Application objet).