Solved

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

Posted on 2015-02-06
19
116 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
Comment Utility
upload a copy of the excel file.
0
 

Author Comment

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

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Also be sure your Office and Windows installations are fully up to date.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
@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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Jay Williams
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thanks
0
 

Author Comment

by:Jay Williams
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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.
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…

762 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

12 Experts available now in Live!

Get 1:1 Help Now