Using UNC path when reconnecting tables

[Baby Face Lee]

I'm a bit out of my depth with this so hope you can help.
I have a database with one frontend and 2 backends. I work from home so all
the table links need to be updated once the files are copied onto the office
server - they're all stored in the same directory.
I'm trying this code:

Function Reconnect()
'**************************************************************
'* START YOUR APPLICATION (MACRO: AUTOEXEC) WITH THIS FUNCTION
'* AND THIS PROGRAM WILL CHANGE THE CONNECTIONS AUTOMATICALLY
'* WHEN THE 'DATA.MDB' AND THE 'PRG.MDB'
'* ARE IN THE SAME DIRECTORY!!!
'* PROGRAMMING BY PETER VUKOVIC, Germany
'* (e-mail address removed)
'* ************************************************************
Dim db As Database, source As String, path As String
Dim dbsource As String, i As Integer, j As Integer

Set db = DBEngine.Workspaces(0).Databases(0)
'*************************************************************
'* RECOGNIZE THE PATH *
'*************************************************************

For i = Len(db.Name) To 1 Step -1
If Mid(db.Name, i, 1) = Chr(92) Then
path = Mid(db.Name, 1, i)
'MsgBox (path)
Exit For
End If
Next
'*************************************************************
'* CHANGE THE PATH AND CONNECT AGAIN *
'*************************************************************

For i = 0 To db.TableDefs.Count - 1
If db.TableDefs(i).Connect <> " " Then
source = Mid(db.TableDefs(i).Connect, 11)
'Debug.Print source
For j = Len(source) To 1 Step -1
If Mid(source, j, 1) = Chr(92) Then
dbsource = Mid(source, j + 1, Len(source))
source = Mid(source, 1, j)
If source <> path Then
db.TableDefs(i).Connect = ";Database=" + path +
dbsource
db.TableDefs(i).RefreshLink
Debug.Print ";Database=" + path + dbsource
End If
Exit For
End If
Next
End If
Next

'DoCmd.Rename "NoAutoExec", acMacro, "AutoExec"
End Function

It all works fine at home but it fails when tried on the server. I presume
it's a problem with recognising the UNC paths. There's a function here:
http://www.mvps.org/access/api/api0003.htm called fGetUNCPath but I haven't
the first idea about how/if to use it. Could you provide me with some
guidance? Feel free to talk down to me as I'm well outside my comfort zone!!
;-)

Thanks!

Lee

 

[Daniel Pineault]

You're on the right path.

simply add the api code into a module and then call it to substitue your
drive letter with the UNC path.

In your case it would be your path variable that you would need to parse the
drive letter and run it through the fGetUNCPath function prior to
reconnecting the tables.

A simple example:
Lets say on my pc I have a drive labelled z: which is really a network path
\\desktop01\c$

then you could simply convert the z: into it's UNC conterpart by

fGetUNCPath("z:")

So in your case you'd need to do something like

path = Mid(db.Name, 1, i)
UNCDrive = fGetUNCPath(left(path,2))
UNCpath = UNCDrive & right(path,len(path) - 2)

This is aircode so please check it before implementing it is just to
illustrate the principle.
--
Hope this helps,

Daniel Pineault