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

Topic

New Reply

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!

Reply | Quote

Replies

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.

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

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”.

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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,

Reply | Quote

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.

Reply | Quote

TPTB – The Powers That Be.

Reply | Quote

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.

Reply | Quote

Pat — I hope you will be able to resolve this with the suggestions “from our listening audience” (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.

Reply | Quote

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.

Reply | Quote

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,

Reply | Quote