Solved

Excel 2007 Data Connection problem

Posted on 2011-09-14
5
331 Views
Last Modified: 2012-05-12
I have an end user in Korea who is encountering an error when they execute a QueryTable Refresh.   They have the same Read/Write access to the directory locations that I have.  When I run the refresh, it works correctly.  When they run the refresh they get an error 1004.  

The Query is on Worksheet, "Tbl_HR+" and the data connection is to an Access db table called "Tbl_HR".   The connection string and Connection Properties box is listed below.   The data is updated from a VBA procedure and the failing line of code is:

    Sheets(wkShtRateTbl).Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False

The full VBA procedure is listed below.  The Procedure name is "Read_HR_Table()" and it is contained in the Module  "MOD_RefreshRateTbl"

I am in the central US and my user is in Korea which is 14 hours ahead.  I will not be able to work with them again until later this evening my time.     I would appreciate any suggestions on things to look for when I can work with them again later tonight.

Excel 2007 and Access 2007

Thanks,
Jerry
 1004 Error Connection Properties Dialog
Connection String:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;
Data Source=N:\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

Option Explicit

Sub Read_HR_Table()
'--------------------------------------------------
    On Error GoTo errHandler

    Dim wkShtControl As String
    Dim wkShtRateTbl As String

    errFlag = False
    Call TurnOffSettings

    'Initialize Variables
    wkShtControl = "Control+"
    wkShtRateTbl = "Tbl_HR+"

    Call SetStatusBar("Importing HR Data Table Information")
    Sheets(wkShtRateTbl).Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False

    With Sheets(wkShtControl).Range("TS_RT")
        .Value = "HR Data Last Updated - " & Format(Now(), "MM/DD/YYYY H:MM AM/PM")
        .HorizontalAlignment = xlRight
        .MergeCells = True
    End With

finished:

    If Not errFlag Then MsgBox "Retrieved Kiosk HR Data.", vbOKOnly + vbInformation, "Successfully Updated"

    Call TurnOnSettings
    Call ResetStatusBar
    Exit Sub

errHandler:
    Call error_Handler(Err.Number, Err.Description, "MOD_RefreshRateTbl", "ReadGoalData")
    GoTo finished

End Sub

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
  • 4
5 Comments
 
LVL 16

Accepted Solution

by:
carsRST earned 500 total points
ID: 36537970
Are they able to connect to Access via the path below?


N:\Human Resources\International\Korea\Kiosk\KRW-Kiosk.accdb;


Is their "N" drive the same as you have it?

You might try to use a UNC path instead.
0
 
LVL 16

Author Comment

by:Jerry Paladino
ID: 36538300
carsRST:

That is probably the issue.   I am not sure what drive letter they map to for that particular UNC path.   It is supposed to be N:\ but I have not confirmed that.   I think I will convert all the connections to the UNC path just to be sure.

Thank you for the suggestion.  I will leave this open until I can speak with them later this evening and then close it out or provide additional information.

Thanks,
Jerry
0
 
LVL 16

Author Comment

by:Jerry Paladino
ID: 36542328
I've requested that this question be closed as follows:

Accepted answer: 0 points for ProdOps's comment http:/Q_27308001.html#36538300

for the following reason:

carsRST,<br /><br />Thank you for your help with this.
0
 
LVL 16

Author Comment

by:Jerry Paladino
ID: 36542329
I selected the wrong solution post.  I am trying to cancel this so I can select carsRST
's solution.
0
 
LVL 16

Author Closing Comment

by:Jerry Paladino
ID: 36542335
carsRST,

Thank you for your help with this!
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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 Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 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