Importing excel file and importerrors message because of data type

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I have a database that imports electronic invoices from excel format. A few
columns have both numbers and text in them, so when i import the file it
gives me an import error because of conversion failure. I can import it
because I am the administrator for the DB, but my users cant because they
dont have "make new" table rights in the database, so they cannot import the
file. Anyone have any ideas?
 
One approach:

-Create a temp table "shell" to import the data into, where the problematic
fields are preset to text. This should prevent import errors.
-Create a saved query to append data from temp table to "real" table. This
query can coerce fields to numeric values, provide substitutes for Null,
etc., as needed.

Add code to that will execute the following sequence on demand:
- Delete all records in temp table
- Import data from excel to temp table
- Run saved append query
- (optional redundancy) delete records in temp table

No import errors, no "make table" rights required.

HTH,
 
Access doesn't always match Excel columns to the correct datatype. If you
select the column and assign a data format in Excel that sometimes helps
Access make the correct choices.
Paul Shapiro
 
Back
Top