Solved

Access run time error 2391 no field name 'yes' in your destination table.

Posted on 2015-02-06
19
119 Views
Last Modified: 2016-02-11
This is an automated import that has been working perfectly for a long time--now it isn't.
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ProcessSheets", sPath & xlFile, True, "IPP_Request!A5:AO5000"

Open in new window


Row 5 (top of the import range) contains the exact field names of the "ProcessSheets" table (so this stuff won't happen).  There is no "yes" value in row 5.  I searched the spreadsheet template and the only "yes" is in the header above the import range.  I can't figure out what's changed.  Any ideas?
0
Comment
Question by:Jay Williams
  • 9
  • 4
  • 3
  • +1
19 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40593838
upload a copy of the excel file.
0
 

Author Comment

by:Jay Williams
ID: 40593887
Here you go.
IPP-Request-Form.xlsm
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40593919
FWIW your code and spreadsheet work fine for me without error...

Perhaps now (perhaps because of an update of change in the Excel Frmat or Extension) Hidden rows (as your Row 5 is) are now ignored in the data range.
So now Row "5" will be the 5th "visible" row, this would make the new row 5, Row 6.

Try the code like this;
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ProcessSheets", sPath & xlFile, False, "IPP_Request!A5:AO5000"
...Or change the range to:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ProcessSheets", sPath & xlFile, True, "IPP_Request!A6:AO5000"
0
 

Author Comment

by:Jay Williams
ID: 40593949
Jeffrey, I had a similar thought and had already tried starting the range at A6 and got the same error, with a different value not found as a field name.  I changed the HasFieldNames argument to "False," and got "no field named F1."
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40594035
do a decompile of your access app
then do a compact and repair

open db and goto to the VBA editor
do a Debug > Compile
correct any errors raised

try again your import
0
 
LVL 84
ID: 40594173
Also be sure your Office and Windows installations are fully up to date.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40594248
@Rey and Scott
Were either of you able to replicate the issue?
I had no errors when I ran the exact code with the exact spreadsheet...
0
 

Author Comment

by:Jay Williams
ID: 40594250
Thanks Rey and Scott, I'll get after it, but don't hold your breath.  I tried to decompile, but this IT policy won't let me pass gas without permission.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 167 total points
ID: 40594305
Jay,
A5:AO5000"

Some observations...
1. The end of your data range seems to be Row 944
...but the resulting imported table has 1622 rows
2. When I do a Group By/Count on just the DrwngNddInBP3rnc field, I get 984 records on "No"

Perhaps it is reading the blank values as "Yes"...?,
...so if NO=0, ...then any other value will be interpreted as "Yes"?

Keep us posted.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Jay Williams
ID: 40594324
AHA!  That's what changed.  Management wanted us to set that column to a default "No."  That's right when the problems started.  I will keep you posted--on Monday!  Have a great weekend, and THANKS! :-)
0
 

Author Comment

by:Jay Williams
ID: 40598180
Well, I played all kinds of games with the import range and HasFieldNames argument.  Same deal. I get either an error saying the there is no field named "yes" or "F1."  Asking my help desk to restore earlier versions of the template and database that worked; asking for permission and assistance to decompile Access.  I have to pull out all the stops on this one.
0
 
LVL 84
ID: 40598252
asking for permission and assistance to decompile Access.
Can you create a desktop shortcut on your workstation? If so, you don't need permission or assistance, I would think. Just create a shortcut with this as the Target:

"full path to msaccess.exe" "full path to your db" /decompile

Run that shortcut, then open your app, go to the VBA Editor and click Debug - Compile. Fix any errors and continue doing that until the menu item is disabled. After doing all that, Compact the database.
0
 

Author Comment

by:Jay Williams
ID: 40598267
I thought I did that, Scott, but I didn't have both paths on the same line on the shortcut.  Is there any separator or delimiter in the combined pathname(s)?
0
 
LVL 84
ID: 40598286
There are no combined pathnames. There are two distinct pathnames, separated by a space and surrounded by double quotes, with the /decompile switch.
0
 

Author Comment

by:Jay Williams
ID: 40598290
thanks
0
 

Author Comment

by:Jay Williams
ID: 40598325
I ran "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" "G:\XE_ECMs\IPP Sharing Development\Processing.accdb"/decompile.  The file opened right up, but didn't appear to do anything more than that.  Is that what to expect?  I went to the module, compiled and then compacted.  Ran the process.  Same result.
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 166 total points
ID: 40598463
try this first, copy and paste

    DoCmd.TransferSpreadsheet acImport, 10, "ProcessSheets", sPath & xlFile, True, "IPP_Request!A5:AO5000"
                         
if you are still getting the error,
try importing the excel file to a NON existing table

       
    DoCmd.TransferSpreadsheet acImport, 10, "NEWProcessSheets", sPath & xlFile, True, "IPP_Request!A5:AO5000"
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 total points
ID: 40599025
he file opened right up, but didn't appear to do anything more than that.  Is that what to expect?
Yes, that's what Decompile does.
0
 

Author Comment

by:Jay Williams
ID: 40600486
I see where you're going, Rey.  The "NEWProcessSheets" import was successful--and showed my formatting conflicts.  I also need to reconcile my stated import range with the actual range on the sheet and play with the default values in the DrwngNddInBP3rnc column as Jeffrey suggested.  I think we're onto something.  Will update later today after testing.  Thanks!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

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…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

863 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

28 Experts available now in Live!

Get 1:1 Help Now