Parallel Databases

[cmgrn]

I have a password protected payroll database in MS Access 97. I want to open it up for other employees to make their own queries and reports.

My idea was to create a parallel database with read-only links to certain payroll tables. Any approved non-payroll employee could use this 2nd read-only database to make inquiries and reports with up-to-the-minute data but not alter the selected payroll tables in the process.

My problem is that I can't seem to make read-only links. Any queries I make let people type directly into the query and change data in the original payroll tables.

The biggest need is for Personnel to generate information for government and insurance requests they get at unannounced times and with almost instant deadlines.

Any help would be greatly appreciated.

Thanks.

 

[AlexMidd]

Why not create a separate front-end, which has read-only forms linked to the tables in the other database?

Have fun! )

Alex Middleton

 

[cmgrn]

Thanks for the quick response.

I don't really know the difference between a "front-end" and what I was thinking of as a "parallel" database. They sound basically the same to me.

We have only used forms for data input so I don't know what a read-only form would be for if it didn't allow altering data (which I don't want them to do).

I sorta pictured an empty database with several read-only table links to the main payroll database so users could do or create anything they wanted (queries, reports) from the available listed read-only tables without really hurting anything in the main database.

Can forms act as a table for input to a query or a report?

We will never know in advance what kind of request that Personnel may need to fill so I want them to have the flexibility they need without bothering me (haha) all the time.

Thanks.

 

[lespaul]

your forms are the "front-end" and the database tables are the "back-end". You will need to create a new "front-end" for the same back end that has the read-only forms. check the Access help for "splitting" the database.

HTH


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[FancyPrairie]

I'm assuming that you have setup your security using Access security (i.e. not a homegrown security model). Your FE (Front-End) database contains all of your forms, queries, report, modules, macros. The BE (Back-end) databases contains just your tables. The FE database links to the tables in the BE.

You should create at least 2 security groups:
1-Users who can modify the data
2-Users who can only read the data

In the FE db and BE db define the permissions on the tables, etc that each groups has.

Then assign users to the groups. Now each user can create their own database and link to the tables in the BE database. Users in the ReadOnly group will only be able to create queries, etc that can read the data. Access will not allow them to change the data.

 

[PHV]

Any queries I make let people type directly into the query and change data
A simple way to make a query NON updatable is to use the DISCTINT predicate:
SELECT DISTINCT ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cmgrn]

Thanks everyone! Got some new direction to follow.

At present I am only using the MS Access database password protection. I wrote a vb6 dll with a GetPassword routine that I use to feed the password to my open database commands in my vb and access projects. I figured that when other people used my db they still wouldnt be able to see the password cause my code used the dll reference. Noone has access to the dll source except my supervisor and I.

'Astor36 ADO Connection
strAstor36Connection = 
     "Provider=Microsoft.Jet.OLEDB.3.51;
     Persist Security Info=False;
     Data Source=" & 
          conAstor36DatabasePath & 
     ";Jet OLEDB:Database Password=" & 
          AstorwareDLL.GetAstor36Password & 
     ";"
Set cnnAstor36 = New ADODB.Connection
With cnnAstor36
        .CursorLocation = adUseServer
        .Open strAstor36Connection
End With

I will check into individual security but sounds like an area I would love to avoid. Haha.

Also the FE and BE layout and the distinct clause.

Thanks

 

[GingerR]

There are lots of shareware and freeware utilities out there that can crack your password. If you have payroll info (SSN's, payrates, people's addresses and phone numbers), I suggest using SQL Server instead.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[cmgrn]

Thanks GingerR!

That's another area/problem we need to give serious thought to.

We are coming off a legacy system that had limited access and tight user controls and, by comparison, our network seems like a nightmare of new accesses, permissions and relationships.