This page sets out to explain some of the fundamentals of Microsoft Access. It has been written for the benefit of anyone who wishes to learn more, or is thinking of trying their own hand at developing an application. It is not necessary to take on board this information in order to use a Microsoft Access Application. 

Microsoft Access is deceptively easy to get into, many people will have a crack at it, and eventually develop an application that they think is suitable for their purpose. What they actually develop is nothing short of a mess. What makes matters worse is that they then go on working long hours to populate their database with information. At a later stage they discover the shortcomings of what they have created.

To develop reasonably successfully in Access one would need to take on board extensive knowledge not only of VBA code but Structured Query Language (SQL), and also an understanding of the relational database model. Not to mention the development of Access forms and reports. It is also worth mentioning that commercial database development requires quite a knowledge of maths, book-keeping and accountancy.

VBA (Visual Basic For Applications) is the programming language of all Microsoft Office software except Outlook. The name "Visual Basic" actually refers to a packaged rapid application development program (RAD). The programming language of this is also Visual Basic For Applications. There is a lot of code in Access that doesn't exist in Visual Basic, however it is still the same language.

A Microsoft Access application is different to an ordinary program such as you would purchase off the shelf at somewhere like PC World. Microsoft Access, as it comes out of the box, is as useless as the proverbial chocolate fire guard to the ordinary user. Unless of course, you have some developer skills, or you are lucky enough to find that one of the packaged templates is exactly what you need. A true program is different in that it is compiled into a single executable file (MyProgram.exe). A Microsoft Access application (MyProgram.mdb, or MyProgram.mde) needs to run on Microsoft Access. If someone wants specialist software, Microsoft Access is capable of providing a very powerful solution. Also, developers are licenced to distribute a version of Microsoft Access known as the "run-time version, along with their solution. This means that users will not have to have Microsoft Access installed on their computers. 

Microsoft Access was developed mainly to support what is known as a  "relational database", although a "flat file" system is an option.  Exactly what "relational" means is best illustrated by example. 

Just suppose that you have a business maintaining and repairing machinery for local factories. Well you will have to keep some kind of record of your work. You probably have a book into which you enter details of each job together with the relevant customer. You would get pretty fed up with entering the same customers name over and over again. Well imagine splitting this book into two, one contains the details of all of your customers, the other is your work book. Each customer in your customers book is given a unique ID number. Then when you enter a job in your workbook, you enter the ID number of the customer that you are doing it for. To take the example further, you could have a book of cash receipts indexed to your customers in the same way, and another book which holds your invoice totals. Well this would be a kind of normalization, to use a developers expression. Your system would probably involve adding up the total of your labour hours, multiplying this by your billing rate, and then adding on the value of any parts that you have used. You would then have to add VAT and send out an invoice for each job, and at the end of each month you would tot up the outstanding balance due to you from each customer and send out statements. The Access equivalent of the above would contain separate record sets representing customers, workbook, invoices, and receipts. of course this is an oversimplification. The graphic image at the top of the page represents the table structure and relationships of the "Cumbria Miners" database written for a mine research society. As you can see, the database has been normalized into 13 separate tables.

As I have written elsewhere on this web, there is quite a bit more to developing in MS Access than understanding the fundamentals of the relational model. f you fancy trying your own hand at it the best advice that I would offer is don't. However if you choose to disregard this advice, the following is intended to spare you some grief.

If you started with some other kind of database, such as Microsoft Works, don't forget that you can save your existing data as DBase and import it into Access as a table. You will then need to split it up as a process of normalization.

 Normalization?

Yes, remember the above example, the principle of the separate books?

It is a good idea to consider some kind of naming convention. Professional developers use a single word, or words separated by the underscore.   If you have imported using Dbase you will see that the various file converters have done this. It is a good idea to stick with it as this ensures compatibility. The names of variables usually have a prefix which denotes the data type. e.g. strMyString  As String. If you don't know what a variable is, you have a long way to go.

You will need to create queries to process the information that you enter into your database. If you have to create a series of queries that are all related to the same task, give them all the same prefix. this will cause Access to group them all together in the database window, you will also be able to see at a glance what they do. If you don't do this it is easy to loose track of what you are doing. If some object in the database becomes no longer in use, don't be in a hurry to delete it, you may find that you need it after all. You can prefix it with a z, then Access will group all of these marked objects at the end of the database window.

 It is important to understand what relationships are and how they work. If you get this wrong, expect some unusual results from calculated fields in your queries. There are relationships in Access between tables, between nested queries, and between forms and sub forms and reports and sub reports.

 If the application that you are working on is a simple one, you may get by without having to write any of your own Visual Basic code. However, there is a function IsLoaded which tests if a form is loaded or not. (Does not work with subforms, see we are getting in deeper already!). This is used quite often and is not one of Access's built in functions. You will have to copy the module from one of the sample databases that you get with MSOffice. It is usually called either "Global Code" or "Utility Functions". 

Office 97 Professional has a good on line manual "Building Applications with Microsoft Access 97", you will probably have to fish about in the CD-ROM to find it, try the ValuePack. 

A new version of the Microsoft Jet data engine (V4), this supports up to 2 gig of data, double its predecessor. The data types have been aligned with those used by SQL Server, there are new record locking procedures, replication features, and Unicode support; allowing 2 bytes per character. This gives support for far eastern languages. And other integration with SQL Server.

The Microsoft Data Engine, based on Microsofts SQL Server software. This software is a database that supports unlimited data storage and wide area networking with unlimited number of clients.

A new data access object collection, the ActiveX Data Object or ADO collection. This works better with MSDE/SQL Server.

It dispenses with Visual Basic for Applications as an integral part of Microsoft Access in favour of using the same libraries as the Visual Basic RAD tool.

To run an existing Access 97 application you can either convert to the new version, or open under a new version to view the application but not make any design changes. It also backwards converts, however I have had problems with this feature on some installations.

Office XP came into the market with Access 2002, then closely followed by Office 2003 with Access 2003. Basically, much improved versions of 2000, their biggest drawback is the controversial product registration process. Microsoft have been ripped off right left and center in the post with software piracy. it has been estimated that for every legitimate installation of Office there have been 10 pirate installations. Microsoft's solution is to require every singe installation to be independently registered with them. They then give you a number without which the product will not run! Obviously, a local network installation would require multiple packages or a multiple client network version. Again, its possible to convert forward and backward with these later versions, and I do have to say that everything ,seems to work much better. This year (2007) yet a later version appeared, however I have yet to work with this, basically I see no advantage at this point in time.

All of these new versions of MS Access are a vast improvement on 2000 which seemed to me to be ponderous and developer unfriendly, to be honest I hated it and continued to work with the old Access 97. The actual feel of both these new versions is much better than 2000 to me, but this may be just my opinion. The fact remains that to the average small/medium business all the necessary functionality was supported in the earlier version, so improvements are really for the benefit of the developer. The developer tools that you get with Access 2002, 2003 and 2007 Developer Edition are much better, the package wizard is better and easier to understand. I have to give full marks to Microsoft for this. A bit of advice. Office 2003 and 2007 will only run on Windows NT Version 5 or later, to the uninitiated that's Win 2000, XP and Vista. Anyone stuck in a Win 98 or Millennium (Yuk) time warp Office XP will work fine.