Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2006
    Posts
    162

    Unanswered: DB Collaboration

    I want some opinions and recommendations.

    Im wanting to build a program. Use SQL to be the back end and Microsoft access to be the frontend.
    There will be three main components.
    Payroll
    Quality Control
    Inventory

    These three components will work together. Payroll will derive deductions from QC and Inventory. What I want is to have 4-5 frontend systems for differant personel.
    Office Manager will have access to Payroll for inputing payroll.
    QC Techs for completing QC inputs.
    Warehouse Supervisor for tracking Inventory.
    System Manager who will have access to view only.
    Last one for Admin who will have editing permissions.


    My questions is, Am I going about this the right way? Is there a better way?

    I will undoubtfully have any questions as I work on this project.




    Wanted to add that each frontend will be offline for the data entry then upload to sql database when confirmed.
    Last edited by Jay59; 07-26-07 at 00:24.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    data is data, and unless you have a specific reason for it not to be in one holding tank (database) then it should be in one amorphous database in properly designed tables using the relational methodology. reasons why it may often be split elsewhere are because different suppliers are providing different elements.
    however even if the data is stored in different databases there is nothing to stop you drawing together bits of data from different databases and presenting it to the user in a seamless way.. it'll just take longer, data integrity may be impaired

    there is a lot to be said for having disparate front end for specific functions, not least of which you can control security much more effectively.. you can base your security on the server product, the network userid and network permissions.

    however what you are outlining is a huge task its certainly many man months of work, possibly more than one many year.


    I think before you embark on this project you need to get a pretty comprehensive user requirement, try to identify what is needed, try to identify what time is required for each task, and then cost it out, compared to the cost of buying in something.

    Personally Im very wary of in house payroll... payroll legislation is so complex, so prone to changes, and so serious an issue if there is a problem that I would always try to keep payroll as a bought in service or program. you can make mistakes in many areas of life, but whatever else you do do you can't make a mistake on payroll.. employees (rightly) get real narked if you screw up their pay.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The security aspect of all this sounds fairly simple. You have far better granular security in SS than access so having that as the BE will help a lot. If there are any relationships between these modules then they should be in a single database as you can't enforce declarative integrity between databases. You can use schemas (in SS 2005) to logically compartmentalise your modules and you can also grant permissions to schemas (which might be easier for you than granting permissions to specific objects - YMMV).

    Also - if it is possible for users to wear different hats (e.g. very senior bod can access inventory & payroll) then you might consider a single front end and only allow access to the forms users have permissions to. This may or may not be possible or desired - just a thought.

    I'd agree with Mark re the scale of the task too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Sep 2006
    Posts
    162
    We have someone else paying our W-2 employees, but we still have to calc what they will make. It all piece work. So most will be calculated on what they do, not how many hours. But we will have hourly employees.

    SQL will be the holding place for the database. The frontend systems will just be used to input and upload the data. One option i'm exploring is using spreadsheets to import to access first before finilizing it to the sql.

    I have systems in place doing this now, but Payroll and QCs are using Spreadsheet. Inventory is not really organized the way it needs to be.

    You mean Sharepoint when you say SS. I actually have Sharepoint Server 2007 running.
    Last edited by Jay59; 07-26-07 at 15:44.

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I am using the exact architecture that you are contemplating. I have about 8 different databases using a SQL Server backend. Each database is used for a specific purpose. And I have one database that stores much of the shared code. You can setup references to other Access databases and use the modules in those databases.

    Which brings up a possible solution for needing one or two pieces from one database in another. My shared code database also has a modifed progress meter class that will open a form with a one or two line description. The form that is displayed is in the shared code database, but to the user it looks like it is in the current database. I have also done this with reports.

    Keep in mind that Access can get a little flaky with all the references to other Access databases. First of all you can't have Database A reference Database B and have B reference A (blows up). Also, if A references B and you have A open and you try to open B, mostly likely you will blow them both up (they will close, no corruption).

    If you understand some of the limitations and account for them, I think it will work for you.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    To add to the excellent points already made, I do the kind of payroll related thing you're talking about. Some of my apps gather employee activity, and their commission and/or hourly amounts owed are calculated for (and uploaded to) our canned payroll program. It works very nicely.

    Also, I think Pootle meant SQL Server, not Sharepoint, though I could be wrong.
    Paul

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pbaldy
    Also, I think Pootle meant SQL Server, not Sharepoint, though I could be wrong.
    Nope - you are not wrong
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •