Save all data Local and some to Remote table 1

[dezel]

We have a “confidential” db with employee info, but this is the only location where the employee data is current. So what trick can we use to write data to two tables. One local with complete records and a remote (linked) with just select info.

Table A Table B (remote/linked)
ID ID
SSN Name
Name title
title work loc
salary
work loc
...

Reason for doing is so one location will update info and others may access current info for their dbs.

 

[hermanlaksko]

First it is my hope that both tables are linked ;-)

Second in table A create a field "Trans" as a boolean (yes/no) datatype. Have some code that transfer any records that have not jet been "Trans"'ed to table B after the trasfer of data update table A's field "Trans" to True.

You can make an update statement to transfer the data and a new update statement to update the table A.

Herman

They say that crime doesn't pay... does that mean my job is a crime?

 

[JerryKlmns]

dezel,

you could make a link for the full info table into the database for everyone to use. Give no rights for that linked table to anyone. Create a query showing the harmless info from the linked table you need and on query design, right click --> properties --> run permissions: Owner's. The owner of that query should have read permission to the linked table.

No double data to maintain, safely protected.

 

[willir]

dezel

Following up on theme...

Have two backend databases -- call them HRPublic and HRPrivate.

In HRPublic

tblEmployeePublic
EmployeeID - primary key
EmployeeLN - last name
EmployeeFN - first name
Title
WorkLocationCode
...etc

tblAddress
AddressID - primary key
EmployeeID - foreign key to tblEmployeePublic
AddressType - business / home
Address
City


In HRPrivate

tblEmployeePrivate
EmployeeID - primary key
SSN
...etc

tblSalary
SalaryID - primary key
EmployeeID - foreign key to tblEmployeePrivate
CurrentSalary
LastSalaryRevDate

tblReview
ReviewID - primary key
EmployeeID - foreign key to tblEmployeePrivate
ReviewDate
PerformanceRank
...etc

So you have two databases. Place one in a fairly easily accessible area on the network. Place the other on the network but use the network security to restrict access to specific users.

...Not quite there yet.
You need to develop the frontend database(s). The frontend does two things. The frontend contains the forms, reports and queries and links to the data tables on the backend databases. It also controls the "views".

You can do this two ways...
Regular employees have one front end that does not link to the private table. The managers would get a different front end database that links to both tables in each database.

A better way would to have the frontend database link to both tables but to use security and code to control views.

So what trick can we use to write data to two tables?

Actaully you do not want to do this. Note in the example presented, EmployeeID is used to link between tblEmployeePrivate and Public. The employee name is located in the public table and there is no real reason to "duplicate" the name in the private database.

If you want to have multiple copies of the database, then look at replication (an advanced topic), or better yet, using a Citrix or Terminal server solution.

Richard