> What if you create a few import specs and then use those to import your
data? I think they're listed as IMEX in the system objects table.
then you could just create a simple form that does most of the work for
you and reuses the specs you've created.
Well, yes, If I import a text file I can click the Advanced button in the
Import Text Wizard dialog to define and save import specs. Thanks for
pointing this out (I didn't realize I could do that). But the problem is
mainly with Excel spreadsheets. AFAIK, there is no way to define import
specs for Excel (using the Import Spreadsheet Wizard).
The Excel files in question are actually built from text files. But the
text files vary in format and need massaging into Excel before they can be
imported. So the spreadsheets are in good shape and have the proper field
names as header rows. It's just the data types that's the problem. One
possible solution might be to Link to the file (rather than Import), then
run an Insert query to dump everything from the linked table into an Access
table. This seems to convert the data types as needed.
Any particular reason you need all the data in separate tables?
Because the tables are normalized, it's a challenge to import data. The
Entity_ID (Autonumber pk) must first be created in the main table, then
stuff like Address, Phone Number and Transactions can be inserted in their
corresponding tables with the Entity_ID as a fk. But the flat file with the
data to be imported has everything in one row - Entity, Address,
Transaction, etc. - so I create a recordset from the imported file and
insert one record at a time saving the MAX(Entity_ID) back to the imported
table after each insert so additional queries can be run against it to
populate the other tables. So if I have a comma delimited text file I could
set up some import specs, but with an Excel spreadsheet I have type
conversion problems.
