Back end databases refering to each other (1 Viewer)

Keith Nichols

Registered User.
Local time
Tomorrow, 00:53
Joined
Jan 27, 2006
Messages
431
Hi Folks,

I am shortly going to embark on the second database of my career. I haven't seen anthything close to what I want to do so I thought I would ask for some general guidance before I started, rather than heading down a blind alley.

  • Around 100 users will use the database to create weekly activity reports.
  • Reports will all use the same date (Wednesday every week).
  • The employee id will be referenced from the Employee_details table of the back end of my first database.
  • Users will select the projects from the Prj_Details table of the first database.
  • Activity descriptions and comments are added for every project worked on.
  • There should be a general comment field for the week.
  • Users should be able to copy activities over from previous weeks
  • The database should report all those who have not completed a report by a certain time (12:00 noon on Wednseday)
  • Individuals should be able to get reports of their activities between any 2 dates
  • Management should be able to view weekly individual activities, all personnel active on any particular project or a sumarised list of projects with personnel active that week etc etc
  • As can be seen, this database should be accessed very heavily in the hours leading up to 12 noon on Wednesday so this needs to be considered in the design.

Any thoughts appreciated.
 

kleky

Just gettin' by..
Local time
Today, 22:53
Joined
Apr 11, 2006
Messages
43
100 users using MS Access heavily? :eek:

My experience with Access gives me the thought that it may not work, or you'll get loads of problems. Depending on what you'll be doing, you should consider how you create new records. If you use AutoNumbers, 100 people trying to add records at the same time can cause problems, so use of VBA to add records is advised in my opinion.
 

Keith Nichols

Registered User.
Local time
Tomorrow, 00:53
Joined
Jan 27, 2006
Messages
431
kleky said:
100 users using MS Access heavily? :eek:

My experience with Access gives me the thought that it may not work, or you'll get loads of problems. Depending on what you'll be doing, you should consider how you create new records. If you use AutoNumbers, 100 people trying to add records at the same time can cause problems, so use of VBA to add records is advised in my opinion.

Thanks Kleky,

I was wondering about that, amongst other things, when I started thinking about this.

The VBA thing sounds like a good idea and I will look into that.

1 way to ease the burden would be to create a separate database for each section of the department, 3 in total. This would reduce the maximum possible loading to 30+ users and you wouldn't expect more than single figures at any one time. I could then create a 4th database to collate the managemnet reporting.

More thinking to be done before I start.

Regards,

Keith.
 

kleky

Just gettin' by..
Local time
Today, 22:53
Joined
Apr 11, 2006
Messages
43
No probs.

Splitting would help, but then you may end up duplicating a lot, then this can become a nightmare to manage (and tedious to repeat). Maybe because its simple numbers and no large processes, Access could handle it.
 

Keith Nichols

Registered User.
Local time
Tomorrow, 00:53
Joined
Jan 27, 2006
Messages
431
kleky said:
No probs.

Splitting would help, but then you may end up duplicating a lot, then this can become a nightmare to manage (and tedious to repeat). Maybe because its simple numbers and no large processes, Access could handle it.

Hi Kleky,

When I actually get round to this I think the duplication thing won't be so bad as the section databases will be identical apart from the set of records they pull from the Project Tracking database. I'm hoping that I can create it once, then copy / edit the query that grabs the personnel from the Project details.

Any future modifications can be made to the first database and then be cloned to the siblings, either by doing the same modification, or by replacing the other databases and re-editing the queries for personnel etc.

I still have a bit more thinking to do before I embark on this. I have decide to bite the bullet and work through a VBA book (Access 97 programming for dummies) which is a bit out of date but will bring me up to speed on concepts etc and should save the good folks of this forum many hours in answering my inane questions. I'm hoping a little education can go a long way. :D

Thanks for your input to date.

Regards,

Keith.

Regards,

Keith.
 

Users who are viewing this thread

Top Bottom