Access VBA error 3349 -

[jessss]

[Solved] - Access VBA error 3349

Hi, I am getting this error when running a macro I have which imports a CSV into a Table and the majority of the time it works however sometimes I get the VBA error 3349 "you cannot record changes because a value you entered violates the settings defined for this table or list".


So far the only way around this issue is to split the file into smaller batches and import them.


The code I use is:

'Import the file
Do While Len(strFile) > 0
      strPathFile = strPath & strFile
      DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames
      'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            strTable, strPathFile, blnHasFieldNames
      strFile = Dir()
Loop

This issue is very confusing as it only happens every other day and it will import the data when its split up, so I am unsure of whether it is a data issue.


Has anyone else had this issue? or know why this might be happening?


Thanks,


Jess

 

[The_Doc_Man]

When you perform the import, have you been able to verify that all of the intended records make it into the required table when you split them?

Frequently when you have import errors, Access will create an "Import errors" table that tells you something about the records that don't make it. Have you seen such a table? If so, look into it to figure out which record causes the big import to barf.

 

[jessss]

I have checked the files before they are split up and they i can never see anything wrong with the data that would cause errors going into the table.


I never see a table called Import errors, if it did it would be wonderful as I could get a better idea of whats causing the problem. However no table seems to be created just a message saying error 3349 "you cannot record changes because a value you entered violates the settings defined for this table or list".

 

[The_Doc_Man]

i can never see anything wrong with the data that would cause errors going into the table

Neither can I most of the time. Has to do with the cold, hard fact that our viewpoint is colored by our own expectations. It's called "being human" and is not a cause for alarm, just a cause of frustration.

What you care about more is that Access doesn't give you an Import_Errors table or any other such named table. Out of curiosity, how many rows are we talking about in a non-split spreadsheet? And how many rows (on average) are in the split sheets?

What I find more telling is that the error isn't a key violation, it is a constraint violation. Check all of the fields in your target table for validation rules because that is where you are tripping on something.

 

[jleach]

As a related side note, I always import into a temp table that's free of any validation rules, foreign keys and usually all text-based (even if they are numbers and dates coming in). This lets me get the data into a table, then clean it up to my heart's desire there.

Importing has always been a troublesome operation, and I find it works best if you can lighten the actual import process itself to be as dumb as possible.

 

[GohDiamond]

Open your csv file with Excel

1. Check for Duplicate IDs in Key field if your table Key field does not allow duplicates
2. Check for String Length in the original that exceeds the set Field Size in the Table
3. Check for Date entries that do not match the Date Format in the table
4. Check for Null value in field for which your Table field does not allow Nulls
5. Check for number of fields in the source matching the number of fields in the table
6. DELETE any Used but Blank Columns from the source file to the right of the last populated field
7. DELETE any used but blank rows from the source file below the last record in the file

These checks can be programmed...

The data is likely dirty somewhere, especially when it is extracted from a source where freeform text is allowed in the fields

Cheers!
Goh

 

[The_Doc_Man]

GohDiamond, your #1 suggestion would give a different error (key violation, not settings violation). However, the other suggestions are eminently possible.

Which isn't meant to say that your advice was wrong in general.

 

[jessss]

Thank you for all your help. I believe to have found a solution. The issue was being opened in Excel before being imported so the columns were being converted to different data types by excel. We have stopped opening it in Excel first and it appears to have solved the issue. But thank you for everyones help

 

[The_Doc_Man]

Ah, that does happen. Might have to do with a setting in Excel relating to auto-compute vs. waiting to be TOLD to compute. And of course the default is "Auto."

The other likely cause is that some of your data in a given column is inconsistent within the first 20 (or so) rows, because Access "Transportxxxx" code, if left to itself, will "guess" at the correct column format based on those first several rows. If other formats show up later in that column, the choice has already been made.

Which is why Jack's suggestion is good. If you import to a table first using a temporary table as intermediary, then you can perform an "INSERT INTO ... FROM temporarytable..." and get better error analysis. Sometimes you can, anyway.