Is Access bogus, or is it me?

Giganews Newsgroups
Subject: Is Access bogus, or is it me?
Posted by:  DFS (nospam@dfs_.com)
Date: Thu, 10 Aug 2006

I have a job to automatically import Excel data and post to database tables,
via a point-click interface.  Choose-file-and-it-does-the-rest kind of
thing.

Cient stores data in columns in his sheet (I told them it should be in rows,
but that's another issue).  Sometimes the sheet gets new columns added.  At
that point, Access/import spec won't recognize the new columns.

DoCmd.TransferText acImportDelim, "importSpec", "TABLE_NAME", "SHEET NAME",
True

* can't store a link to the Excel file and use it, because the sheet has
text data in columns which Access recognizes as dates, and it shows #Num and
gives Numeric Overflow errors when trying to query/post the data.

* can't save sheet as .csv and import it, or store a link to the .csv,
because some of the data has quotes around names, and it gets split up
wrong.  Plus, when the user adds columns to the sheet and saves as .csv,
Access won't see the new columns because the import spec won't recognize
them.

* can't save sheet as .txt (tab-delimited) and import it, or store a link to
the .txt, because when the user adds columns to the sheet and saves as .txt,
Access won't see the new columns because the import spec won't recognize
them.

I can't figure out how to do this - in fact I think it can't be done.

Or am I missing something?

Replies