Hello,
I have a developed a nice access file and I split the file to front-end and back-end.
My DB (back-end) is on a small local server, and the fornt-end was copyed to 3 PC's on the network.
The technique I'm useing to connect the DB from the Front-end is when end user open his front-end, the code check if some table can use RefreshLink methud, if error occurs than a small "popup form" ask end user to browse to the DB and reconnect.
The connection/reconnection is this code:
Private Sub b_Submit_Click()
Dim Db As DAO.Database
Dim tDef As DAO.TableDef
Dim TableName As String
Set Db = CurrentDb()
For Each tDef In Db.TableDefs
If Len(tDef.Connect) > 1 Then
If tDef.Connect <> ";DATABASE=" & lb_Path.Caption Then 'only relink tables if the are not linked right
If Left(tDef.Connect, 4) <> "ODBC" Then 'Don't want to relink any ODBC tables
TableName = tDef.Name
Db.TableDefs(TableName).Connect = "MS Access;PWD=" & DATABASE_PASSWORD & ";DATABASE=" & lb_Path.Caption
Db.TableDefs(TableName).RefreshLink
End If
End If
End If
Next tDef
DoCmd.Close
End Sub
The files is working very good when one or two end users working together but when I'm trying to open the third file/from-end, the first or the second file/front-end is disconnected.
I have to say that this is my first development on access (I'm a Excel VBA person), so I dont know if my techniques acceptable in access or maybe this issue occurs because the small server ?
thank you !!