Re: Make linked table have a relative path

Here is how I solved that type of problem for our site.(This example is
doing more than you need, but gives you the idea of how it might be
handled.)..................

I have created a control file within the app defined as:

TimeAnalysisControlTable
ControlID text
ControlInfo text

I place in that table such things as Report Directory, BaseDirectory,
etc anything that is static but is subject to change on occasion. This
way no path is hard coded anywhere in the application.

For instance
ControlID ReportDirectory
ControlInfo C:\MYApp Directory\

I have the following query defined

SELECT TimeAnalysisControlTable.ControlInfo,
TimeAnalysisControlTable.ControlID
FROM TimeAnalysisControlTable
WHERE (((TimeAnalysisControlTable.ControlID)="ReportDirectory"));

and it is called "Query - Get Report Directory"

========================================

In my vba module I have a createanalysis function: it deletes the
output report if it happens to have already been run so that it can be
replaced by a more recent version. I then copy a master that has some
special formating etc. and then export into that copy.

function CreateAnalysis()

MasterDir = DLookup("[ControlInfo]", "Query - Get Masters
Directory")
ReportDir = DLookup("[ControlInfo]", "Query - Get Report
Directory")

ReportFileName = "Time Analysis Report " & Year(EndDate) &
Format(Month(EndDate), "00") & Format(Day(EndDate), "00") & ".xls"



With Application.FileSearch
.NewSearch
.LookIn = ReportDir
.SearchSubFolders = False
.Filename = ReportFileName

If .Execute() = 1 Then
Let match = ""
VBA.FileSystem.Kill ReportDir & ReportFileName
End If
End With

VBA.FileCopy MasterDir & "Time Analysis Ding Master.xls", ReportDir
& ReportFileName

ReportFileName = ReportDir & ReportFileName

DoCmd.TransferSpread*** _
TransferType:=acExport, _
SpreadsheetType:=acSpreadsheetTypeExcel9, _
TableName:="Query - All Employee Ding Only", _
Filename:=ReportFileName, _
HasFieldNames:=True, _
Range:="ExportData"

function end
===========================================
That is the general gist of the function. I does other things but that
is how I use the dlookup and the control file.

I started out a long time ago having a single record file with many
fields, but whenever I had to add another field it was a bear. Then I
saw a reference to this type of structure in in one of the groups and
have converted over to using it.

Ron

.

Quantcast