Importing From Excel

[Guest]

My Access application imports a fairly large amount of its information from
Microsoft Excel. Two tables in my database will need to be updated with
large amounts of data from Excel, and data already in the tables cannot be
harmed. One of these tables may be updated fairly regularly, and the import
process must be quick, easy, and reliable.

Now, as with most databases, both of these tables contain unique ID's and
foreign keys to parent tables. The users who are inputting data for the
Excel files should not have to know this information, and in many cases,
cannot.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"tblActualCost", strActualImportPath, True

DoCmd.TransferSpreadsheet TableName:="tblActualCost",
FileName:=strActualImportPath, HasFieldNames:=True

I have tried these commands, but I sometimes wind up with errors complaining
that the autonumber field was left null. Even worse, these errors are then
saved as seperate tables, something that I simply CANNOT have happening in my
application. After all, how are users supposed to be able to go in and
delete junk tables Access has created after I've locked them out of doing so
with an MDE?

What can I do?

Dustin

 

[Jeff Boyce]

Dustin

It sounds like you might be trying to import Excel data directly into your
"permanent" Access tables. Since Access is a relational data base, and
Excel generally holds "flat" data (not its fault, that's how it's built), it
would be rare that you could directly place Excel-organized data in
well-normalized Access tables.

Instead, a common approach is to link to or import Excel data "as is", then
use a series of parsing queries to distribute the data to the proper tables.
It would be during this parsing process that you could handle the issues
with primary and foreign keys.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Dustin

I may have mis-stated my previous response.

You certainly CAN stuff Excel data into an Access table.

But you won't usually get good use of Access' features and functions unless
you feed it well-normalized data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Nurick]

Hi Dustin,

the import
process must be quick, easy, and reliable.

For this to be the case, you have to be able to assure the quality of
the data that's being imported, and it's not easy to do that if you're
using Excel to collect data from users.

By itself, Excel offers no protection against user actions such as
entering a text value in a cell that should be numeric, or accidentally
entering a foreign key value that does not exist in the other table, and
so on. If Excel is to be the front end, you'll need a combination of

1) sophisticated data validation, cell locking and protection in Excel

2) a multi-stage process for importing the data into Access: e.g.
(a) import to a temporary table
(b) run queries to validate the data (e.g. identify illegal value)
and eitehr correct it or report it to the operator
(c) finally append only good data to the permanent table(s).