Solved

Problem with data types during VBA import

Posted on 2014-11-13
10
357 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

707 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

17 Experts available now in Live!

Get 1:1 Help Now