Copy/Paste From Excel Corrupts(?) Access DB

Occasionally when I copy/paste a range of data from Excel 2010 into Access 2010 to create a new table the operation fails.  After failing, the database no longer allows me to delete objects or copy/paste objects.

The only workaround I have found for this is to create a blank database and then import all of the objects from the "corrupted" database into the new one.

Has anyone else experienced this?  Is my DB truly corrupted or did some settings/permissions get reset?  If so, is there a way to set them back so I can delete/copy/paste DB objects once again?
koughdurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hamed NasrRetired IT ProfessionalCommented:
Compact and repair, may solve the problem.
Jeffrey CoachmanMIS LiasonCommented:
How big is this range?
If the range a correctly formatted "list"?
ex.:
Field names in row 1
No blank rows or columns
No data in columns with no headings
No invalid characters (line feeds, carriage returns, ...etc)

Also, try importing the Excel data from Access instead of copy/pasting from Excel)
External Data-->Import & Link-->Excel
Browse to the file
Click: Open
Select: "Import...."
OK
Select First Name contains Field names
Next
Next
Let Access assign a primary key
Name the new table

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
I'm with Jeff on this one.

I strongly advise my clients not to try to copy cells from Excel and Paste them into Access, this is a recipe for disaster.

I generally provide a method for them to link the Excel spreadsheet to the database using the TransferSpreadsheet method, and then upload the data from Excel via a query.

I do that manually where this is not a common process, but if it is a regular occurance, you should automate the process.

Dale
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

koughdurAuthor Commented:
Thanks for all the quick responses:

hnasr:  I have compacted/repaired several times to no effect.

Jeff:  In most cases the range is rather small (maybe upt to ten columns and maybe 100 rows).  The range is usually well ordered, i.e. all columns have string headers (no blanks), all rows have similar data.

Import doesn't work well for me.  I am not using Access as a well-designed application.  I am using Access for like someone would use Excel, i.e. as a data processing tool.  Some processing steps are easier in Excel than in Access so I will query some data into Access, Copy/paste it into Excel, do some processing there, and then paste back.  Import/Export is OK, but clunky in comparison.

Dale:  Again, I am not working with a well-defined application.  I am processing data and moving seamlessly (most of the time) between Access and Excel.

I don't see why copy/paste should be "a recipe for disaster".  I have never had problems copy/pasting into or out of Access tables.  The only problems I've had as copy/creating a table.  This is a function which Access nominally supports.  But evidently it is a trap that I have fallen into silly me.
Jeffrey CoachmanMIS LiasonCommented:
Its not that copy/paste is bad, its just that a lot of things can go wrong.
1. You can forget that you have something on the clipboard, and accidentally copy something else
2. You may get the Access "The database has been placed in a state by Admin" message
3. You might make a mistake in selecting the range
4. If the Excel file is not saved, ...the previously selected range may be pasted.

All this being said, your issue here is one I have never heard of before.
Does this happen for all copy/pastes to Access?
Is this for all excel ranges?
All Excel files?
All users?
All databases?

Can you post a sample Excel file that fails, ...for us to examine?

JeffCoachman
koughdurAuthor Commented:
Jeff,

I love working with Access as an extension of Excel.  There are certain things, like I said, that Access does better than Excel and vice-versa.  So for a particular task I am copy/pasting various ranges back and forth between the two apps.  Only later do I find out that I can no longer delete/copy/paste.  I can't remember exactly what particular operation caused the "corruption".

I can try and keep better track and report back when this happens again.  I was hoping this was a known issue with a known resolution (besides creating a blank database and copying everything over to that, which is what I resorted to yesterday.)

Evidently this is not a well-known problem with an easy solution.
Hamed NasrRetired IT ProfessionalCommented:
One other way, instead of copy and paste, is to link the excel range in access. Then you can use the linked table as an access table.
Jeffrey CoachmanMIS LiasonCommented:
Again, ...this is an issue that I have never hear of.
This is whey I asked all the questions...

Will you be posting a sample Excel file?
koughdurAuthor Commented:
Thanks for the suggestions.  I will not be posting any sample Excel files or MS Access files.  If I have time, I'll very carefully check MS Access after each-and-every copy paste action to see which one(s) cause the corruption.

Since I won't know how large my copy/paste range is ahead of time, I may just create a generic table with 100 columns that I can paste stuff into.  Maybe I can create two tables:  One for the data and one for the headers, and then create a subroutine that creates a new table with the data from one using the headers from the other as field names.  It's a kludge, but it may allow me to work almost as effortlessly between Excel and Access as I currently do.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.