Table Column Format/Input Mask

[mkc80]

I have problem loading data from an excel file in to a table. I usually import the excel file in to a table via VBA script (because there are some changes I need to do before importing).
It was working fine until this week when the data in a cell for a particular column in the excel file changed from being 3 letters to 4 letters. The column in the table is defined as text.
The data in this column usually looks like 001, 002, 003 etc but one of the cells in the excel file has data as P001. So now I get an error Runtime Error 3349 even though I have defined the column as text. When I change the data P001 to 004 similar to the other data, it works just fine. Is there a format or Input Mask I can set so that the data are correctly loaded in to the table?
Or am I missing something?

[ssanfu]

Open the table in design view and click on the field.
Is the field data type TEXT?
Check the field size - is it set to 3? If the field size is 3, change it to 5 or greater...

[mkc80]

Yes, the field type is TEXT but No, the field size is set to 255....

[ssanfu]

So it must be something in the script... Are you using an Import specification in the script?

[mkc80]

Yes,
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblCustomer", "C:\Downloads\CurrentFile.xlsx", True, "Customer!"
and this is where the execution stops and gives me a run time error 3349 "you cannot record your changes because a value you entered violates the settings defined for this table or list. Correct the error and try again"

[msoffice.vidyarthi]

could you attach both the excel and db files for analyzing purpose

[ssanfu]

The only thing I can see is the "!" in the last argument, "Customer!". That creates an invalid name.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblCustomer", "C:\Downloads\CurrentFile.xlsx", True, "Customer!"

[qa12dx]

haven't done this in a while, but why dont you save the excel file as a delimited text file. as long as the col headings and coll count are the same, i think it should work. also i would link the text file instead of importing it. use a append query in ur db to put into the table u want. this way all u would do in replace the text file and all else should run.