Advanced Linking Tables

[ianward]

Hi All,

I have a database with FE/BE split, and our IT Networking dept are moving all data onto a new server, the problem lies with the policies that are being implented alongside this, where users are no longer able to browse top level folders - and users have now had the ability to map drives removed. (So i cannot use linked table manager) as users in other departments will have a different drive mapped for access to these files.

All users follow the same structure for mapped drives ie:
H: Home
U: Department Drive
P: Company Shared
S: Site Shared

However I have users in different departments that require access to this application and previously got around this by browsing to the files via the network so the linked table was using the fully qualified unc path:

\\Servername\Site\Department\DataSource.mdb

But as we can no longer browse top level folders i cant do that either...
I came up with the solution of using a batch file which can map a drive where all users could be mapped to a common drive letter (even though the menus are disabled) but IT Networking wont allow this - as i was told in very strong terms

I did find where this information is stored within Access where the linked tables are stored in MSysObjects but cannot update the path directly in the table!

Is there a way of updating this that anyone is aware of - or an alternative solution to my problem?

Many Thanks

Ian

 

[DCrake]

This is a case of politics over production.

I can understand the IT issues, however, you and they need to consider the mission critical issues regarding the shared application. If denying staff access to the database affects the companies production then IT should relent. Conversely if it is, say a reporting tool, then IT should stand their ground. Don't forget it's management that hires and fires not IT.

One solution would be to have the full path between the FE and BE as a value in the registry on each machine. Then get your splash screen retrieve this path and check the current setting, if different then get it to relink the FE and BE.

Another would be to create a DSN on each machine and use this to point to the database.

CodeMaster:

 

[ianward]

Thank DCrake,

I will have a look into the DSN option and see if i can get that to work.

Thinking about your initial suggestion, I think i could get the splash screen to check a registry value and compare it, but how would i programatically get it to relink the BE?

Regards -- Ian

 

[DCrake]

Option Compare Database
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function FindUserName() As String
    ' This procedure uses the Win32API function GetUserName
    ' to return the name of the user currently logged on to
    ' this machine. The Declare statement for the API function
    ' is located in the Declarations section of this module.
    
    Dim strBuffer As String
    Dim lngSize As Long
        
    strBuffer = String(100, " ")
    lngSize = Len(strBuffer)
    
       
    strBuffer = String(100, " ")
    lngSize = Len(strBuffer)
    
    If GetUserName(strBuffer, lngSize) = 1 Then
        FindUserName = Left(strBuffer, lngSize - 1)
    Else
        FindUserName = "User Name not available"
    End If

 End Function

Function RefreshLinks() As Boolean
' Refresh links to the supplied database. Return True if successful.
DoCmd.Echo True, "Refreshng table links, please wait..."
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim DeskTopPath As String
    DeskTopPath = "C:\Documents and Settings\" & FindUserName & "\Desktop\CSA Data\"
    ' 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
            If InStr(tdf.Name, "dta") > 0 Then
                tdf.Connect = ";DATABASE=" & DeskTopPath & "CSAReportsData.mdb"
            Else
                tdf.Connect = ";DATABASE=" & DeskTopPath & "CSAMasterfiles.mdb"
            End If
            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
DoCmd.Echo True, "Done"

    RefreshLinks = True        ' Relinking complete.
    
End Function

The above code checks the windows user name and relinks the data to the designated path. Play around with the code to match your issue.

CodeMaster:

 

[ianward]

Thanks for the quick response regarding this... however i feel i may have to beg and get networking to map all users to a common drive as an interim measure until we can get this onto the SQL Server.

for some reason when using paths they only work when using drive letter:

LinkPath = "C:\Shared\Site\Department\Datafile.mdb"

will work as it starts C:\

LinkPath = "\\ServerName\Site\Department\Datafile.mdb"

wont work as it uses \\ServerName

Is this something that others have come accross, or is it just me?