Solved

Problem with data types during VBA import

Posted on 2014-11-13
10
366 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
  • 6
  • 4
10 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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
 
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 119

Expert Comment

by:Rey Obrero
ID: 40441129
did you save the excel file as .CSV?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 119

Expert Comment

by:Rey Obrero
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now