No warnings issued when Access is started via Automation

[RichardP]

Hi there everyone - I'm new to this forum.

I am having an issue when running an application from an instance of Access
which has been started through automation (early or late bound, makes no
difference).

No warning / confirmation messages are issued (eg. when running action
queries, deleting records from a datasheet, deleting database objects such as
tables).

It's as if I have executed the command "DoCmd.SetWarnings False", but I
haven't at all. If I explicitly execute the command "DoCmd.SetWarnings True"
(in an attempt to force warnings to be displayed), this makes no difference
also - I still receive no warnings or confirmations.

If I run the very same database from an instance of Access where I have
launched Access normally (manually), everything works normally and I receive
warnings, confirmations, etc.

This is undesirable as I would like the users to receive cancellable
confirmation messages, for example when they attempt to delete records from
various forms in my applications.

I notice that when deleting records in forms, the BeforeDelConfirm and
AfterDelConfirm events fire, although no actal confirmation between the two
events is displayed. If necessary I can go through the application and add
code to such events - though I'm trying to keep things lean and simple, so
would rather avoid that if possible.

My working environment is limited to Access 2002 (XP) Service Pack 2.

Has anyone else experienced this?

Thanks in advance - Richard

[salad]

RichardP wrote:

Hi there everyone - I'm new to this forum.

I am having an issue when running an application from an instance of Access
which has been started through automation (early or late bound, makes no
difference).

No warning / confirmation messages are issued (eg. when running action
queries, deleting records from a datasheet, deleting database objects such as
tables).

It's as if I have executed the command "DoCmd.SetWarnings False", but I
haven't at all. If I explicitly execute the command "DoCmd.SetWarnings True"
(in an attempt to force warnings to be displayed), this makes no difference
also - I still receive no warnings or confirmations.

If I run the very same database from an instance of Access where I have
launched Access normally (manually), everything works normally and I receive
warnings, confirmations, etc.

This is undesirable as I would like the users to receive cancellable
confirmation messages, for example when they attempt to delete records from
various forms in my applications.

I notice that when deleting records in forms, the BeforeDelConfirm and
AfterDelConfirm events fire, although no actal confirmation between the two
events is displayed. If necessary I can go through the application and add
code to such events - though I'm trying to keep things lean and simple, so
would rather avoid that if possible.

My working environment is limited to Access 2002 (XP) Service Pack 2.

Has anyone else experienced this?

Thanks in advance - Richard

Try this. Substitute the word Table1 with a table you can safely delete
all of the records from.

Dim strSQL As String
Docmd.SetWarnings True
MsgBox "Execute"
strSQL = "DELETE Table1.* FROM Table1"
CurrentDb.Execute strSQL

MsgBox "RunSQL"
DoCmd.RunSQL strSQL
MsgBox "Done"

[RichardP via AccessMonster.com]

Consistent with above. In automation mode, there aren't any message dialogs
issued other than the "Execute", "RunSQL", and "Done".

In normal mode, I get the "You are about to delete n row(s) from the
specified table" [Yes/No] confirmation prompt dialog in between the "RunSQL"
and "Done" messages.
salad wrote:

>Hi there everyone - I'm new to this forum.

[quoted text clipped - 30 lines]

>>
Thanks in advance - Richard

Try this. Substitute the word Table1 with a table you can safely delete
all of the records from.

Dim strSQL As String
Docmd.SetWarnings True
MsgBox "Execute"
strSQL = "DELETE Table1.* FROM Table1"
CurrentDb.Execute strSQL

MsgBox "RunSQL"
DoCmd.RunSQL strSQL
MsgBox "Done"

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200609/1

[salad]

RichardP via AccessMonster.com wrote:

Consistent with above. In automation mode, there aren't any message dialogs
issued other than the "Execute", "RunSQL", and "Done".

In normal mode, I get the "You are about to delete n row(s) from the
specified table" [Yes/No] confirmation prompt dialog in between the "RunSQL"
and "Done" messages.

I guess I don't know what you mean by automation mode vs normal mode. I
don't get a warning message with .Execute. I do get one with RunSQL
asking for confirmation. If that's the case, use Docmd.RunSQL.

>
salad wrote:

>>>Hi there everyone - I'm new to this forum.

[quoted text clipped - 30 lines]

>>>Thanks in advance - Richard

Try this. Substitute the word Table1 with a table you can safely delete
all of the records from.

Dim strSQL As String
Docmd.SetWarnings True
MsgBox "Execute"
strSQL = "DELETE Table1.* FROM Table1"
CurrentDb.Execute strSQL

MsgBox "RunSQL"
DoCmd.RunSQL strSQL
MsgBox "Done"

[RichardP via AccessMonster.com]

Normal meaning: eg. you start Access by manually launching from an Access
shortcut, then you manually open the desired database, and everything happens
as expected.

Automation meaning: eg. you execute the following (example) code -

Dim objAccess As Object
Set objAccess = CreateObject("access.application")
objAccess.OpenCurrentDatabase "myaccessapp.mdb"
...

In a commercial application which insulates the users from underlying data
processing functionality, you can't rely on DoCmd.RunSQL to provide reliable
results, so I always use the Execute method for any action queries.

Anyone else come across this issue at all?

I found another forum thread where someone else was having the same issue:
http://snipurl.com/wu2z
Richard P [MCP]

salad wrote:

>Consistent with above. In automation mode, there aren't any message dialogs
issued other than the "Execute", "RunSQL", and "Done".

In normal mode, I get the "You are about to delete n row(s) from the
specified table" [Yes/No] confirmation prompt dialog in between the "RunSQL"
and "Done" messages.

I guess I don't know what you mean by automation mode vs normal mode. I
don't get a warning message with .Execute. I do get one with RunSQL
asking for confirmation. If that's the case, use Docmd.RunSQL.

>>>>Hi there everyone - I'm new to this forum.

[quoted text clipped - 14 lines]

>> DoCmd.RunSQL strSQL
MsgBox "Done"

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200609/1

[David W. Fenton]

"RichardP via AccessMonster.com" <u26938@uwewrote in
news:66ac63cb84d67@uwe:

Normal meaning: eg. you start Access by manually launching from
an Access shortcut, then you manually open the desired database,
and everything happens as expected.

Automation meaning: eg. you execute the following (example) code
-

Dim objAccess As Object
Set objAccess = CreateObject("access.application")
objAccess.OpenCurrentDatabase "myaccessapp.mdb"
...

Why would you ever do this?

In a commercial application which insulates the users from
underlying data processing functionality, you can't rely on
DoCmd.RunSQL to provide reliable results, so I always use the
Execute method for any action queries.

Anyone else come across this issue at all?

I've never used anything but .Execute once I got beyond the
training-wheels stage of Access development.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[salad]

David W. Fenton wrote:

"RichardP via AccessMonster.com" <u26938@uwewrote in
news:66ac63cb84d67@uwe:

>>Normal meaning: eg. you start Access by manually launching from
an Access shortcut, then you manually open the desired database,
and everything happens as expected.

Automation meaning: eg. you execute the following (example) code
-

Dim objAccess As Object
Set objAccess = CreateObject("access.application")
objAccess.OpenCurrentDatabase "myaccessapp.mdb"
...

Why would you ever do this?

>>In a commercial application which insulates the users from
underlying data processing functionality, you can't rely on
DoCmd.RunSQL to provide reliable results, so I always use the
Execute method for any action queries.

I have no idea what that means. RunSQL and Execute should produce the
same results.

>>
Anyone else come across this issue at all?

I've never used anything but .Execute once I got beyond the
training-wheels stage of Access development.

..Execute has a RecordsAffected property. Maybe that will assist the OP.
Run the query and then check the .RecordsAffected property.

[deluxeinformation]

RichardP via AccessMonster.com wrote:

Normal meaning: eg. you start Access by manually launching from an Access
shortcut, then you manually open the desired database, and everything happens
as expected.

Automation meaning: eg. you execute the following (example) code -

Dim objAccess As Object
Set objAccess = CreateObject("access.application")
objAccess.OpenCurrentDatabase "myaccessapp.mdb"
...

In a commercial application which insulates the users from underlying data
processing functionality, you can't rely on DoCmd.RunSQL to provide reliable
results, so I always use the Execute method for any action queries.

Anyone else come across this issue at all?

I found another forum thread where someone else was having the same issue:
http://snipurl.com/wu2z

By default warnings are off. Are you issuing a docmd.setwarnings true
somewhere within the .mdb being started or are you doing it from your
application instance, i.e.

Dim objAccess As Object
Set objAccess = CreateObject("access.application")
objAccess.DoCmd.SetWarnings True
objAccess.OpenCurrentDatabase "myaccessapp.mdb"

?

Bruce

[deluxeinformation]

RichardP via AccessMonster.com wrote:

Normal meaning: eg. you start Access by manually launching from an Access
shortcut, then you manually open the desired database, and everything happens
as expected.

Automation meaning: eg. you execute the following (example) code -

Dim objAccess As Object
Set objAccess = CreateObject("access.application")
objAccess.OpenCurrentDatabase "myaccessapp.mdb"
...

In a commercial application which insulates the users from underlying data
processing functionality, you can't rely on DoCmd.RunSQL to provide reliable
results, so I always use the Execute method for any action queries.

Anyone else come across this issue at all?

I found another forum thread where someone else was having the same issue:
http://snipurl.com/wu2z

Also, you may want to try:

Dim objAccess As Object
Set objAccess = CreateObject("access.application")
objAccess.SetOption "Confirm Action queries", True
objAccess.SetOption "Confirm Record Changes", True
objAccess.OpenCurrentDatabase "myaccessapp.mdb"

HTH,
Bruce

[RichardP via AccessMonster.com]

Just like to say thanks to all for the responses so far. In answer to some of
those responses:

Re: David's question: Why would I ever do this? -
----------------------------------------------------------------
Where I work they have an access database launcher code written into an Excel
add-in (don't ask!) and a suite of access applications. The launcher method
is as aforementioned - to instanciate Access with CreateObject and then open
the database . Everyone's reasonably satisfied with this way of things, but
I discovered this shortcoming of not receiving critical warning messages in
data entry situations and more. I now have to find a way of getting these
warnings enabled. It would take a fair degree of effort to persuade them to
change their launcher code as it is quite a regimented environment.
Re: David's statement: I've never used anything but .Execute
once I got beyond the training-wheels stage of Access development.
----------------------------------------------------------------
I couldn't have put it better myself. The likes of RunSQL and SetWarnings are
hangers-on from macro world.
Re: salad's statement: RunSQL and Execute should produce the same results.
----------------------------------------------------------------
They should if everything all data constituents are valid and there is no
capacity for error during execution.
In any serious application, however, the user will need to be insulated from
warning messages and any execution failings will need to be properly handled
by subsequent code. So some programmers use SetWarnings False before their
RunSQL statement, but this way some data errors that occur will not trigger a
handlable error in VBA (eg. referential integrity, invalid data values). So
using the Execute method is the only real way to go.
Re: Bruce's question: Are you issuing a docmd.setwarnings true?
----------------------------------------------------------------
I mentioned I've tried setting this in my original post, but yes, I've also
tried setting this statement exactly as your code suggests, but sadly this
makes no difference (except in A97, it seems - see below).
Re: Bruce's suggestion: SetOption for "Confirm Record Changes" and "Confirm
Action Queries"
----------------------------------------------------------------
Thanks - I tried explicitly as you suggested, but no difference I'm afraid.
I also checked those settings beforehand by reading the values with the
GetOption statement - these were both set to True already.
I did a bit more testing. In Access 97, as Bruce suggested, you can issue a
SetWarnings True and this indeed brings back warning messages. Then I tried
2000, 2002 and 2003 - the warnings are switched off independently of the
SetWarnings setting. So, the issue seems specific to Access 2000 and above
(though I haven't tried it with beta 2007).

Has anyone else tried this and experienced different results to me (on
versions 2000 or above) ?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200609/1

[salad]

RichardP via AccessMonster.com wrote:

Just like to say thanks to all for the responses so far. In answer to some of
those responses:

Re: salad's statement: RunSQL and Execute should produce the same results.
----------------------------------------------------------------
They should if everything all data constituents are valid and there is no
capacity for error during execution.
In any serious application, however, the user will need to be insulated from
warning messages and any execution failings will need to be properly handled
by subsequent code. So some programmers use SetWarnings False before their
RunSQL statement, but this way some data errors that occur will not trigger a
handlable error in VBA (eg. referential integrity, invalid data values). So
using the Execute method is the only real way to go.

At this point I have no idea what you really need or even what you want
or what the original question was. I have no idea if you want or don't
want warnings. I hope you found a solution in the replies that
satisfies your quest.

[RichardP via AccessMonster.com]

Sorry for your confusion. To clarify, I need warnings issued when running
databases launched via Access 2002 through automation. Unfortunately no
replies have yet provided a solution.
salad wrote:

>At this point I have no idea what you really need or even what you want
or what the original question was. I have no idea if you want or don't
want warnings. I hope you found a solution in the replies that
satisfies your quest.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200609/1

[RichardP via AccessMonster.com]

Further to my last message, and to help clarify further, the specific
warnings I need to see issued include:

- The warning you receive when manually deleting record(s) in a form, and it
says "You are about to delete n record(s)."
- Error messages that occur in a form when the user attempts to manually
enter a duplicate record into a table which does not permit duplicate entries.

RichardP wrote:

>Sorry for your confusion. To clarify, I need warnings issued when running
databases launched via Access 2002 through automation. Unfortunately no
replies have yet provided a solution.

>>At this point I have no idea what you really need or even what you want
or what the original question was. I have no idea if you want or don't
want warnings. I hope you found a solution in the replies that
satisfies your quest.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200609/1

[Granny Spitz via AccessMonster.com]

RichardP wrote:

Further to my last message, and to help clarify further, the specific
warnings I need to see issued include:

- The warning you receive when manually deleting record(s) in a form, and it
says "You are about to delete n record(s)."
- Error messages that occur in a form when the user attempts to manually
enter a duplicate record into a table which does not permit duplicate entries.

Here's an example:

Dim accObj As Object
Dim fActionQrySetting As Boolean
Dim fRecChgsSetting As Boolean

Set accObj = CreateObject("Access.Application")
fActionQrySetting = accObj.GetOption("Confirm Action queries") ' Save
curr settings.
fRecChgsSetting = accObj.GetOption("Confirm Record Changes")
accObj.SetOption "Confirm Action queries", True
accObj.SetOption "Confirm Record Changes", True
accObj.OpenCurrentDatabase "C:\Test\db1.mdb"
accObj.UserControl = True
accObj.DoCmd.RunSQL "INSERT INTO Bldg (Code) VALUES ('C34');" '
Duplicate entry.
accObj.DoCmd.RunSQL "DELETE * FROM Bldg;"
accObj.SetOption "Confirm Action queries", fActionQrySetting '
Reset users's settings.
accObj.SetOption "Confirm Record Changes", fRecChgsSetting
accObj.Quit
Set accObj = Nothing

But wouldn't life be easier if you just launched the Access database with VBA
Shell() and allowed the users to keep their own settings for action queries
and record changes, instead of forcing all users to repeatedly confirm that
they *really do* want to tie their shoelaces?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200609/1

[RichardP via AccessMonster.com]

Well done - that's the setting that's eluded me - "UserControl" - I haven't
noticed that before!
Sorted - many thanks.

I hear what you're saying about allowing the users to maintain their own
preferences about warnings, however in the context of this organisation and
the applications in question it is justified - the users aren't power users
and aren't the type who are even aware of these settings; furthermore there
won't be many instances where they need to delete records, but if they do,
then the confirmation is sensible, because Access doesn't offer a record
'undo' (undelete) facility like they are accustomed to having in Excel.

But the bigger problem was the fact that the form data errors weren't being
issued under automation - so duplicate records weren't being warned about,
and the form refused to close until the user had figured out what the problem
was, or had abandoned their data entry completely!

Thanks again for the solution - (Application.UserControl property setting).
Granny Spitz wrote:

>Further to my last message, and to help clarify further, the specific
warnings I need to see issued include:

[quoted text clipped - 3 lines]

>- Error messages that occur in a form when the user attempts to manually
enter a duplicate record into a table which does not permit duplicate entries.

Here's an example:

Dim accObj As Object
Dim fActionQrySetting As Boolean
Dim fRecChgsSetting As Boolean

Set accObj = CreateObject("Access.Application")
fActionQrySetting = accObj.GetOption("Confirm Action queries") ' Save
curr settings.
fRecChgsSetting = accObj.GetOption("Confirm Record Changes")
accObj.SetOption "Confirm Action queries", True
accObj.SetOption "Confirm Record Changes", True
accObj.OpenCurrentDatabase "C:\Test\db1.mdb"
accObj.UserControl = True
accObj.DoCmd.RunSQL "INSERT INTO Bldg (Code) VALUES ('C34');" '
Duplicate entry.
accObj.DoCmd.RunSQL "DELETE * FROM Bldg;"
accObj.SetOption "Confirm Action queries", fActionQrySetting '
Reset users's settings.
accObj.SetOption "Confirm Record Changes", fRecChgsSetting
accObj.Quit
Set accObj = Nothing

But wouldn't life be easier if you just launched the Access database with VBA
Shell() and allowed the users to keep their own settings for action queries
and record changes, instead of forcing all users to repeatedly confirm that
they *really do* want to tie their shoelaces?

--
Message posted via http://www.accessmonster.com

[David W. Fenton]

"RichardP via AccessMonster.com" <u26938@uwewrote in
news:66c8480f589a5@uwe:

But the bigger problem was the fact that the form data errors
weren't being issued under automation - so duplicate records
weren't being warned about, and the form refused to close until
the user had figured out what the problem was, or had abandoned
their data entry completely!

I think the way it's designed makes sense precisely because the main
use of automation does not involve user interaction. Your
circumstances force you to use automation, but you're not using it
for its designed purpose.

Why does your application launcher have to use automation? Why can't
it use SHELL or ShellExecute?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[RichardP via AccessMonster.com]

I agree, the way it's designed does make sense.

If it were up to me I would also adopt the approach of ShellExecute - however,
it's out of my direct control. Looking at the code documentation of the app
launcher there's some notes there along the lines of co-existence of Access
97 and Access 2000/2002 installations on the same user workstation and the
late bound instanciation somehow overcoming problems of electing the correct
Access EXE for the MDB file association, although I can't see what their
original method for starting Access was, so it's difficult to see precisely
what the issue was for them.

In the meantime, so far, setting the UserControl property to True appears to
be working around the original issue successfully.

Thanks for the all the interest and contributions.

David W. Fenton wrote:

>I think the way it's designed makes sense precisely because the main
use of automation does not involve user interaction. Your
circumstances force you to use automation, but you're not using it
for its designed purpose.

Why does your application launcher have to use automation? Why can't
it use SHELL or ShellExecute?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200609/1