Display paste error table from code

  • Thread starter Thread starter robert demo via AccessMonster.com
  • Start date Start date
R

robert demo via AccessMonster.com

I do not allow my users to directly interact with tables. Instead, I
present them with a datasheet view of certain tables. These datasheet view
forms can then be used to input large numbers of rows via "Paste Append"

Here's the problem. Sometimes there are paste errors. I recently paste
appended 8200 rows and there were only three paste errors, but because I
don't provide direct access to the tables (i.e. the database window is not
available to the user), these rows couldn't be displayed.

How could I via code present the opportunity to users to see the paste
error table (probably via a datasheet form). A couple of difficulties:

1) there may be more than one paste error table (perhaps from a previous
attempt), so how do I trap the name of the paste error table that MS Access
lists in their message box when a paste error occurs. If I can't I could
display a combobox that lists all the tables with the word "Paste" in the
table title and the user could select which one to view. There would also
be an option to delete the table.

2) What can I use as a trigger in code to make the viewing of paste error
tables available. I guess it could be a menu bar item (I use custom menus)
, but is there any way to use the MS Access event that displays the Paste
Errors message as a trigger?

Any ideas or help is greatly appreciated.

Thanks.
 
Robert,

Even with the database window hidden, you can easily display the paste
errors table in code simply by means of:

DoCmd.OpenTable "Paste Errors"

Your wish to select one of several is not realistic, though; each new
Paste Errors table simply overwrites the previous one, if one exists, so
there will only ever be one (if at all)... at least this is how it works
in A2K, don't know if this has changed in later editions. If it has
indeed, it would be easy by means of a form with a listbox whose
rowsource is:

SELECT Name from Msysobjects
WHERE Type = 1 and Name Like "Paste Err*"

I'm not sure if you could use the paste errors message as a trigger, but
can suggest some alternatives: (a) count the number of records in the
destination table before and after, compare the difference with number
of records attempted to paste, or (b) check for the existence of a paste
erroes table (or number thereof, in case later versions support multiple).

HTH,
Nikos
 
Thanks for your response.

How do you suggest that I make the ability to view the paste errors table
to the user.

When paste errors occur MS Access will notify the user. But thereafter,
what would be a good way to allow the user to view the table.

Do they have to know that there is a menu option to view the table? Or is
there someway for me to bring up a messagebox, after the user closes the MS
Access paste error message, that asks if the user wants to see the paste
errors table form.

Thanks.
 
Robert,

I'm not sure if and how paste errors can be captured; since it is paste
errors, I suppose it is the result of a manual paste in a table, right?
You can trap an error that occurs in code execution, but I don't know if
the same can be done for one that occurs as a result of a manual
operation - actually I doubt it. One workaround could be to use a timer
event in a form that stays open at all times (a switchboard, if one
exits, or a dummy hidden form just for the purpose) to check for the
existence of a Paste Errors table every few seconds, and either notify
the user or just open it right away.

That said, I am skeptical about the practice of manual pasting in a
table; I wouldn't want my users doing that. Are you convinced there is
no better way to do it? For instance, if it's data from an outside
source, like an Excel spreadsheet, it would be a lot safer to import
programatically while also running integrity checks. If you explain your
situation, either myself or someone else will probably come up with a
neater proposal.

HTH,
Nikos
 
Back
Top