Solved

Excel 2007 Data Connection Error (1004)

Posted on 2011-09-15
4
841 Views
Last Modified: 2012-05-12
I have an end user in Korea who is encountering an error when they execute a ListObject.QueryTable.Refresh against an Access Db Query.   They have Read/Write access to the directory location where the Access Db resides.   There are three worksheets with ListObject.QueryTable.Refresh updates in the workbook.  Two are linked to Access Queries and one is linked to an Access Table (that is a linked Excel file in the Access Db).  

The Korean user can execute the Refresh against the Access Table successfully but receives the following error message when trying to run the Refresh on either of the Access Queries:

Error 1004- Query did not run or a database table cannot be opened.
Check the database server or contact your database administrator.
Check you can use an external database, the database has been moved or reorganization, and then try the operation again

Since it runs on multiple PCs in the US and not in Korea I assume it has something to do with Language or Locale but I have not been able to solve this and would appreciate any suggestions.

Excel 2007 and Access 2007

Thanks,
Jerry
 
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;
Data Source=\\rshbgfs1\business\Human Resources\International\Korea\Kiosk\KRW-Kiosk.accdb;
Mode=Read;Extended Properties="";Jet OLEDB:System database="";
Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;
Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;
Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";
Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;
Jet OLEDB:Don't Copy Locale on Compact=False;
Jet OLEDB:Compact Without Replica Repair=False;
Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False

Open in new window

0
Comment
Question by:Jerry Paladino
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 9

Expert Comment

by:suvmitra
ID: 36565717
I think...for the data source ... user should first map the shared drive with a letter eg I:\SHARE\rshbgfs1\business\


...update the path and re-try.
0
 
LVL 16

Author Comment

by:Jerry Paladino
ID: 36565983
suvmitra,

Thank you for your reply.   I do not understand why I would map to a drive letter.  It is currently mapped to the full UNC path.   As I mentioned above, they can execute a simple QueryTable.Refresh to a table in the Access Db.   If the mapping was incorrect they would not be able to do that.  

Thank You,
ProdOps
0
 
LVL 24

Accepted Solution

by:
broomee9 earned 500 total points
ID: 36568374
Hi Jerry,

Check the version of Excel and Access being used, are they both 2007 SP2?  It may be an issue with compatibility between the two applications.

Thanks,
Tracy
0
 
LVL 16

Author Closing Comment

by:Jerry Paladino
ID: 36568600
Tracy,

Great catch!  Thank you.  
Yes, the Korean PC had an SP1 version of Excel and the newer Access file was unable to read the XLSB file it was creating.   We did two things…  Updated the Excel software and also change the file type from XLSB to XLSX.   Everything is working now.

Thanks,
Jerry
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

705 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