koughdur
asked on
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.
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.
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.
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?
This is whey I asked all the questions...
Will you be posting a sample Excel file?
ASKER
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.
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.
ASKER
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.