Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    link to the database via your Network Neighborhood (Access 2002)

    Greetings!

    I am humbly hoping someone can help me out with this. I have a back end and a front end, the back end is on a server. Right now it is linked by drive names, but I want to link it by the server name/UNC. I seem to be unable to do this, but when I look through this MB, people seem to say everywhere that it is "easy."

    If I go into Linked Table Manager, I click on Prompt for New Location. When I choose a new location, even if I see the UNC path, it always links it as a drive letter. When I go to My Network Places, the only choice I have is to add an internet/intranet path. There are no locations under My Network Places, and adding the internet/intranet path (obviously) doesn't work.

    I haven't seen any other options under Linked table Manager. Am I missing something?

    TIA!

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

    Re: link to the database via your Network Neighborhood (Access 2002)

    I can't test this, since I'm not on a network at the moment. The following function is from the Solutions9 database from Microsoft. It uses DAO, so you need to set a reference to the Microsft DAO 3.6 Object Library in Tools | References... (in the Visual Basic Editor). Copy the code to a new module.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Public Function RefreshLinks(strFileName As String) As Boolean
    ' Refresh links to the supplied database. Return True if successful.

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef

    ' Loop through all tables in the database.
    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
    ' If the table has a connect string, it's a linked table.
    If Len(tdf.Connect) > 0 Then
    tdf.Connect = ";DATABASE=" & strFileName
    Err = 0
    On Error Resume Next
    tdf.RefreshLink ' Relink the table.
    If Err <> 0 Then
    RefreshLinks = False
    Exit Function
    End If
    End If
    Next tdf

    RefreshLinks = True ' Relinking complete.
    End Function

    <img src=/w3timages/blueline.gif width=33% height=2>

    You can call this function from other code, or interactively from the Immediate window. For example:

    ? RefreshLinks("serversharefolderdatabase.mdb")

    Substitute the appropriate path. If the function succeeds, you'll get a message box saying "True".

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: link to the database via your Network Neighborhood (Access 2002)

    Hi Hans,

    Thanks so much for the code, but what I'm looking for is much less high tech. How do I set the paths through the Linked Table Manager using server name instead of drive letter?

    This will work just fine if I'm always connecting to the same location, but it won't help much if the paths change.

    Thanks!

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: link to the database via your Network Neighborhood (Access 2002)

    Hi Cecilia,

    I think to some extent this is a function of the OS you are using - at the moment I'm on a WinME system, and the behavior is rather different. If you do File / Get External Data / Link Tables ... then I get a dialog box that prompts me for the database, and on the bottom left side there is an icon that says My Network Places. When I choose that, I get several options, one being the entire network, and I can then get to a database using a UNC path, pick the table, and voila. Once you do that, I believe the Linked Table Manager will show you the correct stuff, and you will be able to browse that as well, though I haven't gone that far. Note that ODBC tables work quite differently. Also, you can see the actual link in table MSysObjects.
    Wendell

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: link to the database via your Network Neighborhood (Access 2002)

    Cecelia,

    I agree with Wendell's suggestion -- I'm using Windows 98 Second Edition. If you create a linked table via the Linked Table option in the New Table dialog (or File...Get External Data... and "drill down" through your Network Neighborhood to the serversharefolderdatabase.mdb of interest and link to the tables you want, the links should end up through the servershare designation rather than a mapped drive letter designation. If you just select the mapped drive and drill down from there, you'll end up with the drive letter being part of the link specification, which you don't want. I tried it both ways and the Linked Table Manager shows them differently (one with the servershare designation and one with the mapped drive letter designation).

    Hope this helps.

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: link to the database via your Network Neighborhood (Access 2002)

    Pat -- I hope you will be able to resolve this with the suggestions "from our listening audience" <g> (of which I am a lurker) -- but I have to tell you, we never resolved this when connecting to our Banyan Vines network after we moved - dually - to a Win2k Network. That may have been unusual, though, in that we were running both networks. However, we never went the code route that Hans suggested. Actually, we were able to connect, excrutiatingly slowly and one table at a time, so it really wasn't worth it -- oops! not exactly the same issue you had. (Occasionally, I still have that problem of being able to only connect one table at a time on a fully Win2k network, Access 97, but it's infrequent.)
    thx
    Pat W.

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: link to the database via your Network Neighborhood (Access 2002)

    What O/S are you using? Is it possible it is a permissions issue?

    Normally, when you select the Linked Table Manager and check the "Always prompt for new locations" checkbox, you will be presented with a browse box which lists your physical drives and which also should have an entry for "Network Neighborhood" (or "My Network",or whatever it is called now). Underneath this "Network" entry should be another entry for "Entire Network". You may have to drill down several layers to get to the server. I've never had problems with this before.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: link to the database via your Network Neighborhood (Access 2002)

    Sorry for the late response....It's WinXP, and by that, it's very possible that TPTB have decided not to let us do that. Entire Network does not give me anything usable at all. I thought that in Acc97 I could specify the paths when linking, but I can't do that anymore. It must be a permissions issue.

    Thanks for the response,

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: link to the database via your Network Neighborhood (Access 2002)

    Hi Wendell,

    I have a feeling it is a permissions problem. TPTB probably locked us out. I'm only trying to link to my backend, but by using the drive letter rather than the unc path, because that way each region can map the same server to the same drive letter and it will always work. But using the unc path, they won't be able to do that (the regions are not allowed access to each others' drives anyway).

    Thanks,

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: link to the database via your Network Neighborhood (Access 2002)

    TPTB - I just have a problem with all these abbrevs.
    FWIW - When you bring up the Linked Table manager and type in the full UNC path into the Filename combobox and tap enter, you should then get a list of all the .mdb's in that location. It is then just a matter of choosing the .mdb you want.

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

    Re: link to the database via your Network Neighborhood (Access 2002)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> TPTB - The Powers That Be. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    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
  •