Relative Table Links

[CharlieT302]

I need to create a table link that looks for a file in the "My Documents" folder regardless of the workstation on which the dbase is running.

Can this be done?

 

[PWise]

The My Documents folder on workstation xxx?
or the current My Documents folder?

 

[CharlieT302]

Actually, I was originally thinking of the My Documents on the current workstation; which would be the workstation where the dbase is installed.

 

[formerTexan]

If you are looking for the current user's "My Documents", then the following functions will return the path in 32 bit Windows OS (not sure how Vista will respond) if the folder exists.

Private Declare Function SHGetFolderPath Lib "shell32.dll" Alias "SHGetFolderPathA" _
  (ByVal hwnd As Long, ByVal csidl As Long, ByVal hToken As Long, ByVal dwFlags As Long, _
  ByVal pszPath As String) As Long

Private Const CSIDL_PERSONAL = &H5
Private Const SHGFP_TYPE_CURRENT = &H0 'current value for user, verify it exists
Private Const SHGFP_TYPE_DEFAULT = &H1
Private Const MAX_LENGTH = 260

Private Const S_OK As Long = &H0
Private Const S_FALSE As Long = &H1
Private Const E_FAIL As Long = &H80004005
Private Const E_INVALIDARG As Long = &H80070057


Private Function fFolderPath() As String
Dim lRet As Long
Dim sPath As String * MAX_LENGTH
  lRet = SHGetFolderPath(0, CSIDL_PERSONAL, 0, SHGFP_TYPE_CURRENT, sPath)
  If lRet = S_OK Then
    fFolderPath = fCleanPath(sPath)
  Else
    MsgBox lRet
  End If
End Function

Private Function fCleanPath(s As String) As String
Dim i As Integer
  i = InStr(s, Chr$(0))
  fCleanPath = left$(s, i - 1)
End Function

Cheers, Bill

 

[PWise]

try this code to return the current user's folder

Dim oWSH
Dim strFolder
Set oWSH = CreateObject("WScript.Shell")
strFolder = oWSH.SpecialFolders("MyDocuments")

 

[CharlieT302]

Thanks guys,

Either of these codes; how do they link to the file say...a spreadsheet in the My Documents folder called: ClientData.xls?

By the way, am I correct in assuming that I perform the code in the Immediate Window or by attaching to a command button (or similar approach)?

 

[Remou]

In what way is this post different from

http://www.tek-tips.com/viewthread.cfm?qid=1474444&page=1,

please?

 

[CharlieT302]

Hi Remou,

There may be a misunderstanding on my part in not connecting the dots. However, the difference is that in the prior post, I was simply checking to see if a file "exists." If it did, it would trigger a command.

What I am asking here is to create a permanent "link" (stored in the Table group of the Main window) to a spreadsheet file. The link will then be used by queries (etc.) to extract or view data.

For simplicity sake, I want to be able to place the dbase in the My Documents folder on anyone's workstation without the need for an absolute path.

 

[formerTexan]

You can create a link to the spreadsheet through the Table section of the Database window? Right click, select "Link Tables" and then set File Type to Excel.

Or go here for a sample of code to handle relinking:

http://www.mvps.org/access/tables/tbl0009.htm

A search on this site using "relinking tables" will turn up many responses.

Cheers, Bill

 

[CharlieT302]

Thank you for the input

However, I know how to create links to external sources.

My question is this:

Can a link be created that always looks for the source in the My Documents folder regardless of what workstation the the dbase is loaded onto or what user is logged on "without the need to recreate the link".

 

[Remou]

No, I do not believe you can do that. However, you can check the limks at start-up and re-link if the link is incorrect. It does not take much time. You can combine a check on the directory.

Sub LinkXLS()
'Needs reference to Microsoft DAO 3.x Object Library
Dim db As Database
Dim rs As DAO.Recordset
Dim tdf As TableDef

Set db = CurrentDb

strpath = CreateObject("WScript.Shell").SpecialFolders("MyDocuments")
'Let us say there is an extra folder called data.
strfile = Dir(strpath & "\Data\*.xls")

Do While strfile <> ""
    'The use of system tables is not supported    
    strSQL = "SELECT Database, Name From MSysObjects " _
    & "WHERE Database Like '*" & strfile & "*'"
    Set rs = db.OpenRecordset(strSQL)
    If rs.EOF Then
        'Missing, transferspreadsheet
    ElseIf rs!Database <> strpath & strfile Then '"\" &
        'Problem, relink
        'You could delete and transfer.
        Set tdf = db.TableDefs(rs!Name)
        'This is 'for example'. Your connect
        'string will be different. You could use 
        'Mid & Instr to get the first pat of
        'the connection string. 
        tdf.Connect = "Excel 5.0;HDR=NO;IMEX=2;DATABASE=" & strpath & "\Data\" & strfile
        tdf.RefreshLink
    End If
        
    strfile = Dir
Loop
End Sub