Linking database advice

[matt beamish]

Dear experts,

I am now wanting to develop further a time-sheet recording and flexitime calculating database that I was developing a few weeks back. This is running smoothly and does what I want it to do. I now want to take a summary of information from perhaps 30 of these databases (one for each user) and append the information into one table in master database that is held centrally.
I am aware that corruptions can occur with table database linking. The central database is critical to the operations of my organisation, and I do not want to risk compromising that with sloppy design and resulting corruptions. I am wondering if it might be advantageous to use another database to collate the 30 or so separate tables from each user, to create one table that is then linked into the main database either on start up, or by an administrator running an append query.
On appending data is it better practise to
1. Delete all exisiting data in the target table and append a new total set
2. By virtue of primary key info attempt to only append new data.
Any general advice on how to do this and avoid corruptions in the future gratefully received.
Thanks
Matt

 

[matt beamish]

The security complication I have always avoided and used NTFS security on the network - essentially using users network logins as validation of their status. This does not permit differential access to forms etc within the database and only works to either permit or deny access. I have kept away from Access security as I have found it complicated!
I understand that databases are not just speadsheets. But why should I consolidate into one database? To avoid corruption? Because multiple databases are unstable?
Thanks

 

[matt beamish]

OK, 2 of the 4 pilot databases I have now merged in a split BE/FE database. Im amazed at how painless the first bit was. All seems OK now, but I had a bit of a job with Autonumber PKs that are used to link other tables together. I could not simpy append data into tables as the new autonumber PKs then had nothing to relate to in the child tables. By using various queries between removing and restoring relationships I have got there. But its not something I would want to do routinely.

I hadnot really appreciated that (from what I am seeing), a BE/FE as *.mdb is nothing more than the en-mass linking of tables into table free projects.

I can now maintain security with NTFS by limiting access to the FEs, locking down the queries and using criteria in the queries to limit users from accessing other peoples data. I can hide the BE to stop most people, but I guess I need to retain modify permissions on the BE to allow data to change, and the file to run in its folder.

Another day, another dollar spent!!

 

[matt beamish]

No I want to get rid of the spreadsheets which is what people are using at the moment, and automate the process of merging the data. I have only 4 users using the pilot dbases currently - and 2 I have now merged. This is testing prior to rolling out in a few months maybe to the rest of them. I think I can control the access adequately with NTFS.

Thanks