Access VBA error 3349 - (1 Viewer)

jessss · Jun 13, 2018

[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:

Code:

'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 · Jun 13, 2018

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 · Jun 13, 2018

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 · Jun 13, 2018

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 · Jun 14, 2018

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 · Jun 14, 2018

Open your csv file with Excel

Check for Duplicate IDs in Key field if your table Key field does not allow duplicates
Check for String Length in the original that exceeds the set Field Size in the Table
Check for Date entries that do not match the Date Format in the table
Check for Null value in field for which your Table field does not allow Nulls
Check for number of fields in the source matching the number of fields in the table
DELETE any Used but Blank Columns from the source file to the right of the last populated field
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 · Jun 15, 2018

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 · Jul 23, 2018

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 · Jul 23, 2018

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.

Access VBA error 3349 - (1 Viewer)

jessss

Registered User.

The_Doc_Man

Immoderate Moderator

jessss

Registered User.

The_Doc_Man

Immoderate Moderator

jleach

Registered User.

GohDiamond

"Access- Imagineer that!"

The_Doc_Man

Immoderate Moderator

jessss

Registered User.

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread