Displaying a table link

[ddelk]

I am using Access to link to DBIV tables. I have a module that, when given a folder path, will link all the .dbf files found in the folder into Access. If a user opens the Access front end, I want to display a link to a table so the user can determine if the folder is the correct one or if they need to change to links. Unfortunately the folder name is always the same but the drive letter can change.

 

[HitechUser]

ddelk,

I am not able to "review" my code (I can remember the syntax), but you can retrieve the entie path.

Have a look at this.

http://www.peterssoftware.com/tlk.htm

 

[VBslammer]

MS Access uses a hidden table named MSysObjects to store information about linked tables. I don't have access to a dbase IV system to experiment with, but for regular MS Access linked tables the full path to the database is stored in the [Database] field of the MSysObjects table. A simple query retrieves the name of the table and its database path:

SELECT MSysObjects.Name, MSysObjects.Database
FROM MSysObjects
WHERE (((MSysObjects.Database) Is Not Null));

The MSysObjects table also has a [Connect] field that is populated for certain types of linked tables, and may contain the type of database, such as DBASE IV or similar. You could also test your linked tables using a simple function to find out which property contains the full path so you can manipulate it:

Sub LinkedTableInfo(ByVal strTableName As String)
On Error Resume Next
  Dim tdf As TableDef
  Dim db As Database
  Dim p As DAO.Property
  
  Set db = CurrentDb()
  Set tdf = db.TableDefs(strTableName)

  With tdf
    If .Connect = "" Then
      Debug.Print "Local Table"
      Exit Sub
    Else
      For Each p In .Properties
        Debug.Print p.Name & ": " & p.Value
      Next p
    End If
  End With
End Sub

The output for a sample table looks like this:

'method call in immediate window:
LinkedTableInfo("Employees")

'output
Name: Employees
Updatable: False
DateCreated: 12/19/2002 12:51:22 AM
LastUpdated: 1/31/2003 12:42:58 AM
Connect: ;DATABASE=C:\Documents and Settings\User\My Documents\Databases\myDB1.mdb
Attributes: 1073741824
SourceTableName: Employees
RecordCount: -1
ValidationRule: 
ValidationText: 
ConflictTable: 
ReplicaFilter: 
GUID: ????????

You can see the path in the connect property, which could be extracted using:

strPath = Mid(tdf.Connect, 11)

You'll just have to experiment with the dbase linked tables to see if the full path is available within the connection string.

VBSlammer

Unemployed in Houston, Texas

 

[PHV]

Unfortunately the folder name is always the same but the drive letter can change
Why not using an UNC instead of a mapped drive ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ddelk]

VBSlammer. Thanks, I know about those sys tables just didnt occur to me. I found a DAO way of doing it but I would prefer to stay with ADO throughout so I will give your sample a try.

PHV. Thanks for your input. The UNC path can change to. Its a linear model and we copy the start directory to a PC and then run it. So we have several machines the model can run on and sometimes its not always the C drive of a particular machine.