This is the most important step and is the process I refer to as Reverse Engineering!
The best way to build the Access database architecture correctly is start with the end result The Reports! by sketching out what reports you want to end up with. After all, database systems are there to serve and we want information please!
This is a longer than normal video tutorial that I typically post on my blog but this is an important introduction for developers (and end-users too of course!) who are considering building an Access database for deployment over the internet as well as managed via the native application objects of Microsoft Access.
You will need SharePoint Services 2010 or an Access hosting service to handle ‘live’ web application’s objects and with Access 2010, simple tools to help check, manage and publish to the web.
Be patient, stick to the full video demonstration and get a feel for the true power of Microsoft Access 2010.
At the end of the day, you still need to know the objects of ‘native’ Access and the main objects which will mean learning about the Tables, Queries, Forms, Reports and RDBMS (Relational Database Management System).
Working with Microsoft Access designing a new database, the question here is what is the core process of the database that you are trying to define? Access Database design is more of an art than a science and common sense will prevail.
Be patient with this video, it’s longer than normal (over 7 minutes) but it covers the essentials when designing and creating a relational (one-to-many) database using two tables.
Keep the rules simple and I have three which I want to share with you:
Make sure the field’s data type is the same size (and where applicable the same size).
Ideally, keep to the same name fields which you are joining – it will help you. Access doesn’t really care!
When creating your join, do this before you add any data in either table especially when setting the ‘Enforce Referential Integrity’ option. Microsoft Access will actually check the data integrity (if any data exists).
Setting the relationship between tables is carried out in one of two places; the Relationship Window (as in the tutorial) and the other in a query itself but doesn’t offer the ‘Enforce Referential Integrity’ option.
There is more to this – it only the start but it is clear to follow and worth a view.
A well-developed Access database will invariably have what is known as ‘Startup‘ switched on and enabled which means that when users start an Access database file it will take you to a starting screen (Access form) normally referred to as the ‘Switchboard’ (Menu).
This is used to add a simple but effective way to take end-users to a starting point and not be confronted with either the Database Window (Access 2003 or earlier) or Navigation Pane (Access 2007 and 2010) and allow access to most if not all objects – causing mayhem!
Check out this article from Christine Harrell who documents well the main myths that I too have had to personally argued with the ‘IT experts’!
Three Myths About Microsoft Access
When a manager tries to get employees into Microsoft database training, many IT administrators will try to talk them out of it. They insist that the company would be better off with a different database system and actively discourage adopting Access.
Microsoft Access isn’t the solution for all database needs, but it is a great choice for many small and even large businesses. Unfortunately, IT people often believe many of the myths about it which makes it hard for managers to implement Microsoft database training.
One of my biggest complaints with Access of old was the inflexibility of publishing and multi-user sharing online (via the web). Earlier versions of Microsoft Access used a ‘static’ web view known as Pages and to get to any level of interaction required some programming and scripting skills.
With Access 2010 combined with SharePoint 2010 services this now provides that missing ‘automated’ link. The video demonstrates what can be done using a simple business process of an online calendar system but using your imagination and head for the skies!
This video is only a demonstration and doesn’t show you how to build and implement and Access database over the web – that’s going to take considerably more than seven minutes but at least you now know the power of Microsoft Access (2010).
Please feel free to contact me for more information.
Being a regular developer and user of Microsoft Access (and other Office applications) for a long time now, I have seen various versions, incarnations and many changes to this powerful desktop application but one aspect to most of the Microsoft Office applications that has not really changed at all (well, in most cases) are the keyboard shortcuts!
Even with the newer ‘Fluent UI’ Ribbon Bar replacing the traditional Toolbars and Menu Bars, the keyboard shortcuts have remained unchanged which meant (for me anyway) no change here then as I’m a fan of the keyboard more so than the little rodent; the mouse!
In general, keyboard shortcuts are quicker than the mouse and I have listed some useful shortcuts that you may want to note and I will let you know if it applies to Microsoft Office and not just Access for good measure. Continue reading “Access Database Keyboard Shortcuts”
The MS Access macro has divided developers ever since their introduction. They’ve not changed a great deal in each release, although Office 2010 will see the most significant changes yet. Cryptic error messages, hard to debug and low on error checking are just some of the complaints from seasoned developers.
A quick video tutorial showing how to carry out basic calculations in a form though it can be applied to a report too.
It demonstrates using the Expression Builder tool which is useful for picking and choosing functions, setting operators and locating other objects from other forms, report, queries and tables.
A new look in Microsoft Access 2010 is the Expression Builder tool and has essentially the same functionality as the old one. Also, you can now even store a calculated field into a table for the first time.
Finally, if your are going to calculate in a query, you do not start with the ‘=’ sign as demonstrated with a form (and for a report).