Solved

Problem with data types during VBA import

Posted on 2014-11-13
10
388 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 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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 …

807 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