Hi, Steve.
The most likely culprits are that your data focal has inserted too many
characters into at least one cell in the spreadsheet or else has reordered
the columns. While Excel doesn't enforce field sizes, Access sure does. If
even one cell value has 21 characters in a column in the Excel spreadsheet
and the corresponding text field in the Access table has a size of 20
characters, you'll get a pasting error message and Access will create a
"Pasting Errors" table. Even a mistyped "date" value can cause a pasting
error. If your data focal reordered the columns, then Access will expect the
fields for each record in a certain data type's format and size, and that's
not what Access is getting. Hence, the pasting error.
First check to ensure that the columns in the spreadsheet haven't been
reordered. If this is not the case, then check your "Pasting Errors" table.
Each problem record will be identified. You have a couple of options to fix
this.
If it's just a single record or so, the excessive field size may be obvious
when you count the number of characters in each field in the "Pasting Errors"
table and compare them to your Access table's field size for each field to
find the offending value(s) in each record. You can edit the Excel
spreadsheet to what the values "should" be, or you can change your table
field sizes to accommodate the new records. Then try to paste the Excel
table into your Access table again.
Or, if the problem isn't immediately obvious, or if you have lots of
records, you can save the Excel spreadsheet as a *.CSV file and import the
file into Access with an import specification that you've created to match
your Access table design. Access will again give you an error message about
the import not being successful, then it will create a table with the same
name as the table that you tried to import into, but with the "_ImportErrors"
appended to the end of the name. When you open this table, you'll see the
error, field name and row of each record that caused an error during the
import. Either edit the spreadsheet or alter your table as described above,
then paste the Excel table into the Access table again.
For what it's worth, can you create a form based upon the query that
identifies those "certain records" that are currently exported to Excel, only
to be reimported after the data updates? That way, your data focal can open
your database, make the data changes directly, and then you won't have to
worry about exporting and importing. I don't know your circumstances. Maybe
you have sensitive data in the database, and you can't allow the user to
access it freely (in which case, you probably shouldn't be using Access,
since it isn't all that secure). Or you don't want this person to "fiddle"
with your database. However, if either of these reasons is the case,
creating a separate Access database file for this user with tables linked to
the original database (for the previously-mentioned editing form) will allow
data isolation, as well as data validation upon input. The latter feature is
something that your Excel spreadsheet is lacking (unless you want to do some
programming).
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
- - -
When you see correct answers to your question, please sign in to Microsoft's
Online Community and mark these posts, so that all may benefit by filtering
