Check Transfettext

[imtksl]

A Procedure with the purpose of checking data import is as follows;

Sub CheckLoadData()

...
DoCmd.RunMacro ("MRO_IMPORT_SRC_DATA")
...

End Sub

MRO_IMPORT_SRC_DATA would import data from a plain text file into a table
which PK is set. This macro uses TransferText action.

If there is part of data to be loaded violates the PK constraint, a dialog
"Microsoft Access was unable to append all the data to the table...".

If in the code I added: "DoCmd.SetWarnings False", the above warning
dialog would not appear, but the problem is it will still insert those
data which are unique into the table.

My questions are
1) how can I stop the entire data loading process when duplicates are
found

2) how can I know there is warning when executing the above MACRO, when
SetWarnings is turned already turned off.

Thanks!

 

[Allen Browne]

Macros are not powerful enough to do what you need. You will need to use a
transaction. This lets you BeginTrans, and then RollBack if there is a
problem, or CommitTrans if everything goes okay.

For details on how to write this code and set up transactions reliably, see
the discussion and example code in this article:
http://members.iinet.net.au/~allenbrowne/ser-37.html

 

[imtksl]

Thanks Allen!

Please correct my concept if I am wrong. Do BeginTrans / CommitTrans only
works with UPDATE statement but not INSERT statement, since I have tried
this method and noticed that INSERT statement was automatically committed.

 

[Allen Browne]

No. You can use transactions around any action query (UPDATE, INSERT,
DELETE), even recordsets opened with a SELECT query that modify the data.