I have a databse that is replicated. It is being used by users in
different offices. The task that I have been assigned is to allow
users to Sync with the master, but only allow them to see their
records.
Example: Office1 and Office2 Sync with Master DB at Office3. All
records are in all databases. When Office1 Opens their database,
the need to only see Office1 records, and Office2 only Office2
records.
I am sure I can do this with some sort of login form/feature, but
don't have time to figure it out as my boss wants this yesterday.
Can anyone help me with any ideas?
Well, the first thing you have to do is make sure that somewhere in
each record (or in the highest-level record of your data
hierarchary) there's a field that records which office the record
belongs to.
For instance, if customers belong to an office, then it should be in
the customer table, but if invoices belong to an office, it should
be in the invoice table. You have to decide where the logical place
for it is.
Then you have two choices:
1. construct your application to display only the appropriate
records, OR
2. use partial replication filters so that the branch offices don't
have any data but their own.
Now, 2) sounds like the best way, but so far as I can tell, it
really works well only for the simplest situations, as the filters
have to be fairly simple.
Implementing the first requires programming that somehow figures out
which office the front end is running in and then filters the data
based on that.
One method would be to have a table that maps the location of the
back end data file in each office to the office code that you're
storing in the data tables. So, you'd have a table like this:
OfficeCode BackEndLocation
Office1 \\Server1\Databases\Data.mdb
Office2 \\FileServer\Databases\Data.mdb
Then during the startup of application, you'd check the connect
string of one of the linked tables to see if it matches one of the
back end locations in your table. You'd do this with code like this:
Global strOffice As String
Public Sub SetOffice()
Dim strBackEnd As String
Dim db As DAO.Database
Dim strSQL As String
Dim rs As DAO.Recordset
Set db = CurrentDB()
strBackEnd = Mid(db.TableDefs("tblInventory").Connect,11)
strSQL = "SELECT OfficeCode FROM tblOffices "
strSQL = strSQL & "WHERE BackEndLocation='" & strBackEnd & "';"
Set rs = db.OpenRecordset(strSQL)
If rs.Recordcount = 0 Then
MsgBox "The Office table does not include a listing for your _
back end.", vbExclamation, "Error!"
[do something, such as Application.Quit]
Else
rs.MoveFirst
strOffice = rs!OfficeCode
End If
End Sub
Public Function GetOffice() As String
If Len(strOffice) = 0 Then Call SetOffice
GetOffice = strOffice
End Function
Now, to use all of this, you'd then add =GetOffice() as a criterion
for all the forms you want to filter by office.
Of course, none of this has anything to do with replication.
