MS Access

[acrobson]

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,

 

 

 

 

[RabbieBurns]

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

[acrobson]

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!!!

[mrwITch]

'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)

[acrobson]

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.

Edited December 27, 2009 by acrobson
Additional info added.

[mrwITch]

'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.

[acrobson]

'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.

[mrwITch]

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

Edited December 28, 2009 by mrwITch

[acrobson]

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

[mrwITch]

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)

[acrobson]

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.