Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2010
    Posts
    2

    Unanswered: Design advice for task tracking database needed

    I am tackling a project that is a little over my head and need some help with some design issues and will probably need some help with implementation. My question for the moment is- Is this kind of thing ok for this forum or am I asking too much? With that said here's a bit more background.

    I'm a professional photographer, but I have a little computer programming experience and some relational database experience (as in I've built a couple of relational databases for my own use, read some literature on good design, but am definitely a beginner) I need a database for my business that is more complex than anything I've done before (plus it's been several years since my last one so I'm a bit rusty.)

    The project I am working on is creating an Access program to track order workflow all the way through the process. It's main purpose is to break down orders consisting of several different products, each with with a number of tasks to complete and assign specific tasks to several different people and track them.

    Before I go into great detail (which I am happy to do), I'll wait to hear if this is the right place to be and what I should do next.

    Thanks,
    Robin

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    There are 2 fields which are key that you'll want to have in your data tables. One is DateEntered (date/time type) and the other is EnteredBy (text type). (they can be named anything). Optionally you may want DateModified and ModifiedBy fields.

    I'd recommend putting the DateEntered and EnteredBy fields on the form (they can be locked) and set the DateEntered default value to = now() and the EnteredBy = getUser(). This gives you a timestamp type of system.

    See this http://www.dbforums.com/6274790-post20.html for coding to automatically grab the user's loginID (ie. getUser() coding).

    I use this kind of setup in almost all my data tables and it really make things easy for other coding/queries.

    As far as the table design and other designs, after you design your relational tables, you can post them for advice. You can also look at these posts for tips on form and other design tips:
    http://www.dbforums.com/microsoft-ac...de-bank-6.html

    Things I'd recommend avoiding in your design:
    1. Switchboard (your main menu form should be unbound to any tables to make it multi-user friendly)
    2. Macros (learn and write vba code - it'll make your life easier)
    3. Bad field/table naming (ie. tables/fields named with spaces or other non-alpha characters like $#! etc...) - it will make your vba coding life easier.
    4. Using the "lookup" tab for a field in the table design.
    5. Using non-standard API type modules (such as the mousewheel.dll and other modules) - until you get familiar with how these types of modules work and the impact they can have in a multi-user environment.

    Things I'd recommend doing in your design:
    1. Splitting the front-end/back-end (ie. front-end = forms/coding, back-end = tables).
    2. When done coding, creating an MDE (or ACCDE) for the users (ie. compiled code).
    3. There are other recommendations which you may want to browse the MSAccess Code Bank on this form for tips. (2nd link on the MSAccess main forum page).

    You may also want to look at the Northwind.mdb database that ships with MSAccess. This has some nice features it demonstrates (but again, avoid the switchboard, macros and other things this mdb demonstrates as indicated above.)
    Last edited by pkstormy; 07-23-10 at 22:36.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jul 2010
    Posts
    2
    Thanks! That gives me a lot to digest. Fortunately I didn't use any spaces in my field and table names so far. As far as vba goes I first fell in love with programming on a 286 running Borland Turbo Basic. That was about half a lifetime ago, but I think i can pick ut up again. Later I played with Delphi enough to get the basics of OOP, so hopefully between the two I can learn vba pretty quick. I knew about avoiding macros, but I had planned on using a Dashboard, so I'll rethink that. Oh, and I found a great article on splitting the database. i'm pretty excited about that capability, we need to run this thing over a peer to peer network and if I understand what I read I should be able to do that after compilng and splitting.

    I'll try adding the fields you suggested and read up on the resources here. I just installed the northwind example files so I can look at them. When I get all that done I'll probably be back with some more specific questions. thanks again for taking the time to get me pointed in the right direction.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Also, if you're going to have a lot of users in your front-end, consider this:

    http://www.dbforums.com/6274786-post19.html
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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