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..