Tuesday, September 30, 2008

Featured Sample - Form_SearchByMultipleListBoxes

Form_SearchByMultipleListBoxes

author: A. D. Tejpal

This sample db demonstrates application of search criteria based upon selections in three multi-select list boxes. For a given list box, the search criteria is deemed True if the field value happens to match any of the selected items. Overall criteria is determined by application of And operator between the outcomes pertaining to different list boxes.

For sake of illustration, three different data types are covered by the three list boxes in this sample, namely date, text and number. In case of text type, handling of embedded quotes if any, within the field values, has also been demonstrated.

Three different styles of displaying the contents of list boxes have been shown as follows:
(a) No cascading - The full list is always under display.
(b) Simple cascading (hierarchical) - LstDate controls the contents of LstType and LstSupp, while LstType controls the contents of LstSupp.
(c) FreeStyle cascade - Whichever list box happens to be updated, causes filtering of contents of other two list boxes accordingly.

For user's convenience, facility for selecting or de-selecting the whole list box has been provided.
Three different styles of applying the search criteria are demonstrated for each style of cascading:
(a) Permanent saved query with embedded criteria clause.
(b) SQL built at run time.
(c) Filter string applied at run time.

Selection results for each list box are stored in corresponding unbound text boxes as comma separated strings. For text type data, each element of such a string is enclosed within outer single quotes, after having replaced each embedded single quote if any, by double single quotes (As the outer enclosing character is single quote, embedded double quotes if any, don't require any special treatment). For date type data, each element of such a string is enclosed within outer hashes.

This arrangement eliminates the need for repeated cycling through the list boxes while testing the field values. The value is simply checked against the contents of corresponding unbound text box, using IN operator. In case of permanently saved query with embedded criteria clause, EVal() function is used for facilitating interpretation of superstring having IN operator.

Version: Access 2000 File Format

Find it here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=403

Thursday, September 25, 2008

Access 101: Can I Create an EXE from my Access Application?

There is no way to create an EXE from an Access database. However, there is a way to install what's known as the "Access Run-time engine" that will allow the user to run the app without having Access installed on their system. The Run-Time is NOT the same as an EXE. For one thing, anyone who has Access on their system will still be able to open the application in their copy of Access (assuming they have the right version).

The Run-Time engine is installed as an option in the Package and Deployment Wizard. In Access 2000, this wizard came with the Access Developer's Edition. In Access 2003, you can get it in the Office Developer Extensions
(http://msdn.microsoft.com/en-us/office/aa905403.aspx). In Access 2007, you can download the 2007 Developer Extensions and Runtime(http://office.microsoft.com/en-us/access/HA102188681033.aspx). To run the Wizard, go to the Visual Basic Window, choose Tools, select the wizard, and follow the directions.

There are a few things to keep in mind when creating an application that will be used with the Run-time:

1) NEVER use macros. If the macros fail, you don't get the HALT screen, the whole app just crashes.
2) Error trap EVERYTHING. You want a graceful exit from every error. This is good practice regardless.
3) Don't rely on the native Access toolbars or menus. You must provide all functionality yourself. For instance, if you rely on the Find button (binoculars) to search for a record, forget it. This button won't be available to the app when using the Run-time.
4) The install disk created with the wizard will be very much larger than your program. However, in this day of CD burners, this is not the issue it once was.

Now, one thing you can do to keep your users from modifying the application is to convert it to an MDE. This is not an executable either, but it DOES have all of the source code removed. That means the user cannot open a form, report, module, or macro in Design view. Tables and queries can still be modified though.

So between converting the app to an MDE and deploying it with the Access Run-time, this comes very close to the functionality of the EXE you are looking for.

Tuesday, September 23, 2008

Featured Sample: At Your Survey (AYS)

At Your Survey

Author: Duane Hookom

At Your Survey (AYS) is a full featured application that allows users to create their own surveys by designing the questions and providing a lookup of possible responses. ATS uses a fairly normalized table structure so the same tables, forms, code, queries, and reports can be used for any number of surveys. There is a brief manual to help you get started as well as a sample survey with data.

Find out more here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3

Thursday, September 18, 2008

New Sample: A.D. Tejpal's ExportToExcelNamedMultiSheets

ExportToExcelNamedMultiSheets

Author: A.D. Tejpal

Description: This sample db demonstrates a drastically simplified method for exporting multiple access tables/queries to custom named worksheets in the given excel workbook. No automation code is needed.

Find out more here: http://rogersaccesslibrary.com/forum/forum_posts.asp?TID=432

Wednesday, September 17, 2008

What is Microsoft Access?

Microsoft Access® is the most popular database product on the planet. People argue about what this means exactly, but it is clear that more people use Access (the actual product) than any other database management system.

But many people don't realize that Access is not just one product, but a suite of tools integrated into one product. In other databases, these tools are separate programs, some of which must be purchased from a third-party. Together, these tools make Access one of the most powerful database products in existence.

Now, some may scoff at this. They would say that Access is just a tinker toy compared to a "real" database like Oracle or SQL Server. But that is just ignorance speaking. It is true that the database component of Access (the Jet database engine) is limited in terms of size, speed, and number of users. However, the Jet engine is only one part (and not the most important part, in my opinion) of Access.

So let's look at the component parts of Access:

First of all, as I already mentioned, there is the Jet database engine. In fact, there are two different versions of Jet. There is the traditional version (mdb) which has been around since version 2.0. With Access 2007, there is a new version, the Access Database Engine (accdb). Although it has limitations, it is nonetheless a fully functional relational database management system.

Secondly, there is the Access User Interface (AUI). This is the part of Access that the user sees when they open a table. With the AUI, a user can create tables, modify tables, add data, modify data, and create filters to view data. In SQL Server, this functionality is done in the SQL Server Management Studio (SSMS) or the older Enterprise Manager. In fact, the original Enterprise Manager was patterned after the Access UI.

Third, there is the Query Builder. This is an ad hoc querying tool. Queries can be built in the graphical user interface (gui) or directly in standard SQL. Access does not have "Views" like SQL Server does, but it does have "Select queries" that server the same function. In addition, Access has Data Definition Language (DDL) queries that create and modify table structure, Data Manipulation Language (DML) queries that display or modify data. In SQL Server, you would need a tool like Query Analyser or SSMS to accomplish this.

Another type of query Access uses is the Pass-through Query. A pass-throrgh query bypasses the Jet engine altogether and passes the query on to a linked source like SQL Server. The versatility of Access connet to and manipulate data from various data sources is truly astounding.

Fourth, Access has a built-in application generator. This itself consists of several parts.
  1. Forms builder. Access allows you to create form in much the same way as Visual Basic. However, Access forms are much easier to create and the controls available are specific to database development.
  2. Report builder. At some point you have to report the data in your database. In Access, you do this in the report builder. In SQL Server, you would have to use a separate product like SQL Server Reporting Services or Crystal Reports depending on how you want them published.
  3. VBA modules. All database applications require a procedural language at some point. There are just some things that can't be done in SQL. In Access, this capability is provide in Visual Basic for Applications modules, both general (global) modules and programming behind the forms and reports (class modules).

Creating an application in another database, SQL Server for instance, would require multiple products: SQL Server as the data engine, SSMS to create and modify tables, Query Builder to run ad hoc queries, Visual Basic to create the application, and Crystal Reports for reporting. In Access, you get it all in one.

So you can see that Access is really a Rapid Application Development (RAD) product than it is a database. In fact, Access might more accurately be called an application generator with a complementary database provided.