CODEWITHSUNDEEP
excelms-access
MikeWojcik
MikeWojcik

Reputation: 15

Trying to load excel file into ms-access where a text field contains the @ symbol

First Post here...

My access app will have a monthly process where the user must load excel files containing account balances. One field occasionally contains the @ symbol as part of the Account Number - this field is a short text field in the database table.

Access flags this a violation. Is there anyway for access to support this character? if not is there any way to automatically remove the character or replace it with another character?

The following is what I am using to load the excel file into access...

DoCmd.TransferSpreadsheet acImport, 9, "TempAccountBalances", [TempVars]![varTBFileName], True

The actual excel file contains 4182@ in the Account number field in several cases.

The file imports properly for all excel rows except those that contain the @ character in the Account# field. Each of those rows generate a row in a newly created table called xxx_ImportErrors with a TypeConversionFailure in the Error Field. Access auto created that table on it own

Update... A little more information... When I import the file using the Access Ribbon - External Data - New Data Source - Excel File It imports the Account# fields without error when importing into a new table

But when I import into an existing table i get the same failed rows as the DoCmd above. ALL the Account# field properties in the new table are identical to the existing TempAccountTable used above ???

Thanks! Mike

Upvotes: 0

Views: 147

Answers (1)

MikeWojcik
MikeWojcik

Reputation: 15

OK - so this was caused by Access determining the field type based on the first X rows of the imported excel file. So I need to use an Import Specification file to force the field type to what I need. That caused me another issue which I'll post separately.

Upvotes: 0

Related Questions