no warnings when using transferspreadsheet or text

[ambmil]

I am using transfertext and transferspreadsheet in VBA to import an excel file (.csv or .xls) into access 2003.

Some of the fields are failing (I am getting an import error file), but I am not getting any warning message. I have explicitly turned on the warnings, but it didn't make a difference. If I manually import the table, the warnings appear ("Microsoft Access was unable to append all the data to the table. The contents of fields in X record(s) were deleted, and Y record(s) were lost due to key violations . . . ")

Does anyone have any ideas how to get these warnings to appear?

Note that the file (stSave) is importing, with the exception of a few fields.

Here is my code:

     DoCmd.SetWarnings True
 
    If Right(importFile, 4) = ".xls" Then
        DoCmd.TransferSpreadsheet acImport, , tblName, stSave, True
    Else
        DoCmd.TransferText acImportDelim, , tblName, stSave, True
    End If
 

Thank you.

[nico5038]

In general this is cause by two main "problems"
1) The data type of the column is set automatically, but Access uses just some 8 rows. So a "change" further down causes a date or number to fail :-(
Solution:
Repeat the first line with the column names. This forces Access to import everything as text and (after deleting the first row) you can set the data type properly.
2) There are fields "mandatory" containing no data. Set the fields of a table to accept Null values by allowing zero length.

Nic;o)

[ambmil]

This process will be used by many people, and the intention is that there not be any manipulation of the excel file (which comes from a third party). So, manually adding another row is out.

I am importing into a pre-existing table, so the field types are certainly preset.

Again, if I bring in the file manually to the existing table (Tables -> new -> import -> existing table), the warnings DO appear.

None of the fields that are failing are mandatory, they all allow zero length fields.

Any other ideas?

[nico5038]

Try to create a new table linked to the excel sheet.
Then use an Append query to load the data.

Nic;o)

[NeoPa]

Always use SpecificationName where possible. This stops Access from trying to determine the type of the field from the data.

When doing a transfer manually, you can go into advanced mode, where you can load and save Import/Export Specifications.

Welcome to Bytes!