+ Post New Thread
Results 1 to 11 of 11
Office Software Thread, MS Access in Technical; Evening all, This is where I turn from techy to user. I have a scenario where I have to create ...
  1. #1
    acrobson's Avatar
    Join Date
    May 2007
    Location
    Tyne & Wear
    Posts
    519
    Thank Post
    5
    Thanked 6 Times in 6 Posts
    Rep Power
    17

    MS Access

    Evening all,

    This is where I turn from techy to user. I have a scenario where I have to create a database using MS Access. I know, there are better options, however this is dicated by the scenario I have been given for a course which I am studying.

    Given my complete lack of experience with MS Access as a development tool and user, can anyone suggest any tips on getting to know access?

    I can normally get my head around this kinda stuff, afterall, it's part of the job description, but this seems to have me beat. It could also be working 42+ hours per week and studying part time which may have hindered me slightly also!

    I have a number of text books on Access, however when ever I go to solve a problem, they never seem to have answer, or perhaps I'm askign the wrong question!


    Cheers,







  2. #2

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,512
    Thank Post
    1,320
    Thanked 469 Times in 306 Posts
    Blog Entries
    6
    Rep Power
    199
    working through a step by step tutorial to completion will be the best way. IT will introduce you to tables, relationships, and then creating queries and reports from the data. Our Year 4s are starting to use access to make up mini databases on dinosours. 3 Tables, linked, with queries and reports etc

  3. #3
    acrobson's Avatar
    Join Date
    May 2007
    Location
    Tyne & Wear
    Posts
    519
    Thank Post
    5
    Thanked 6 Times in 6 Posts
    Rep Power
    17
    Quote Originally Posted by RabbieBurns View Post
    working through a step by step tutorial to completion will be the best way. IT will introduce you to tables, relationships, and then creating queries and reports from the data. Our Year 4s are starting to use access to make up mini databases on dinosours. 3 Tables, linked, with queries and reports etc
    Yeah, I have been thinking of that,kept putting it off. Probably the only way of course. Maybe I'll come and join your year 4 class!!!

  4. #4

    Join Date
    Feb 2009
    Posts
    45
    Thank Post
    1
    Thanked 5 Times in 5 Posts
    Rep Power
    12
    'I have a scenario where I have to create a database using MS Access'

    Is this part of the problem definition - you are mandated to use MS Access??

    Assuming yes - what is the rest of the problem statement
    What are you having to build, how big/small a problem is it
    Can you see a way to build it in smaller (but useful to the end-user) parts
    How are you going to test it

    Do you have a rough idea of the sets of data that you need to store/query/manipulate
    Do you know the relationships between these data sets
    Do you have any idea of the number of rows of each data type


    Do you have to provide Data entry forms
    Might want to create two dbs - one containing Data structures/data the other containing forms/VBcode (can always separate these out later on tho')

    Do you have to provide Reports (fixed or user configurable)

    Just some thoughts - rough it out on paper before you comitt
    Be careful about making fields mandatory
    Be careful about table relationships and cascade deletes (not always what you want to do)

  5. #5
    acrobson's Avatar
    Join Date
    May 2007
    Location
    Tyne & Wear
    Posts
    519
    Thank Post
    5
    Thanked 6 Times in 6 Posts
    Rep Power
    17
    Thanks for the reply mtwITch.

    Right, here goes.

    I have to create a case mangement system in access, which, in summary has to be an access version of an application like QCC Case Notes, but with my own take on things etc.

    QCC Information Security UK - Casenotes - Can be downloaded here.

    Menus – main menu, reports menu, data input menu etc..
    Good Navigation
    Remove Record Selectors, scroll bars where not needed etc
    Close all windows not needed (i.e. don’t have too many forms open at once behind each other)
    Make finding records easy (combo boxes etc..)
    On startup make sure a form opens (main menu)
    Reports
    Custom queries

    And a few of things you mentioned in your post.

    Along with the actual database development, I also have to wrote a report on the whole project from start to end, that I can do fine, I do project management as part of my job and enjoy the challenge. Therefore, within the report, things such as what you mentioned, project design, feasibility study, project scope etc etc etc is all covered in that. I know what i want from the database development, it's simply knowing if access can provide and to get access to do this.
    Last edited by acrobson; 27th December 2009 at 06:26 PM. Reason: Additional info added.

  6. #6

    Join Date
    Feb 2009
    Posts
    45
    Thank Post
    1
    Thanked 5 Times in 5 Posts
    Rep Power
    12
    'along with the actual database development, I also have to wrote a report on the whole project from start to end, that I can do fine, I do project management as part of my job and enjoy the challenge. Therefore, within the report, things such as what you mentioned, project design, feasibility study, project scope etc etc etc is all covered in that. I know what i want from the database development, it's simply knowing if access can provide and to get access to do this.'

    If you have to produce a report on the whole project -- start it now, use it as a way of documenting your requirements / high level design
    It will help you structure the project & possibly even enable you to pruioritise the various aspects

    I think you'll find that you'll need to spend a lot of time on your UI - the navigation through it and the many ways data might need to be presented/maintained

    For your Audit trail build that as a separate module - everything gets written to the Audit logs through a single channel (perhaps an Event listener) - Users then ONLY have sequential read access to anything in the Audit logs (?? Is this a multi-User system --- If so should everyone see everyone elses Audit logs ???

    so you perhaps think in terms of Users with differing access rights to various parts of the system --- Maybe some people can cretae/modify reports Whereas other Users might only be able to trigger the report generation (this will afeect what menu options you might want to expose)

    If you have access to Visio (or similar) Drawing tool then you can draw up a 'state transition' diagram showing what actions (menu/link clicks) take a User from one screen to another - Also good documentation for your project report.

  7. #7
    acrobson's Avatar
    Join Date
    May 2007
    Location
    Tyne & Wear
    Posts
    519
    Thank Post
    5
    Thanked 6 Times in 6 Posts
    Rep Power
    17
    Quote Originally Posted by mrwITch View Post
    'along with the actual database development, I also have to wrote a report on the whole project from start to end, that I can do fine, I do project management as part of my job and enjoy the challenge. Therefore, within the report, things such as what you mentioned, project design, feasibility study, project scope etc etc etc is all covered in that. I know what i want from the database development, it's simply knowing if access can provide and to get access to do this.'

    If you have to produce a report on the whole project -- start it now, use it as a way of documenting your requirements / high level design
    It will help you structure the project & possibly even enable you to pruioritise the various aspects

    I think you'll find that you'll need to spend a lot of time on your UI - the navigation through it and the many ways data might need to be presented/maintained

    For your Audit trail build that as a separate module - everything gets written to the Audit logs through a single channel (perhaps an Event listener) - Users then ONLY have sequential read access to anything in the Audit logs (?? Is this a multi-User system --- If so should everyone see everyone elses Audit logs ???

    so you perhaps think in terms of Users with differing access rights to various parts of the system --- Maybe some people can cretae/modify reports Whereas other Users might only be able to trigger the report generation (this will afeect what menu options you might want to expose)

    If you have access to Visio (or similar) Drawing tool then you can draw up a 'state transition' diagram showing what actions (menu/link clicks) take a User from one screen to another - Also good documentation for your project report.
    Yep, the actual project report is sorted, I know what i want and I know what i have to use to get it. It's mainly knowning how to use what I have to use (MS Access) to get what I know I want. If that makes sense.

    Thanks again.

  8. #8

    Join Date
    Feb 2009
    Posts
    45
    Thank Post
    1
    Thanked 5 Times in 5 Posts
    Rep Power
    12
    I prefer NOT to use the Wizards (as they don't always produce quite what you want), so do all of my work in the appropriate Design View mode

    Design your tables (& their relationships) in one database

    Design your UI(Forms) (and Reports) in another database & make good use of Buttons (to manage CRUD) backed up by VBcode to ensure that ALL User input is validated before it is written to the DB
    If your Forms are always populated via a VBCode DAO then you can build the code such that it presents default data (along with any validations) - you can test a large % of your UI without storing/retrieving data from the Data DB

    You might also want to have 2 Data DBs --- One for all of your Dev/Test work then a Clean one for production
    (ie make a Data structure ONLY copy of the Test DB when you get close to finishing --- BUT don't delete the Dev/Test Db as you'll want to carry on testing developing / bug fixing)



    If this is a multi-User application with most of your info being updateable then you must ensure that Form load/re-load regularly retrieve latest info from the Db

    Make good use of Queries (in the Data Db) to provide data to the Form/VBCode (and also Reports)

    Think in terms of properties/operations for your Objects and apply a naming convention for them across all the MS Access components taht you end up using
    Last edited by mrwITch; 28th December 2009 at 12:11 AM.

  9. #9
    acrobson's Avatar
    Join Date
    May 2007
    Location
    Tyne & Wear
    Posts
    519
    Thank Post
    5
    Thanked 6 Times in 6 Posts
    Rep Power
    17
    Wow, thats a lot of info. It all sounds great, I shall start looking in to how to achieve that today. Redbull here I come, long nights ahead for me!

    Thanks for that

  10. #10

    Join Date
    Feb 2009
    Posts
    45
    Thank Post
    1
    Thanked 5 Times in 5 Posts
    Rep Power
    12
    Another thought

    Be careful about usine Autonumber option as a primary key in your tables --
    It is a very easy way to ensure that you have a Unique primary key BUT it has no meaning in terms of the data in that table & doesn't add any value when forming table relationships

    For you Audit trail/log table consider using a Timestamp as the primary key
    (You might have to write a bit of code - if you want a timestamp more granular than seconds)

  11. #11
    acrobson's Avatar
    Join Date
    May 2007
    Location
    Tyne & Wear
    Posts
    519
    Thank Post
    5
    Thanked 6 Times in 6 Posts
    Rep Power
    17
    Good point, I have never being fond of the good old auto number, in my short life using access! I need to add a field of the user being able to enter there own given reference number anyway, so I should be able to work around that.

    Cheers.

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 5
    Last Post: 4th December 2009, 11:50 PM
  2. Replies: 1
    Last Post: 23rd October 2009, 08:53 AM
  3. Replies: 20
    Last Post: 20th August 2009, 09:40 PM
  4. [MS Office - 2003] Access Help
    By garethedmondson in forum Office Software
    Replies: 2
    Last Post: 26th January 2009, 10:02 PM
  5. M$ Access
    By Hightower in forum How do you do....it?
    Replies: 5
    Last Post: 15th June 2008, 06:15 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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