Problem with data types during VBA import

I am using this code to import and append data to an existing table in Access 2010:

Private Sub ImportCarcassA_Click()
If IsNull(Me.txtFileName) Or Len(Me.txtFileName & "") = 0 Then
    MsgBox "Please select the Excel file"
    Me.ImportCarcassA.SetFocus
    Exit Sub
End If

DoCmd.TransferSpreadsheet acImport, 10, "data_Carcass", Me.txtFileName, True

Me.txtFileName.SetFocus
Me.ImportCarcassA.Enabled = False

End Sub

Open in new window


The problem is:

The Access table ("data_Carcass") contains some fields formatted as text. The input file I am attempting to append contains both integer and text values in these fields. When I attempt to import, I get a table of errors for all text values in these fields. It is as if Access is recognizing these fields as integer and attempting to append as integer rather than text. It is probably because the first several lines of the input file do not contain any text values.

How can I modify my VBA code to ensure these fields are imported as text regardless of the data type that the first several lines of the input file contains?
LVL 1
dougf1rAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you can do this in two ways
1. save the excel file as a .CSV file
    a. then create an import specification
    b. you can then use
        docmd.transfertext acimportdelim, "ImportSpec", "tableName", fileName, true

2. read the excel file row by row, column by column and append to table as you go along
0
dougf1rAuthor Commented:
Option 1 seems best for me to implement.

Can you please elaborate on creating an import specification? Would this be a blank table with fields of my desired data types?

Edit: You probably mean do this: https://support.office.com/en-gb/article/Save-the-details-of-an-import-or-export-operation-as-a-specification-6b94e183-2b10-4333-a31a-001fe75321b5
0
Rey Obrero (Capricorn1)Commented:
no, that is not the one..

To create the Import Specification
1) Click on external data > text file which then opens another window called "Get external data - Text file"
2) Use radio button to select "Import the source data into a new table in the current database"
3) specify the source of the file using the browse procedure then click OK
4) Choose radio button to select delimited format and then click next
5) this window allows you to choose delimiter and text qualifier and if first row contains names - click next
6) This window allows you to type the name of the field in the Field Name column, choose data type, and if you want field indexed - also can choose to skip field - then click next
7) This window allows you to add primary key or designate field as such - click next

8) Click on ADVANCED button

9. in the import specification window
type the name of the field in the Field Name column
(here you can use the field names of the destination table, specify data type,
check the box Skip if you do not want to import the column)


10 click save as, give the specification a name <-- this is the specification name that you will use in the command line below


DoCmd.TransferText acImportDelim, "ImportSpecificationName", "TableName", "C:\Import_File.txt", True
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dougf1rAuthor Commented:
Ok. I first tried importing just to be sure I don't get any errors and I am getting a type conversion error for a few cells in two of the fields formatted as double. I checked these cells in the input file and see no reason why these values cause a type conversion error. I re-entered the values and still get the error on these cells.

This is a somewhat unrelated question, but I have no idea why this is occurring and it would be great if you had an idea.
0
Rey Obrero (Capricorn1)Commented:
did you save the excel file as .CSV?
0
Rey Obrero (Capricorn1)Commented:
also,
try importing the .CSV file into a non existing temp table

DoCmd.TransferText acImportDelim, "ImportSpecificationName", "tempImport", "C:\Import_File.txt", True
0
dougf1rAuthor Commented:
Yes, I saved as a .CSV and am using only using the import wizard at this point to get the import specification.
0
dougf1rAuthor Commented:
I figured out why the errors were occurring. Too long to explain. Anyway, I am working on your import specification suggestion...
0
dougf1rAuthor Commented:
I just realized that I am going to need code to Browse for a .CSV file if I make this change. Currently, my "Browse..." button is set up to browse for an .xlsx file.

Should I post another question for this, or is this something that can provided here?
0
dougf1rAuthor Commented:
All set now. Thanks Rey.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.