[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

Excel 2007 Data Connection problem

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
Jerry Paladino
Asked:
Jerry Paladino
  • 4
1 Solution
 
carsRSTCommented:
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
 
Jerry PaladinoAuthor Commented:
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
 
Jerry PaladinoAuthor Commented:
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
 
Jerry PaladinoAuthor Commented:
I selected the wrong solution post.  I am trying to cancel this so I can select carsRST
's solution.
0
 
Jerry PaladinoAuthor Commented:
carsRST,

Thank you for your help with this!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now