Solved

Problem with data types during VBA import

Posted on 2014-11-13
10
413 Views
Last Modified: 2014-11-13
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?
0
Comment
Question by:dougf1r
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40440909
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
 
LVL 1

Author Comment

by:dougf1r
ID: 40440927
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40441005
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 1

Author Comment

by:dougf1r
ID: 40441077
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40441129
did you save the excel file as .CSV?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40441132
also,
try importing the .CSV file into a non existing temp table

DoCmd.TransferText acImportDelim, "ImportSpecificationName", "tempImport", "C:\Import_File.txt", True
0
 
LVL 1

Author Comment

by:dougf1r
ID: 40441155
Yes, I saved as a .CSV and am using only using the import wizard at this point to get the import specification.
0
 
LVL 1

Author Comment

by:dougf1r
ID: 40441243
I figured out why the errors were occurring. Too long to explain. Anyway, I am working on your import specification suggestion...
0
 
LVL 1

Author Comment

by:dougf1r
ID: 40441263
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
 
LVL 1

Author Comment

by:dougf1r
ID: 40441474
All set now. Thanks Rey.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Email question 19 86
GA Ribbon creator 9 55
Why can't I change data in my query? 3 33
Multiple tables for a tabbed form 2 24
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question