Help needed with TransferSpreadsheet method

[WDR]

I am trying to use the DoCmd.TransferSpreadsheet method to import records in
an Excel 2000 spreadsheet file (has one sheet) but it is not working and
would appreciate your expertise. Error msg I'm getting indicates either key
violations, or data types/field sizes not matching, or a PK violation. Thank
You. More details below.

The import process I want to accomplish will actual APPEND the records to an
existing Access 2000 table. The table has no PK and doesn’t need one. I
desire to have a few of the fields indexed but took them off in just trying
to get the import/append to work. Field types my Access table has are Date,
Text, Memo, Yes/No and Number(the number fields used to be Text – all they
store are years). Other variations I’ve attempted are:
- I’ve tried using and not using field names (w/corresponding TRUE/FALSE)
- I’ve tried importing TRUE/FALSE fields into Yes/No Access fields but have
since changed the True/False on the Excel side to “Yâ€/â€N†and therefore
changed those Access field types to Text.
- I’ve tried formatting the Excel columns to Text that correspond to Text on
the Access side. I did this for all data types.

I keep hearing about linking, but in my situation I'm not sure that will
work unless the linking can be handled within the process I’ve pasted below
(I don’t want the end-user to have to manually do anything in this process).
I have multi-Excel users emailing a single sheet file with records which I
want to import into a history table in Access. In case you’re wondering, all
the data validation occurs on the Excel side.

Additional info is that I can import into a new table which is not my desire
PLUS in the new table there is an additional field with a data type of
Autonumber as the PK. I imported via the File/GetExternal Data menu.


*****- THIS IS THE IMPORT PROCESS THAT DOES NOT WORK -********
Sub ImportSpreadsheetFiles()
Dim strFile, strFolder, strBackupFolder, strBkupFolderFile,
strFolderFile As String

strFolder = "C:\Documents and Settings\" + Environ("UserName") + "\My
Documents\ImportFilesForAccess\"
strBackupFolder = strFolder & "FilesAfterImport\"

strFile = Dir$(strFolder & "*.xls")

Do While Len(strFile) > 0
DoCmd.TransferSpreadsheet acImport, 8, "PastJobs", strFolder &
strFile, True

Name strFolder & strFile As strBackupFolder & strFile

strFile = Dir$(strFolder & "*.xls")
Loop
End Sub

 

[Allen Browne]

Spreadsheets are different kind of beasts than databases. They are less
rigid, e.g. there is nothing to stop someone typing a word such as Unknown
into a column of dates, so when you try to import them into your database
there is always a chance that the data will not import correctly.

One solution is to import into a temporary table, where all fields are just
Text type. Then programmatically check the validity of the data: correct
type, suitable range, required fields present, validation rules met, no
duplication of values in the new data or between new and existing data, and
so on. Highlight the problems to the user, and ask them to fix up these
records. Only when all data is correctly validated do you then append the
records to the real data table in Access.

So, you are about to program an interface for the user that has these steps:
1. Select the file to import.
Your code first deletes any records in the temp table, and then you pop up
the File Open dialog asking the user to choose the file to import. When they
select the file, you import into the temp table, and verify that the data
does like what you expect (the user did not choose an irrelevant file) and
has not been imported already . Then run the checks illustrated above, and
pop up the form explained below.

2. Solve the issues
Pop up a form listing two kinds of issues:
- critical: the user must solve these issues before the import can continue;
- warnings: the user might want to consider these issues too.
The interface allows the user to massage the problem data in the temp table,
supplying missing values, correcting bad dates, removing duplicates and so
on. When they have addressed all the critical issues, you enable the button
for step 3.

3. Go.
Typically, you create a transaction (so it can be rolled back if necessary),
and assign a new batch number (so you can identify which records resulted
from which import), and execute an Append query statement to copy the data
to the real table. The append query statement will need to perform the
correct type conversion to ensure the data is understood correctly. If
anything goes wrong and the import fails, your code at step 2 failed to deal
with all cases, so you need to develop it further. If it succeeds, pop up a
final confirmation dialog showing the number of records being imported and
get final confirmation to commit (or rollback) the transaction.

That is the only safe way to interface the import. If you prefer it is
possible to link the spreadsheet instead of importing to a temp table. To
link, go to:
File | Get External | Link Tables.
Personally, I find this less flexible and robust than using the temp table.

 

[WDR]

Thanks Allen for your good advice. Do you have any similar examples of the
steps you suggested? I did go to your website but didn't see anything.
Thanks,
Warren

 

[Allen Browne]

No, there is not a published example of this on the site.

A generic example is not really practicable, because of the specifics of the
data types, integrity rules, relationships to existing data, and so on.