Unable to append all the data to the table when importing

[dp724 via AccessMonster.com]

At the click of a button my code begins importing my Excel data in to an
Access table; but I get the following message:

"Microsoft Acess was unable to append all the data to the table."

I get it because I have a validation rule in place which prevents the
violating data from importing, which is fine; so I just hit 'yes' to finish
importing.

Can someone please help by providing some instruction or preferably some code
I can plug in to my code, which provides a 'yes', and the importing is
processed without the message.

Thanks in advance.

Dave

 

[dp724 via AccessMonster.com]

OK!

I'm still learning.

Docmd.SetWarnings False

Does the trick.

 

[Pat Hartman]

Make ABSOLUTELY CERTAIN to turn the warnings back on after the import or
you'll be sorrrrry. In fact, leaving warnings off is so dangerous that when
I have ocassion to turn them off, I always turn on the hourglass so I have a
visual reminder should the warnings not get turned back on. Then after I
turn the warnings on, I turn the hourglass off.

With the warnings turned off, you will not be prompted to save modified
objects when you close them. So if you spend an hour changing a form and
close it without saving first, ALL your changes are discarded.

 

[Jerry Whittle]

At or near the beginning of the code:

DoCmd.SetWarnings False

Towards the end of the code after the import is finished:

DoCmd.SetWarnings True

 

[Pete D.]

I would also add something in case import fails to deal with possible error
and fall through to an error exit which turns the warnings back on no matter
what happens.

 

[boblarson]

And, as Pete D. mentioned, I ALWAYS put an error handler in any code that I
use DoCmd.SetWarnings because if it errors out before it can get to the place
where it resets the warnings, you will be left without warnings. So, always
use a

On Error GoTo err_handler

and in the error handler make the first line

DoCmd.SetWarnings True

--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

Make ABSOLUTELY CERTAIN to turn the warnings back on after the import or
you'll be sorrrrry. In fact, leaving warnings off is so dangerous that when
I have ocassion to turn them off, I always turn on the hourglass so I have a
visual reminder should the warnings not get turned back on. Then after I
turn the warnings on, I turn the hourglass off.

With the warnings turned off, you will not be prompted to save modified
objects when you close them. So if you spend an hour changing a form and
close it without saving first, ALL your changes are discarded.

 

[Tony Toews [MVP]]

At the click of a button my code begins importing my Excel data in to an
Access table; but I get the following message:

"Microsoft Acess was unable to append all the data to the table."

I get it because I have a validation rule in place which prevents the
violating data from importing, which is fine; so I just hit 'yes' to finish
importing.

Can someone please help by providing some instruction or preferably some code
I can plug in to my code, which provides a 'yes', and the importing is
processed without the message.

I always either import to a temporary table or programmatically
examine the cells individually and examine the data for errors. If
any errors I then display those along with a cell reference so the
user can correct the data.

Once I know the data is valid then I'll do the import.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/