Solved

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

Posted on 2015-02-06
19
131 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
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.

 

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 120

Expert Comment

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

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 120

Assisted Solution

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

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)

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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.

770 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