autoexec macro, dialog box pops up

[Amod]

I have created a autoexec macro that runs according to a task scheduler. This
macro runs a query over a Linked table(linked to sharepoint list). now when
this scheduler open the access db, it gives me a dialog box specifying that
execution will affect the change in the lnked table(YES OR NO). Because this
macro will run on network server, its not possible to click yes all the time.
can you please suggest how can I click YES automatically through a macro or
this dialog box can be prevented from popping up. It does not come when i run
this macro for a query over a normal table. Please help!

 

[Steve Schapel]

Amod,

Can you please give the exact wording of the message in the dialog box?

Can you please give the exact details of the actions in the macro?

Thanks.

 

[Steve Schapel]

Amod,

Put a SetWarnings/No action in the macro, before the OpenQuery action.

 

[Amod]

Steve,

I do not have a "SetWarnings/No action "action in access 2007, is this a
condtion?
Do not see any such action in the drop down list.

Thanks!

 

[Steve Schapel]

Amod,

Perhaps you will need to click the 'Show All Actions' button on the
macro design ribbon.

 

[Amod]

thanks Steve, That worked in the way that i added more actions & set warning
action by setting argument to NO didnt display the dialog box but it didnt
execute the macro either.
I added set warning (with yes & no)above the open query action. I tried it
along with echo off & on, but it does not run the macro.
Please help dude!

 

[Steve Schapel]

Amod,

Before you put the SetWarnings action in the macro, did the query run?
With any records?

The addition of the SetWarnings should not affect whether the rest of
the query runs. It will only affect the display of the action query
confirmation messages. So I can't think of any reason why the rest of
the macro does not proceed. All I can suggest is that you have a
Condition in your macro, and the Condition does not evaluate to true,
and this is therefore resulting in the macro not proceeding. It is much
more likely to be due to the macro condition, or the number of records
returned by the query, or a failure of the query itself, than anything
to do with the SetWarnings.

 

[Amod]

yes it was working fine without the SetWarning & Echo actions
If you can give me your ID, I can send you the exact screenshot of the macro.

It does stops that dialog box to appear but then it does not run the query
as I can not see the desired records to be added in the destination table.

 

[Amod]

oThanks for the reply...
but I tried doing that....

Macro was working fine without the SetWarning & Echo actions.
If you can give me your ID, I can send you the exact screenshot of the macro.

It does stops that dialog box to appear but then it does not run the query
as I can not see the desired records to be added in the destination table.

 

[Steve Schapel]

Amod,

Can you show us please the exact expression you are using for the
Condition in the macro?

And can you also please post a copy/paste of the SQL view of the Append
Query?

Thanks.

 

[Amod]

Steve,
Here is the condtion I am using for the OpenQuery action:
DCount("[ID1]","testTbl")>DCount("[ID1]","New testTbl")

testTbl: source table (S)
New testTbl: destination table (D)
so when the # of records in S table are more than D table, macro runs. I
named this macro as Autoexec.
above this action I have SetWarnings action with Argument : No

The append query:
INSERT INTO [New testTbl] ( ID1, [First], [Last], Age, DOB, Employed,
Experience )
SELECT testTbl.ID1, testTbl.First, testTbl.Last, testTbl.Age, testTbl.DOB,
testTbl.Employed, testTbl.Experience
FROM testTbl
WHERE (((Exists (SELECT * FROM [New testTbl] WHERE [testTbl].[ID1] = [NEW
testTbl].[ID1] AND [testTbl].[First] = [NEW testTbl].[First] ))=False));

I am testing for ID1 & First columns to be different when the query should
run.

just a simple background: New testTbl is a linked table from SharePoint list.
so whenever the macro runs(used to when SetWarnings was not there) it pops
up the dialog message box that it will affect the table & you can not undo
the changes, want to run the query? Yes or No? So as I specified earlier, I
want this Yes to be performed automaticaaly when this macro runs.

Thanks..

 

[Steve Schapel]

Amod,

Just as a matter of interest, the dialog message when you run the append
query has nothing to do with the tabel being a linked table, or
SharePoint, or whatever. This is standard behaviour for an action
query, and would happen even if both tables were in the local Access file.

As a further "aside", please note that 'first' and 'last' are both
Reserved Words (i.e. have a special meaning) in Access, and as such it
is best not to use them as the names for fields or controls.

The structure of what you have done seems fine. And the insertion of
the SetWarnings action will not prevent the query from working. So all
I can expect is that the fact you are getting no records appended is
because of one of these:
- the number of records in the testTbl table is *not* greater than the
number of records in the New testTbl table.
- the query itself returns no records
- there are records to be appended, but doing so would fail because the
data violates a Validation Rule or would try to duplicate a unique index

Could you please try to make a query like this:
SELECT testTbl.ID1, testTbl.First, testTbl.Last, testTbl.Age,
testTbl.DOB, testTbl.Employed, testTbl.Experience
FROM testTbl LEFT JOIN [New testTbl] ON (testTbl.ID = [New testTbl].ID1)
AND (testTbl.[First] = [New testTbl].[First])
WHERE [New testTbl].ID1 Is Null

Run this query and see if it returns any records.

If it does, change it to an Append Query, and try and run it manually.

If that works, go to the New testTbl table and manually delete the
record(s) that were just appended.

Then set this query as the query that is run by the macro, and run the
macro. See if the append works.

If not, please confirm that the number of records in the testTbl table
is greater than the number of records in the New testTbl table.

--
Steve Schapel, Microsoft Access MVP

Steve,
Here is the condtion I am using for the OpenQuery action:
DCount("[ID1]","testTbl")>DCount("[ID1]","New testTbl")

testTbl: source table (S)
New testTbl: destination table (D)
so when the # of records in S table are more than D table, macro runs. I
named this macro as Autoexec.
above this action I have SetWarnings action with Argument : No

The append query:
INSERT INTO [New testTbl] ( ID1, [First], [Last], Age, DOB, Employed,
Experience )
SELECT testTbl.ID1, testTbl.First, testTbl.Last, testTbl.Age, testTbl.DOB,
testTbl.Employed, testTbl.Experience
FROM testTbl
WHERE (((Exists (SELECT * FROM [New testTbl] WHERE [testTbl].[ID1] = [NEW
testTbl].[ID1] AND [testTbl].[First] = [NEW testTbl].[First] ))=False));

I am testing for ID1 & First columns to be different when the query should
run.

just a simple background: New testTbl is a linked table from SharePoint list.
so whenever the macro runs(used to when SetWarnings was not there) it pops
up the dialog message box that it will affect the table & you can not undo
the changes, want to run the query? Yes or No? So as I specified earlier, I
want this Yes to be performed automaticaaly when this macro runs.

Thanks..

 

[Amod]

Steve,
Thanks for all the suggestions and points you told me.

I tried doing all this:
1. Created the query
2. Run the query: It gives me the records.
3. I created the append query, it also runs (with the dialog box) & appends
the records.
4. I created the Macro Autoexec which runs(with the dialog box) & appends
the records.
5. But when I created SetWarnings : action No. It does not pop that dialog
box BUT it does not append the records.

I know is something wierd, but I have a question for you, is this
SetWarnings action just compresses the dialog box or it also clicks(performs)
yes on that dialog box. Because I sure this Setwarnings action is not able to
perform that YES button click.


--
Amod Goyal
IT System Developer
NSK Precision America

Amod,

Just as a matter of interest, the dialog message when you run the append
query has nothing to do with the tabel being a linked table, or
SharePoint, or whatever. This is standard behaviour for an action
query, and would happen even if both tables were in the local Access file.

As a further "aside", please note that 'first' and 'last' are both
Reserved Words (i.e. have a special meaning) in Access, and as such it
is best not to use them as the names for fields or controls.

The structure of what you have done seems fine. And the insertion of
the SetWarnings action will not prevent the query from working. So all
I can expect is that the fact you are getting no records appended is
because of one of these:
- the number of records in the testTbl table is *not* greater than the
number of records in the New testTbl table.
- the query itself returns no records
- there are records to be appended, but doing so would fail because the
data violates a Validation Rule or would try to duplicate a unique index

Could you please try to make a query like this:
SELECT testTbl.ID1, testTbl.First, testTbl.Last, testTbl.Age,
testTbl.DOB, testTbl.Employed, testTbl.Experience
FROM testTbl LEFT JOIN [New testTbl] ON (testTbl.ID = [New testTbl].ID1)
AND (testTbl.[First] = [New testTbl].[First])
WHERE [New testTbl].ID1 Is Null

Run this query and see if it returns any records.

If it does, change it to an Append Query, and try and run it manually.

If that works, go to the New testTbl table and manually delete the
record(s) that were just appended.

Then set this query as the query that is run by the macro, and run the
macro. See if the append works.

If not, please confirm that the number of records in the testTbl table
is greater than the number of records in the New testTbl table.

--
Steve Schapel, Microsoft Access MVP

Steve,
Here is the condtion I am using for the OpenQuery action:
DCount("[ID1]","testTbl")>DCount("[ID1]","New testTbl")

testTbl: source table (S)
New testTbl: destination table (D)
so when the # of records in S table are more than D table, macro runs. I
named this macro as Autoexec.
above this action I have SetWarnings action with Argument : No

The append query:
INSERT INTO [New testTbl] ( ID1, [First], [Last], Age, DOB, Employed,
Experience )
SELECT testTbl.ID1, testTbl.First, testTbl.Last, testTbl.Age, testTbl.DOB,
testTbl.Employed, testTbl.Experience
FROM testTbl
WHERE (((Exists (SELECT * FROM [New testTbl] WHERE [testTbl].[ID1] = [NEW
testTbl].[ID1] AND [testTbl].[First] = [NEW testTbl].[First] ))=False));

I am testing for ID1 & First columns to be different when the query should
run.

just a simple background: New testTbl is a linked table from SharePoint list.
so whenever the macro runs(used to when SetWarnings was not there) it pops
up the dialog message box that it will affect the table & you can not undo
the changes, want to run the query? Yes or No? So as I specified earlier, I
want this Yes to be performed automaticaaly when this macro runs.

Thanks..

 

[Steve Schapel]

Amod,

As far as I understand it, the SetWarnings simply suppresses the display
of the confirmation dialog.

I am very sorry, I am out of ideas at this point regarding the cause of
the problem. It doesn't make sense to me.

If you were to use a VBA procedure rather than a macro, you could use
code like this:
CurrentDb.Execute "YourAppendQuery"
This would not require any SetWarnings provision, as this method does
not generate the confirmation messages.

 

[Amod]

Thanks Steve for all the input and your time. I know this is wierd but it is
not possible to click Yes on that dialog box...

Do you know some one I can contact for Infopath help. The discussion group
doesnt sound to be very active & responsive. I would appreciate if you can
help me find one.

Thanks again Steve & all the best!