Hello all,
can you tell me if there's a way to achieve the following.
Currently my users utilize an access database of which the backend is situated on a network drive and the front end is local. I have 1 main user, and about ten other users which only use the database once a day. As the main user is now complaining about speed, I decided to put the backend part on his pc as well, in a shared folder, so that the other users can still access the data.
However, how do I re-link the tables to that share? The problem is that the user is located about 250 km away from here, and I really do not want to drive all the way over there... And from here, I cannot access the pc...
thanx for any tips.
SpSp

You might be able to talk the user through it if the Front End is an mdb. He should copy the Back End to a local shared drive, preferably while no one has the Back End open. I would suggest he rename the BE file before copying to ensure no one is in and no one accidentally continues to use the old data from your Front End. He has to delete all the 'linked tables' in the front end, then, using File menu, Get External Data, Link Tables and in the dialog that pops up, have him browse to his Network and find the folder he is sharing to provide access to the other users. Once he selects the back end database, he should select all the tables and all the links should be replaced. He should then copy the Front End to where everyone can get a copy and advice them to replace their old Front End.

The biggest assumptions made are:

1. There is a single Back End file and this is an Access mdb.
2. The linked table names are the same as those in the back end database.

Most versions of Access also come with a relinker add in that is not installed by default. I doubt your clients will have it installed. The advantage is that the users don't need to delete the existing links. This is accessible from the Tools... Add-Ins menu and can be installed from that menu as well.

You may do a google search on 'Carl Tribble relinker' and find a fairly sophisticated accessory program that manages all manner of linked data. It provides a good model for how to implement automatic relinking for users. It will test for the existence of a BE file and prompt the user for the location of various kinds of BE files and permit them to browse and locate a BE by themselves.

Another alternative, assuming that the drives are mapped universally to a common letter, is that you create an identical mapped drive on your system, do the relink on your system and then send the Front End to the users. All users must map identically.

A final alternative is to rename a computer to the same name as the primary user and then relink to the UNC path that you create identical to the users system and then send the relinked FE file. Make sure you have them send you a screen shot of Windows Explorer showing full paths (view menu, below tools on newer versions of Windows, Display full MS-DOS Path in title bar turned on) showing the location that the BE will reside.



Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

Wow! Thanx a lot for that elaborate reply.
I'll take my time reading through, and keep you updated on the choices made.
SpSp

Jürgen,
I may have found another way to do it: through VBA: in the table defs, I update the 'Connect' property to the correct path.
I found that I can retrieve the value stored for the location of the backend db, now I need to see if I can update this information...
When the user opens the database, he actually runs a vbscript which checks if the frontend database on the server is more recent than the one he has locally. If so, the script overwrites the local version with a copy from the server. This way, he will also get the updated location for the backend database.
Thanx however for your extensive reply.
Regards,
SpSp

Spiff:

Please note that when you iterate the tabledefs collection, you should check for a length on the connect property because there are system tables included in the tabledefs collection and you will get an error when you attempt to work with their connect property. After you change this property, you need to call the refreshlinks method to give it effect. This approach is well documented in 'Tribble's' relinker.


Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

How many total users do you have for your database?



Sal

Sorry about not having replied for a while, things have been rather hectic over here.
Total number of users is about 30, but number of synchronous users is never more than about 5.
Jürgen, how can I tell from the length of the connect property what the system tables are? Oh, wait, I'll just try to find the relinker documentation you mentioned.
Thanx for the support ! :D

OK Spiff:

I've written relinker code a few times and I pulled this out of a sample. There is a function to see if a known linked table exists and then there is an API declare to get at the folder/file browse windows dialog (so you don't need some reference based wonky common controls installed). The line that checks:

If Len(tdf.Connect) Then

which by the way can be rewritten to make more sense:

If Len(tdf.Connect) > 0


I haven't got time at the moment to go through the exact implementation but the module below is one way in which I've done this. Note that I've opened a database variable against the database to be relinked. If you don't do this, if someone else already has the database open, the subsequent person to relink will find that the relink time takes many times longer.

Option Compare Database
Option Explicit

Private Declare Function GetOpenFileName Lib "comdlg32" Alias "GetOpenFileNameA" (FilNm As StrFileName) _
  As Boolean

Private Type StrFileName
  lngStructSize As Long
  hwndOwner As Long
  hInstance As Long
  strFilter As String
  strCustomFilter As String
  intMaxCustFilter As Long
  intFilterIndex As Long
  strFile As String
  intMaxFile As Long
  strFileTitle As String
  intMaxFileTitle As Long
  strInitialDir As String
  strTitle As String
  lngflags As Long
  intFileOffset As Integer
  intFileExtension As Integer
  strDefExt As String
  lngCustData As Long
  lngfnHook As Long
  strTemplateName As String
End Type

Public Function GetFileName(Optional strInitDir As String, Optional strFilter As String = _
  "All Files (*.*)" & vbNullChar & "*.*", Optional intFilterIndex As Integer = 1, _
  Optional strDefaultExt As String = "", Optional StrFileName As String = "", Optional _
  strDialogTitle As String = "", Optional fOpenFile As Boolean = True, Optional ByRef _
  lngflags As Long = 0&) As Variant
'Gets Open file dialog and returns chosen file name
    On Error GoTo errbail

    Dim typFileName As StrFileName
    Dim strFileTitle As String
    Dim blnResult As Boolean

    StrFileName = Left(StrFileName & String(256, 0), 256)
    strFileTitle = String(256, 0)
    With typFileName
        .lngStructSize = Len(typFileName)
        .hwndOwner = Application.hWndAccessApp
        .strFilter = strFilter
        .intFilterIndex = intFilterIndex
        .strFile = StrFileName
        .intMaxFile = Len(StrFileName)
        .strFileTitle = strFileTitle
        .intMaxFileTitle = Len(strFileTitle)
        .strTitle = strDialogTitle
        .lngflags = lngflags
        .strDefExt = strDefaultExt
        .strInitialDir = strInitDir
        .hInstance = 0
        .strCustomFilter = String(255, 0)
        .intMaxCustFilter = 255
        .lngfnHook = 0
    End With
    blnResult = GetOpenFileName(typFileName)
    If blnResult Then
        lngflags = typFileName.lngflags
        GetFileName = TrimNull(typFileName.strFile)
    Else
        GetFileName = ""
    End If

ExitErrBail:
    Exit Function
errbail:
    MsgBox Err.Description, , "Error: " & Err.Number _
      & " in function modGetFiles.GetFile"
    Resume ExitErrBail
End Function

Sub callrelink()
    'assumption is that all linked tables are in one file. If not, modifications are required.
    On Error GoTo ErrorHandler

    Dim strDatabase As String
    Dim db As DAO.Database
    Dim tdf As TableDef

    Set db = CurrentDb
    For Each tdf In db.TableDefs
        If Len(tdf.Connect) Then
            strDatabase = Right$(tdf.Connect, Len(tdf.Connect) - 10)
            If Len(Dir(strDatabase)) Then 'If len of return of Dir <> 0 then implicitly True _
              BE is where expected so links should be good
                MsgBox "links should be fine. Easy enough to skip this message. Note: This does not test if the table is in the BE. You need to either try to refresh the link or open a recordset that returns few or no records (WHERE PK = 0) and trap any error"
                Exit For
            Else
                'Not found. So check to see if it is in the FE path and offer to link there
                strDatabase = fnCurrentDBDir & fnLinkedFileName(strDatabase) 'Current Path & name of linked file without path
                If Len(Dir(strDatabase)) Then 'check to see if BE is in FE path
                    If MsgBox("The data is not found. Do you wish to link to the file in the same location as the application", vbYesNo) = vbYes Then
                        If Relinker(strDatabase) = True Then
                            MsgBox "Relinked successfully"
                            Exit For
                        Else
                            MsgBox "Relink Failed"
                            Exit For
                        End If
                    Else
                        'Although BE is in FE path user wishes to browse for another version
                        MsgBox "You must find the BE database file named " & fnLinkedFileName(strDatabase)
                        strDatabase = GetFileName("C:\", "Access Databases (*.mdb)", 1, "mdb", "*.mdb", _
                          "Choose Database to Secure")
                        If Relinker(strDatabase) = True Then
                            MsgBox "Relinked successfully"
                            Exit For
                        Else
                            MsgBox "Relink Failed"
                            Exit For
                        End If
                    End If
                Else
                    'BE is not in FE path or BE file name is changed or you don't want to use it if it's there so go find it
                    MsgBox "Data is not found. You must find the BE database file named " & fnLinkedFileName(strDatabase)
                    strDatabase = GetFileName("C:\", "Access Databases (*.mdb)", 1, "mdb", "*.mdb", _
                      "Choose Database to Secure")
                    If Relinker(strDatabase) = True Then
                        MsgBox "Relinked successfully"
                        Exit For
                    Else
                        MsgBox "Relink Failed"
                        Exit For
                    End If
                End If
            End If
        End If
    Next

ExitRoutine:
    On Error Resume Next
    Set tdf = Nothing
    db.Close
    Set db = Nothing
    Exit Sub
ErrorHandler:
    With Err
        Select Case .Number
            Case Else
                MsgBox .Number & vbCrLf & .Description, vbInformation, "Error - Call to Relinker"
        End Select
    End With
    'Resume 0
    Resume ExitRoutine
End Sub

Function Relinker(strConnect As String) As Boolean
    'mdb connect string begin with ;DATABASE, other BE files are different. This will only work
    'with mdb files. You can modify the hardcoded line tdf.Conect = ";DATABASE=" & strConnect

    On Error GoTo ErrorHandler

    Dim dbCurr As DAO.Database
    Dim dbLink As DAO.Database
    Dim tdf As DAO.TableDef

    Set dbLink = OpenDatabase(strConnect) 'needed for performance in multi user application
    Set dbCurr = CurrentDb
    For Each tdf In dbCurr.TableDefs
        If Len(tdf.Connect) Then
            tdf.Connect = ";DATABASE=" & strConnect
            tdf.RefreshLink
        End If
    Next
    Relinker = True

ExitRoutine:
    On Error Resume Next
    Set tdf = Nothing
    dbCurr.Close
    Set dbCurr = Nothing
    dbLink.Close
    Set dbLink = Nothing
    Exit Function
ErrorHandler:
    With Err
        Select Case .Number
            Case Else
                MsgBox .Number & vbCrLf & .Description, vbInformation, "Error - Relinker"
        End Select
    End With
    'Resume 0
    Resume ExitRoutine
End Function

Private Function TrimNull(ByVal strItem As String) As String

    On Error GoTo Err_TrimNull

    Dim lngI As Long

    lngI = InStr(strItem, vbNullChar)
    If lngI > 0 Then
        TrimNull = Left(strItem, lngI - 1)
    Else
        TrimNull = strItem
    End If

Exit_TrimNull:
    Exit Function

Err_TrimNull:
    MsgBox Err.Description, , "Error: " & Err.Number _
      & " in function modGetFiles.TrimNull"
    Resume Exit_TrimNull
End Function

Function fnCurrentDBDir() As String
    'returns path of the currently open application
    Dim strDbPath As String
    Dim strDBFile As String

    strDbPath = CurrentDb.Name
    strDBFile = Dir(strDbPath)
    fnCurrentDBDir = Left(strDbPath, InStr(strDbPath, strDBFile) - 1)
End Function

Function fnLinkedFileName(strConnect As String) As String
    'returns mdb file name by stripping of path etc
    Dim lngPos As Long

    lngPos = 1
    lngPos = InStr(lngPos, strConnect, "\")
    If lngPos > 0 Then
        Do While InStr(lngPos, strConnect, "\")
            lngPos = InStr(lngPos, strConnect, "\") + 1
        Loop
        fnLinkedFileName = Mid$(strConnect, lngPos)
    End If
End Function


Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

Have you considered using MSDE for a database? Pulling data with access on a slow network or long distances will slow down your pipe even more. You will run into issues of backups not being made on the user PC and you may lose all of your data this way.



Sal