share db over basic xp network, 3 terminals

[antonyx]

hello all.

there are 3 pcs in my office, and i need to have my database open on all 3.

we are constantly opening and closing the database to allow another terminal to make changes..

all the pcs use xp professional, the access version i have is the standard office 2003.

we have a standard network, with all pcs linked to the router, the network is working fine (file sharing and printing)

we do not have a server.. all pcs are equal at the moment.

what we are currently doing is holding the database in the shared folder of the best pc.

-------------------------------------------

as you know.. when the db is open, a locked icon is displayed in the folder which signifies it is open.

i have come to realise that access can be shared without any trouble between 2 pcs... (one has exclusive access)

however, between 3 pcs, it is not workin for me.

one person has the db open, then the next person opens it.. the third person cannot open it tho.

-------------------------------------------

without a dedicated server (we dont have time for this), is there any way to resolve this issue.


can i allocate users to the db within access itself (users within my network i mean.. eg \\pc1 \\pc2 \\pc3)


i also realise that i may have to split the db into a back end and front end.
if this is the only way around this problem, then i have to do this.. so my question is, how do i go about doing this?

and i know that doing this could lead to headaches and problems.. but i need to do this, so the process must begin now.


for those who have read this thank you, and i look forward to your assistance

 

[pbaldy]

It should be split. You run the risk of corruption otherwise:

http://allenbrowne.com/ser-01.html

 

[antonyx]

ok, i have split the db on my main pc

in the folder i have

MAIN DATABASE
and
MAIN DATABASE_be

now i tried to open the front end on all the pcs, but had the same problem.

so now that i have split the db. how can i allow my 3 pcs to access the back end without any problems.

my first guess would be to place the front end on each of the pc's desktops (so the pcs are not all opening the same front end file)

and then make sure that the front ends on all the pcs know where to locate the back end..

eg: \\pc1\Shared Docs\MAIN DATABASE_be.mdb

is it as simple as that?

 

[pbaldy]

Yes; as noted on the link I gave you:

In multi-user situations, each user receives a local copy of the application file, linked to the tables in the single remote data file.

 

[antonyx]

ok.. i get you.

as it says, this has to go on the database's startup..

i presume i should paste the code into a module...

2 questions

1. how can i tell the database to read that code as it starts up.

2. where abouts in the code do i place the address of the back end file.

thanks

'***************** Code Start ***************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Function fRefreshLinks() As Boolean
Dim strMsg As String, collTbls As Collection
Dim i As Integer, strDBPath As String, strTbl As String
Dim dbCurr As DATABASE, dbLink As DATABASE
Dim tdfLocal As TableDef
Dim varRet As Variant
Dim strNewPath As String

Const cERR_USERCANCEL = vbObjectError + 1000
Const cERR_NOREMOTETABLE = vbObjectError + 2000

    On Local Error GoTo fRefreshLinks_Err

    If MsgBox("Are you sure you want to reconnect all Access tables?", _
            vbQuestion + vbYesNo, "Please confirm...") = vbNo Then Err.Raise cERR_USERCANCEL

    'First get all linked tables in a collection
    Set collTbls = fGetLinkedTables

    'now link all of them
    Set dbCurr = CurrentDb

    strMsg = "Do you wish to specify a different path for the Access Tables?"
    
    If MsgBox(strMsg, vbQuestion + vbYesNo, "Alternate data source...") = vbYes Then
        strNewPath = fGetMDBName("Please select a new datasource")
    Else
        strNewPath = vbNullString
    End If

    For i = collTbls.Count To 1 Step -1
        strDBPath = fParsePath(collTbls(i))
        strTbl = fParseTable(collTbls(i))
        varRet = SysCmd(acSysCmdSetStatus, "Now linking '" & strTbl & "'....")
        If Left$(strDBPath, 4) = "ODBC" Then
            'ODBC Tables
            'ODBC Tables handled separately
           ' Set tdfLocal = dbCurr.TableDefs(strTbl)
           ' With tdfLocal
           '     .Connect = pcCONNECT
           '     .RefreshLink
           '     collTbls.Remove (strTbl)
           ' End With
        Else
            If strNewPath <> vbNullString Then
                'Try this first
                strDBPath = strNewPath
            Else
                If Len(Dir(strDBPath)) = 0 Then
                    'File Doesn't Exist, call GetOpenFileName
                    strDBPath = fGetMDBName("'" & strDBPath & "' not found.")
                    If strDBPath = vbNullString Then
                        'user pressed cancel
                        Err.Raise cERR_USERCANCEL
                    End If
                End If
            End If

            'backend database exists
            'putting it here since we could have
            'tables from multiple sources
            Set dbLink = DBEngine(0).OpenDatabase(strDBPath)

            'check to see if the table is present in dbLink
            strTbl = fParseTable(collTbls(i))
            If fIsRemoteTable(dbLink, strTbl) Then
                'everything's ok, reconnect
                Set tdfLocal = dbCurr.TableDefs(strTbl)
                With tdfLocal
                    .Connect = ";Database=" & strDBPath
                    .RefreshLink
                    collTbls.Remove (.Name)
                End With
            Else
                Err.Raise cERR_NOREMOTETABLE
            End If
        End If
    Next
    fRefreshLinks = True
    varRet = SysCmd(acSysCmdClearStatus)
    MsgBox "All Access tables were successfully reconnected.", _
            vbInformation + vbOKOnly, _
            "Success"

fRefreshLinks_End:
    Set collTbls = Nothing
    Set tdfLocal = Nothing
    Set dbLink = Nothing
    Set dbCurr = Nothing
    Exit Function
fRefreshLinks_Err:
    fRefreshLinks = False
    Select Case Err
        Case 3059:

        Case cERR_USERCANCEL:
            MsgBox "No Database was specified, couldn't link tables.", _
                    vbCritical + vbOKOnly, _
                    "Error in refreshing links."
            Resume fRefreshLinks_End
        Case cERR_NOREMOTETABLE:
            MsgBox "Table '" & strTbl & "' was not found in the database" & _
                    vbCrLf & dbLink.Name & ". Couldn't refresh links", _
                    vbCritical + vbOKOnly, _
                    "Error in refreshing links."
            Resume fRefreshLinks_End
        Case Else:
            strMsg = "Error Information..." & vbCrLf & vbCrLf
            strMsg = strMsg & "Function: fRefreshLinks" & vbCrLf
            strMsg = strMsg & "Description: " & Err.Description & vbCrLf
            strMsg = strMsg & "Error #: " & Format$(Err.Number) & vbCrLf
            MsgBox strMsg, vbOKOnly + vbCritical, "Error"
            Resume fRefreshLinks_End
    End Select
End Function

Function fIsRemoteTable(dbRemote As DATABASE, strTbl As String) As Boolean
Dim tdf As TableDef
    On Error Resume Next
    Set tdf = dbRemote.TableDefs(strTbl)
    fIsRemoteTable = (Err = 0)
    Set tdf = Nothing
End Function

Function fGetMDBName(strIn As String) As String
'Calls GetOpenFileName dialog
Dim strFilter As String

    strFilter = ahtAddFilterItem(strFilter, _
                    "Access Database(*.mdb;*.mda;*.mde;*.mdw) ", _
                    "*.mdb; *.mda; *.mde; *.mdw")
    strFilter = ahtAddFilterItem(strFilter, _
                    "All Files (*.*)", _
                    "*.*")

    fGetMDBName = ahtCommonFileOpenSave(Filter:=strFilter, _
                                OpenFile:=True, _
                                DialogTitle:=strIn, _
                                Flags:=ahtOFN_HIDEREADONLY)
End Function

Function fGetLinkedTables() As Collection
'Returns all linked tables
    Dim collTables As New Collection
    Dim tdf As TableDef, db As DATABASE
    Set db = CurrentDb
    db.TableDefs.Refresh
    For Each tdf In db.TableDefs
        With tdf
            If Len(.Connect) > 0 Then
                If Left$(.Connect, 4) = "ODBC" Then
                '    collTables.Add Item:=.Name & ";" & .Connect, KEY:=.Name
                'ODBC Reconnect handled separately
                Else
                    collTables.Add Item:=.Name & .Connect, Key:=.Name
                End If
            End If
        End With
    Next
    Set fGetLinkedTables = collTables
    Set collTables = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function

Function fParsePath(strIn As String) As String
    If Left$(strIn, 4) <> "ODBC" Then
        fParsePath = Right(strIn, Len(strIn) _
                        - (InStr(1, strIn, "DATABASE=") + 8))
    Else
        fParsePath = strIn
    End If
End Function

Function fParseTable(strIn As String) As String
    fParseTable = Left$(strIn, InStr(1, strIn, ";") - 1)
End Function
'***************** Code End ***************

 

[pbaldy]

1) Either call it from the load event of the form that opens with the db or you could call it from an autoexec macro. I'd do the former.

2) The code appears to get the existing location from the table properties.

To be honest, I've never bothered with code like this. I've never had a problem with losing the back end connection. If I move the back end, I simply relink the tables manually and distribute a new front end.

 

[Zigzag]

Create a new form explaining to the client what is happening. Add a relink button to this form that calls the Refresh code.

Call your new form from the Error event of the first Bound form your DB opens.

Garry

 

[antonyx]

hi.. i have placed the front end on each of the pcs and they all find the back end.

i realise that there will be a problem if pc1 is editing a record.. and then the network fails..

but will this just mean pc1's changes will be lost?? that's not to bad.

are there any other hidden dangers you lot think i should know?

 

[Zigzag]

Hi antony,

What you have posted is just 1 problem, and yes it should just be the Pc1's changes that will be lost.
There are other factors that will come into play with a multi user database (more than can be explained on here)

Here is one for free.....
Pc1 and Pc2 are changing the same record.
Pc2 closes the form therfore that record is then updated.
Pc1's form is then closed, the message appears asking if the record should be updated with PC1 changes or drop the changes that PC1 has made?

This is a real pain, someone has to have there changes dropped.
I stop this happening by not letting 2 users open the same record at the same time but there are a couple of other workaround.

Search for record locking is a good place to start

Garry

 

[antonyx]

hello.. i have a problem and need help urgently

the way i had things set up.. was that the database was split on a laptop.

i placed the front end of the database on the laptop desktop.. and held the backend in a shared folder on the laptop..

the other pcs in my office all had front ends on their respective desktops.. and all loaded the data from the backend on the laptop.

now i have taken the laptop out of the office.. and it is on another network..

i assumed that the laptop on its own would still be able to find the backend.. but i am gettin disk.network error when i open the front end..

how can i tell the database where the backend is.. please help..

i need to use it now!

 

[Zigzag]

Hi Antony,

You need to relink your tables to the new location of the BE.
This can be done by clicking Tools > Database utilities > Link table managers.
If might also be worth adding code to allow the OP to do this themselves.
Search forum for "fRefreshLinks" Credit to Dev Ashish

Garry

 

[antonyx]

thank you so much mate.. you are a legend