Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Nov 2002
    Location
    USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linked tables (2002)

    Is there a way you can type the path for a linked table versus having to click it in. I have a database that has links to several tables in a database on a network drive. The drive on my workstation is G:. However when the application is used from the actual workstation, it needs to go to the R: drive as that is the network drive specified in that location. I am using my machine only to set it up. I realize I can change the mapped G: drive to an R: drive on my workstation as a workaround but I really don't want to do that as I have other files that use the G:. Hopefully that makes sense.

    Thanks,
    Steve

  2. #2
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked tables (2002)

    If you want to do this in code, I can post some. It's moderately complicated and its written in Access 97, so it might not work in 2002.

  3. #3
    Star Lounger
    Join Date
    Nov 2002
    Location
    USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked tables (2002)

    Gwenda, If you have it handy and it is not too much trouble I would love to look at.

    Thanks... Steve

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked tables (2002)

    OK. This code snippet actually re-connects the tables, where strFileName is the name of the .mdb backend. When code is posted it loses all indenting, so you'll have to put it back in again. Also, remember that this is Access 97 code.
    G

    '-- Loop through all tables, reattaching those with non zero-length Connect strings.
    For Each tdf In dbCurrent.TableDefs

    With tdf
    If .Connect <> "" Then
    .Connect = ";DATABASE=" & strFileName
    End If
    End With
    Next tdf

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linked tables (2002)

    Hi Gwenda,

    This is off topic. There are two ways to display indented code in a post:
    <UL><LI>Put <!t>[pre]<!/t> before and <!t>[/pre]<!/t> after your code. It will be displayed "as is" in Courier New.
    <LI>Replace n spaces by <!t>[tab]<!/t>, where n is the number of spaces inserted when you type the Tab key in the Visual Basic Editor (default 4).[/list]The <!t>[pre]<!/t>...<!t>[/pre]<!/t> pair and <!t>[tab]<!/t> tag can be typed manually, or entered from the 1-Click TagPanel.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linked tables (2002)

    The code will work in 2002 (and in 2000), but you need to have a reference set to the DAO 3.6 library and you should declare the database and recordset objects specifically as DAO.Database and DAO.Recordset to avoid possible compile errors. To handle this in ADO, you would need to use a Catalog object and loop through its Tables collection instead of the DAO tabledefs.
    Charlotte

  7. #7
    New Lounger
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked tables (2002)

    Steve
    I had a similar problem and came up with a simple solution, remap the drive to both letters. Or in the case of a local drive, I have a "D" drive and need the database to appear on the "E", I mapped "E" to point to a directory on the "D" drive on Windows 2000. Where the operating system does not allow this, you can use the subst command to get the same effect.
    This has the advantage of not changing your drive mappings and not changing the code when you move to the workstation.
    Good Luck
    Humble Irishman

  8. #8
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked tables (2002)

    When I have to link to network drives I navigate through "My Network Places" (W2K) to link them. This gives them the network path rather than the mapped drive. All users should then be able to use them from any network drive.

    HTH

    Peter

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linked tables (2002)

    You may find that Subst is unreliable or not allowed, depending on the network OS and the operating system on the workstations. It is not the recommended method for handling this, the UNC path (not mapped drive letter) is the only reliable method.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •