Microsoft Access

\nMicrosoft Access is a database management system from Microsoft, packaged with Office which combines the Jet relational database engine with a graphical interface intended to make it possible for relatively unskilled programmers and non-programmer "power users" to build sophisticated "front-ends" to complex databases with surprising ease. For skilled developers and data architects, it offers nearly unlimited potential for sophisticated applications, with rapid development as the rule rather than the exception. Microsoft Access was also the name of a communications program from Microsoft, meant to compete with ProComm and other programs. It proved a failure and was dropped. Years later they reused the name for their database. One of the major benefits of Access from a programmer perspective is its relative compatibility\nwith SQL—queries may be viewed and edited as SQL statements. It uses VBA for programming forms and logic, and offers more object-oriented possiblities than are generally made use of. The report writer in Access is similar to the other popular database report writer - Crystal Reports but the two products are vastly different in their approach. MSDE (Microsoft SQL Server Desktop Engine) 2000, a cut-down version of MS SQL Server 2000, is included with the developer edition of Office XP and may be used with Access as an alternative to the Jet Database Engine. Access is widely used by small businesses and hobby programmers to create ad hoc customised systems for handling small tasks. Its ease of use and powerful design tools give the non-professional programmer a lot of power for little effort. This ease of use can be misleading. This sort of "developer" is often an office worker with little or no training in application or data design. Because Access makes it possible even for such "developers" to create useable systems, many are misled into thinking that the tool itself is limited to such aplications. This is far from true. Professional application designers use Access to create applications whose power and speed of devlopment still surprise even many who should know better by now. It does not scale well if data access is via a network, and Microsoft encourages users to transition to its SQL Server product if they want to, for example, have a large number of users. However, an Access "front end" (the forms, reports, queries and VB code) can be used against a host of database backends, including Access itself, SQL Server, Oracle, and any other ODBC-compliant product. This approach allows the developer to move a matured application's data to a more powerful server without sacrificing the development already in place. Other RDBMSs, e.g. MySQL or Borland's Interbase, may be used in its place as most have ODBC support. Companies often opt to do this as MS SQL licenses are relatively expensive, costing between about $2000 and $639,968. Its cut and paste functionality can also make it a useful tool for connecting between other databases (for example, Oracle and SQL Server) during data or database conversions. Unlike complete RDBMSs, but as with all "desktop" development tools, it lacks triggers and stored procedures. It is common to use pass-through queries and other techniques in Access to run stored procedures in RDBMSs that support these. Many developers who use Microsoft Access use the Leszynski Naming Convention, though this is not universal. It is not in any way required by Access, where you many name things as you wish. Naturally, some logical system of doing so is highly recommended, regardless of the tool.

Table of contents

1 Development 2 See also 3 External links

Development

Microsoft Access is easily applied to small projects but scales very inefficiently to big ones if applications are not designed properly. Because Access is so easy to get started in, many developers completely miss the fact that it offers a powerful development environment, the considerable power and ease of Visual Basic, and table and query possibilities that fall just short of SQL Server et al. Ironically, it seems to be the very ease of use that misleads designers into thinking that they cannot or should not use proper relational data design techniques, object-oriented programming techniques, or sophisticated application design strategies. When these are applied, Access is capable of turning out extremely powerful and robust systems. All database queries, formulas and reports are stored in the database, and in keeping with the ideals of the relational model, there is no possibility of making a physically structured hierarchy with them, that is, there is only one level. Thus, if you make a lot of queries, there is no way of knowing which ones are used by formulas, which ones by other queries, etc., unless you apply your own logically structured hierarchy. The only easy way of knowing which queries depend on which tables and/or queries is to use a logical naming scheme. If that has been done, when you modify a query or table, you can tell whether it will affect other queries and how. This becomes more problematic, as always, when projects grow. It's usually a good design technique, if the application is going to be used by a different person from the developer, to divide the Access application between data and programs. One database should have all tables, and nothing else, the other will have all programs, forms, reports and queries, and links to the first database tables. Unfortunately, Access allows no relative paths when linking, so the development environment should have the same path as the production environment. This technique also allows the developer to divide the application among different files, so some structure is possible. For anything except the most simple projects, a knowledge of VBA, the Access programming language, is needed. A very good resource with free recipes for VBA is "The Access Web" (http://www.mvps.org/access/), and a well defined search through Google (or the like) will harvest many examples. It's a large community.

See also

\n*Microsoft SQL Server\n*Microsoft Office\n*Microsoft

External links

\n*A FAQ site about Microsoft Access Access