By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 614 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

no warnings when using transferspreadsheet or text

P: 3
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:
Expand|Select|Wrap|Line Numbers
  1.      DoCmd.SetWarnings True
  3.     If Right(importFile, 4) = ".xls" Then
  4.         DoCmd.TransferSpreadsheet acImport, , tblName, stSave, True
  5.     Else
  6.         DoCmd.TransferText acImportDelim, , tblName, stSave, True
  7.     End If
Thank you.
Dec 15 '08 #1
Share this Question
Share on Google+
4 Replies

Expert 2.5K+
P: 3,072
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 :-(
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.

Dec 16 '08 #2

P: 3
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?
Dec 16 '08 #3

Expert 2.5K+
P: 3,072
Try to create a new table linked to the excel sheet.
Then use an Append query to load the data.

Dec 16 '08 #4

Expert Mod 15k+
P: 31,419
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!
Dec 21 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.