Split DB

[Guest]

I wish to be able to create a split DB. The purpose is to allow another
individual the ability to update data through the use of a form. I am running
into several problems. Mainly due to the fact that many of my tables are link
to many tables within other databases.

Is there a better way to utilize an existing form to allow for the
individaul the ability to ONLY enter data into the form in which I may link
it to the database which contains confidential information in which the
individual will not be allowed to view ?

Thank you in advance.

John

 

[Lance McGonigal]

I recommend that you switch to a SQL Server backend and use trusted
security. This way you can prohibit the view or edit of unauthorized data.

Good luck.

 

[Tim Ferguson]

I wish to be able to create a split DB. The purpose is to allow
another individual the ability to update data through the use of a
form. I am running into several problems. Mainly due to the fact that
many of my tables are link to many tables within other databases.

You have a number of things to do.

First of all, I would be wondering why you have a single database
fragmented across several mdb files. You could make life much easier for
yourself by consolidating the design into one Back End mdb with all the
tables, and as many front ends as you need for your users.

Next, the sort of things you are asking can be achieved using Access User
Level security. This will present you with a substantial learning curve
but once you have the hang of it, it's pretty straightforward. The best
answer is to remove all permissions from all your users to all your
tables; then allow access to specific parts of the data using RWOP
queries. This is all detailed in the help files and various white papers
and web pages.

The final part is to manage the users' experience so that they do not
come across any "You cannot save this data" errors. In other words, give
them front ends that only have forms that do things they are allowed to
do. That might be full read-write record level stuff for the data entry
clerks, and a bunch of reports and pivot table queries for the managers.
Etc.

Yes, it takes planning; but that will be true regardless of which
platform or technology you want. Managing security effectively always
needs careful thought. But Access does have to tools to allow you to do
what you want here.

Hope that helps


Tim F

 

[Guest]

I did not clarify. I am using Access as the middle DB. data (time sheet and
project allocation) is entered into the Access DB, 2002. The time sheet data
is then imported into Ceridian's SQL database. The project allocations are
imported into Great Plains Accounting software for posting to the G/L.
The links I spoke of in the original questions refered to links to the SQL
databases of the accounting and payroll software. Only one mdb file.

Access security is very important. But I will attempt your suggestion about
User Level Security. I have been trying to use that route, but ran into
problems due to the queries refering to the link tables. Tables of which I
would disallow the user to "see".

 

[Tim Ferguson]

The links I spoke of in the original questions
refered to links to the SQL databases of the accounting and payroll
software. Only one mdb file.

That makes sense; but it also seems to solve the security problems, since
you can rely on the security built in to the SQL Server databases to
ensure that nobody can write or read anything they are not allowed to.

Or am I expecting too much from the 3rd party software?

Access security is very important. But I will attempt your suggestion
about User Level Security. I have been trying to use that route, but
ran into problems due to the queries refering to the link tables.
Tables of which I would disallow the user to "see".

If you can, then do it on the server not the client.

Best wishes


Tim F