This database contains various examples of Data Definition Language (DDL) and Data Manipulation Language (DML) of both DAO and SQL.


The examples here were taken largely (though not completely) from "Access Database Design && Programming" by Steven Roman and published by O'Reilly.
There are 5 modules:
Class 10: Shows general examples of Data Access Objects (DAO)
Class 11: Illustrates DAO DDL techniques
Class 12: Illustrates DAO DML techniques
Class 13: Shows how to use embedded SQL DDL
Class 14: Shows how to use embedded SQL DML

Download

Access 97 (101 KB): Dao97.zip

Access 2000 DAO (144 KB): DAO2k.zip

CascadingComboBoxes.mdb (beginner)

This form illustrates how to restrict the value of one combo box based on the value of another. In this case, you choose the city, and you get a listing of just the zip codes available for that city.


It also shows how to get the asterisk (*) to show up in the RecordSource for the city, so you can choose all cities. When you choose the asterisk to list all zipcodes and then choose a zip, the corresponding City appears in the city control.

Download

Access 97 (42 KB): CascadingComboBoxes97.zip

Access 2000 (95 KB): CascadingComboBoxes2k.zip

BackUpWithCompact.mdb (intermediate)

This sample illustrates how to back up a database using the Compact Method. It creates a backup file called "BackUpWithCompact2kXX_XX_XXXX.mdb" with the X's representing the data. It puts this file in the same directory as the database. It also looks for the spreadsheet in the same directory.


Download

Access 2000 DAO (133 KB): BackUpWithCompact2k.zip

AppendValuesToExcel.mdb (intermediate)

This sample demonstrates how to append data from a database to an Excel Spreadsheet using Office Automation to find the next empty row in the spreadsheet.

The overall process:

1) Define Access and Excel object variables
2) Open Excel sheet as an Excel Object
3) Find the last row of data

4) Write data from Access query to Spreadsheet

5) Save and close spreadsheet

Look in the "basExport" module for the relevant code.

Note: This requires a spreadsheet called "ExportSpreadsheet.xls" (included with the sample) exist in the same directory as the application. Also requires references set to DAO 3.6 and Excel
Download Access 2000-2003 Version: AppendValuesToExcel.zip

CalculateSpec.mdb (intermediate)

This sample automatically calculates the Start Bit of an ImportSpec by adding the Start Bit and Length of the previous record.




Download

Access 2000 (25 KB): CalculateSpec2k.zip

Books.mdb (intermediate)

Books.mdb" and it's companion database "Books_be.mdb", make up a simple personal book inventory database. It uses the "BoilerPlate.mdb" sample to create a complete application.




Note: Put both databases in the same physical directory. If you do, the front-end (Books.mdb) will always find the tables in the back-end (Books_be.mdb).



Download

Access 97 (80 KB): Books97.zip

Access 2000 DAO (102 KB): Books2k.zip

BoilerPlate.mdb (intermediate)

This sample is a useful template for an application.


It has a number of standard forms for maintaining reference tables, viewing reports, checking database locations, and users logged on.

The tables, MainDataEntry form, and reports are just to show how the template works
This sample is a useful template for an application.




It has a number of standard forms for maintaining reference tables, viewing reports, checking database locations, and users logged on.



The tables, MainDataEntry form, and reports are just to show how the template works



Download

Access 97 (191 KB): BoilerPlate2_97.zip

Access 2000 DAO (115 KB): BoilerPlate2k.zip

AutomatingPowerpoint.mdb (intermediate)

A simple example of automating the creation of a Powerpoint Presentation from Access.




Download

Access 2000 (544 KB): AutomatingPowerpoint2k.zip

AuditTrail.mdb (intermediate)

This sample illustrates one way to produce a simple audit trail table containing: machine name, login name, user name (access security), record number, field name, original value, new value, and a date/time stamp. It tracks all changes made to a record in either the form or subform. It also tracks when new records are inserted and when records are deleted and the values of the deleted fields. It does not work properly for deleting if Cascade Deletes is set. It will show the deletes from the main form, but not the subform. Code is contained in Module1 and is called from each text box on each form. There are no doubt many improvements that can be made. This is just for illustration purposes.
Download


Access 97 (58 KB): AuditTrail97.zip

Access 2000 DAO (59 KB): AuditTrail2k.zip

AppendValuesToExcel.mdb

This sample demonstrates how to append data from a database to an Excel Spreadsheet using Office Automation to find the next empty row in the spreadsheet.




The overall process:

1) Define Access and Excel object variables



2) Open Excel sheet as an Excel Object



3) Find the last row of data



4) Write data from Access query to Spreadsheet



5) Save and close spreadsheet



Look in the "basExport" module for the relevant code.





Note: This requires a spreadsheet called "ExportSpreadsheet.xls" (included with the sample) exist in the same directory as the application. Also requires references set to DAO 3.6 and Excel



Download Access 2000-2003 Version: AppendValuesToExcel.zip

 

http://www.bmsco.tk/

AmbiguousOuterJoins.doc ( beginner )

The Outer Join can be a powerful tool for querying data in Microsoft Access. When you have only two tables, there is usually no problem. When there are more than two tables, however, using an Outer Join becomes more complicated. Sometimes Access allows it, and sometimes it gives you the not-very-descriptive "Ambiguous Outer Join" error.




Download

Access 2000 (79 KB): AmbiguousOuterJoins2k.zip

www.bmsco.tk