Path Issues Importing Access Data into Excel

Hello All,
I’m having some path issues and was wondering if anyone could help.
I have created an Access file called “calcdata.mdb” and an Excel file called “calcdata.xlsm”. I ran the Macro Recorder and was able to create a Macro that will go and get the information that I need from Access. No problems with that at all. Where I encounter problems is when I move these two files from one folder to another. These files are job specific and need to stay with the rest of the job files.
Then, when the Access file is updated, the “Refresh” does not work. It tells me that it can’t find the file.
What I would like to know is what do I have to do to the Macro that will make it look only in the folder that the Excel file is located? (Both the Access file and the Excel file are located in the same folder regardless of what the rest of the tree below it is.)
The original files (blank templates) are located in H:\Engineering Dept\Engineering Documents\Jobs in process\Job Setup\4. Engineering Documents\Calculations\Calculation Storage. Whan a new job enters the department, the “Job Setup” part gets changed to the Job Number. The real “calcdata.mdb” is created by another program and then I want to open the “calcdata.xlsm” and run the macro and have it find the correct Access file.
Here is the code generated by the Macro Recorder. Could someone please tell me what I need to add to make it look in the right place and what I need to change on the “Data Source” & “SourceDataFile =” lines?


Forum.txt

Well, now that I know how to do it, here is the code...
[code]
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+z
'
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=H:\Engineering Dept\Engineering Documents\JOBS IN PROCES" _
, _
"S\Job Setup\4. Engineering Documents\Calculations\Calculation Storage\CalcData.mdb;Mode=Share Deny Wr" _
, _
"ite;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engi" _
, _
"ne Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:N" _
, _
"ew Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on C" _
, _
"ompact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:B" _
, "ypass UserInfo Validation=False"), Destination:=Range("Basering!$A$1")). _
QueryTable
.CommandType = xlCmdTable
.CommandText = Array("Baserings")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"H:\Engineering Dept\Engineering Documents\JOBS IN PROCESS\Job Setup\4. Engineering Documents\Calculations\Calculation Storage\CalcData.mdb"
.ListObject.DisplayName = "Table_CalcData1"
.Refresh BackgroundQuery:=False
End With
End Sub
[code]

This turned out to be a lot simpler than I expected. As long as both files reside in the same folder, all that needed done was to remove the path information from both the "Data Source" and "SourceDataFile" lines.