Roger's Access Library Homepage
Forum Home Forum Home > Roger's Access Library > Roger's Download Samples
  New Posts New Posts RSS Feed - TOPICAL INDEX TO SAMPLES
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

>
Want a good read? Try The Summer of His Life (available on Amazon!)

TOPICAL INDEX TO SAMPLES

 Post Reply Post Reply
Author
Message Reverse Sort Order
Roger Carlson View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 20 Feb 2008
Status: Offline
Points: 261
Post Options Post Options   Thanks (0) Thanks(0)   Quote Roger Carlson Quote  Post ReplyReply Direct Link To This Post Topic: TOPICAL INDEX TO SAMPLES
    Posted: 14 Jul 2008 at 11:06pm
The follow is a compendium of Access routines, each separated into its own database. Each database illustrates one or more possible solutions to a single problem. They are not intended to be fully functioning utilities, but rather an illustration of how the problem might be solved and are therefore intended for educational use. These routines are entirely free. You may use them anywhere and in any way you choose, with or without citation. Therefore, you agree to use them at your own risk.  There is no warranty implied and I am not responsible for any damages that might result from their use.
 
Note: Files designated "Access 2000 DAO" use the DAO data model and require a reference to DAO set in Access versions 2000 and 2002 (XP). Files designated "Access 2000 ADO" do not require this reference, but may need a reference to ADOX set. Files designated "Access 2000" do not use a data model.
 
If you don't understand the above, or are having problems with User Defined Variables, Click Here
 
API Samples
Applications
Automation
  • 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.
  • AutomatingPowerpoint.mdb ( intermediate )
    A simple example of automating the creation of a Powerpoint Presentation from Access.
  • AutomatingWordFromAccess.mdb ( advanced)
    This sample demonstrates how to create a Microsoft Word document from information stored in a database and charts stored in an Excel spreadsheet.
  • EmailingSpecificReports.mdb ( advanced )
    This illustrates how to email the same report with different data to a variety of users.
  • ExportToExcel.mdb ( intermediate )
    This sample demonstrates how to export data from a database using the TransferSpreadsheet method, but have the data populate a formatted spreadsheet. The trick here is to export the data to a NEW tab in the Excel workbook and link the fields from the new tab into the formatted spreadsheet.
  • ExportToExcelCharts.mdb ( intermediate )
    Sample with Documentation. This sample demonstrates how to export data from a database using the TransferSpreadsheet method, but have the data populate a Chart. The trick here is to export the data to a NEW tab in the Excel workbook and link the fields from the new tab into the chart.
  • RunRemoteMacro.mdb ( intermediate )
    This sample illustrates how to run a macro in a remote database to automate a process.
Data Conversion
  • CharacterScramble.mdb ( intermediate )
    This application illustrates to scramble character data WITHIN A FIELD. To Randomize FIELDS WITHING A TABLE, see my sample
    Datascramble.mdb.
  • ConvertDateTimeToFormattedString.mdb ( intermediate )
    This sample shows how to calculate the difference between two times and display the result in 'xx days, xx hours, xx minutes' format.
  • ConvertStringStuff.mdb ( intermediate )
    Demonstrates how to convert a variety of strings into their numeric equivalents and back.
  • CSVtoFixed.mdb ( intermediate )
    This sample reads a CSV file and exports it as a fixed width file. Module 1 (CSVtoFixed) does the entire process in code. For very large records, Module 2 (CSVtoFixed2) uses a table to allow you to create your own Export Spec.
  • DataScramble.mdb ( intermediate )
    This application illustrates how to scramble data in a table for confidentiality reasons. To scramble Text data WITHIN A FIELD, see my sample CharacterScramble.mdb.
  • Denormalize.mdb ( intermediate )
    This example takes data in a normalized table and writes it to another table in denormalized form.
  • NormalizeDenormalize.mdb ( intermediate )
    It illustrates how to take a table which has fields like Value1, Value2, Value3,... and writes them into a table which has one field (Value) with each of the values in a new record rather than having them all in one record.
  • NormalizingRepeatingColumns1 (beginner)
    Document Included
    This sample demonstrates how to normalize a simple PC_Inventory table.  Its purpose is to demonstrate the general principles of normalizing denormalized data from a spreadsheet.
  • NormalizingRepeatedColumns2 (beginner)
    Document Included
    This sample demonstrates how to normalize a PC_Inventory table that has two sets of repeated columns.  It's purpose is to demonstrate the general principles of normalizing denormalized data from a spreadsheet.  
  • NormalizingRepeatedColumns3 (beginner)
    Document Included
    This sample demonstrates how to normalize a table that has repeated Yes/N0 columns.  It's purpose is to demonstrate the general principles of normalizing denormalized data from a spreadsheet.
  • NormalizingRepeatingColumnsVBA.mdb
    Document Included
    This sample demonstrates how to normalize a table that has repeated columns with VBA.  It's purpose is to demonstrate the general principles of normalizing denormalized data from a spreadsheet with code rather than SQL statements. 
  • ParseMultivalueFieldTonormalizedStructure.mdb
    This sample illustrates how to write values from a multiple valued field (not the Access 2007 multi-value field) into a normalized Many-to-Many structure. 
  • ParseStuff.mdb ( beginner )
    The included modules demonstrate a variety of parsing problems, where information in one field needs to be put into several fields.
  • RemoveSpaces.mdb ( beginner )
    This sample removes the spaces and carriage returns from text typed into the text box.
  • RoundingStuff.mdb ( beginner )
    This sample shows a variety of rounding techniques, the most common being rounding to the nearest x decimal places. The function RoundIt is similar to the Round function in VB. All the code is stored in the basRounding module.
  • SigFigs.mdb ( beginner ) 
    This example illustrates how to round a number to a specified number of significant figures.
  • StringStuff.mdb ( intermediate )
    This sample illustrates a number of useful string functions. See the individual functions for details.
Date/Time Samples
  • ConvertDateTimeToFormattedString.mdb ( intermediate )
    This sample shows how to calculate the difference between two times and display the result in 'xx days, xx hours, xx minutes' format.
  • DateStuff.mdb ( intermediate )
    This example illustrates how to do a variety of date calculations like: First day of Month, Last Day of Month, First Day of Next Month, First Day of Current Week, Last Day of Current Week, First Day of Current Work Week, Last Day of Current Work Week.
  • VacationUsed.mdb (beginner)
    This sample illustrates one way to dynamically calculate Vacation Days Available.
  • YearsMonthsDays.mdb ( intermediate )
    A variety of functions created by different developer's to calculate the difference between two dates, which can also be used for Age Calculations. Some work in some situations and not in others. You'll have to decide which is best.
Forms
Import/Export/Update
  • CreateTableFromLinked.mdb ( intermediate )
    This sample illustrates how to create a native Access 2000 table from a linked Excel table. This process can be easily modified to do the same thing for linked Text or ODBC tables.
  • ExportFormattedFixed.MDB ( intermediate )
    This shows how to export to a fixed width text file programmatically.
  • ExportToExcel_Automation (advanced)
    • There are two ways of using Office Automation that I want to discuss:
      1. Using a Do…While loop to PUSH data from Access to Excel
      2. Using the CopyFromRecordset to PULL data into Excel from Access
  • ExportToExcel_TransferSpreadsheet
    This sample demonstrates how to append data from a database to an Excel Spreadsheet.
    • The overall process:
    • 1) Create macro to export queries
    • 2) Convert the macro to VBA code
    • 3) Modify the code to loop through a table of table and file names (MyExport)
    • Look in the "basExport_TransferSpreadsheet" module for the relevant code.
  • ImportHTML.mdb ( intermediate )
    This sample illustrates how to use the LineInput statement to import text from a text file in an extremely unconventional format. The file is an HTML file, and the code searchs for the HTML tags to find specific information and puts that in a field.
  • ImportLineInput.mdb ( intermediate )
    This sample illustrates how to use the LineInput statement to import text from a text file with a mixed format. Each line of the file is a separate record, but is delimited in a variety of ways.
  • ImportSpreadsheet.mdb ( intermediate )
    This sample illustrates 3 things:
    1) How to back up a database using the Compact Method,
    2) Create a native Access 2000 table from a linked Excel table
    3) Import data to an existing table.
  • ImportToTempDatabase.mdb ( intermediate )
    This illustrates how to programmatically create a database to hold a temp table for updating.
  • OutputText.mdb ( intermediate )
    This illustrates a simple way to precisely format data for text output.
  • SeekWithLinkedTable.mdb ( intermediate )
    It is usually said that the Seek method cannot be used with a linked table. This is true, it cannot. However, it WILL work if you open the table in the remote database explicitly with the OpenDatabase method. This sample illustrates how to do this.
  • SQLDAOLoader.mdb ( advanced )
    This sample shows how to modify the structure of a Back-End database using SQL DDL (data definition language) statements and DAO code. This sample does much the same as "SQLLoader.mdb", but does not require text files to hold the SQL.
  • SQLLoader.mdb ( advanced )
    This sample shows how to modify the structure of a Back-End database using SQL DDL (data definition language) statements. The actual code is found in the Back-end database (SQLLoaderBE.mdb).
  • UpdateDirect.mdb ( intermediate )
    This sample illustrates how to programmatically update a table directly from a text file.
  • UpdateDirectFixed.mdb ( advanced )
    This sample illustrates how to programmatically update a table directly from a FIXED WIDTH text file.
  • UpdateImportSeekFind.mdb ( intermediate )
    This sample illustrates how to programmatically update a table from a temp table.
  • UpdateRemote.mdb ( advanced )
    This sample illustrates how to run an update to a Back End (BE) database remotely for performance reasons.
  • UpdateWStatus.mdb ( intermediate )
    This database illustrates two things: 1) How to update a table programmatically with values from another table and 2) how to implement a pop-up status meter.
Miscellaneous
  • Animation.mdb ( intermediate )
    This sample shows how to implement a variety of simple animations in an Access Form.
  • AutonumberProblem.mdb ( beginner )
    This sample illustrates a number of ways to simulate your own Autonumber primary key.
  • CreateDescriptionProperty.mdb ( intermediate )
    This sample demonstrates how to create and modify the "Description" property of the following Database objects: Tables, Table Fields, Queries, Query Fields, Forms, Reports, Macros, and Modules.
  • DAO.mdb ( beginner )
    This database contains various examples of Data Definition Language (DDL) and Data Manipulation Language (DML) of both DAO and SQL.
  • DataEntryMask.mdb ( beginner )
    This sample illustrates a way to restrict what kind of characters are input into a textbox without using an input mask. This method checks the characters as they are typed and only allows legal characters.
  • ERMaster.ppt
    Not an Access sample, but a useful Powerpoint template for creating ER Diagrams.
  • FlatFileDemo.mdb ( beginner )
    This example illustrates some of the difficulties inherent in bad (non-normalized) database design and how they can be corrected. It is not a formal tutorial on Normalization, rather it is a series of examples which allow you to demonstrate the kind of trouble that a non-normalized can produce. I use this example in my classes as an introduction to why you would want to normalize a database design.
  • Ordinals.mdb ( intermediate )
    Ordinals are ordered numbers: 1st, 2nd, 3rd, 4th,...etc. This function shows how to add the correct suffix to any positive integer.
  • Pictures.mdb ( beginner )
    It is usually preferable NOT to store OLE objects like pictures in the table itself. It causes massive database bloat. The usual solution is to store the pictures as files on the hard drive and store the file name in the table.
  • Snapshot.mdb (beginner)
    It is usually preferable NOT to store OLE objects like snapshots in the table itself. It causes massive database bloat. The usual solution is to store the snapshots as files on the hard drive and store the file name in the table. This sample demonstrates how to to this.
  • TwelveBalls.mdb ( beginner )
    A fun problem in Nasty Nested IF statements
Queries
  • ActionQueries.mdb (beginner)
    Sample with Documentation. Illustrates simple Action (DML) queries.
  • ActionQueriesInCode.mdb ( beginner )
    Sample with Documentation. Illustrates how to suppress confirmation messages when running an Action query in VBA code.
  • AmbiguousOuterJoins.doc ( beginner )
    Document with sample database. Examines the Ambiguous Outer Join error.
  • BeginDateEndDateQuery.mdb (beginner)
    This sample illustrates how to create a  query which displays the BeginDate and EndDate of a range based on a single EffectiveDate stored in the table.  It is assumed that the Effective Date range of a record spans the time from the Effective date of the current record and the Effective Date of the NEXT record (minus 1).  There are three methods shown here: Using a 1) correlated subquery, 2) DMax domain aggregate function, and 3) LEFT OUTER JOIN.
  • ChooseQueryFromList.zip
    This sample shows how to display all the queries in your database in a listbox and run them with a button.
  • CreateMultiSelectQuery.mdb ( intermediate )
    This database illustrates how to create a query programmatically based on the selected values of a multi-select list box.
  • CreateQueries2.mdb ( intermediate )
    This database illustrates several examples of programmatically creating queries.
  • CreateQueries3.mdb ( intermediate )
    This form shows how to create a query programmatically from a form with option boxes.
  • CreateQueries4.mdb ( advanced )
    This form illustrates how to create a relatively flexible inter-active query facility for an end-user. It allows you to choose the table, then shows the fields available for that table and then values available for the fields.
  • CreateQueries5.mdb ( advanced )
    This set of samples illustrate how to create a flexible query generator for your users. It is similar to CreateQueries4.mdb, but no longer limits the query to 3 criteria.
  • CreateQueryFields.mdb ( intermediate )
    This sample illustrates how to programmatically create a query based on a table or another query and select only those fields that you want to see. It also has a form, which shows how to restrict the rows based on a criteria.
  • Data Definition Language: SQL vs DAO
    This sample (with full documentation) illustrates how to do a variety of DDL (Data Definition Language) operations using both SQL and DAO.  DDL operations are those that modify the database structure, ie. tables, fields, indexes, and relationships.
  • DaysBetweenQuery.mdb ( beginner )
    This sample illustrates how to create a "Days Between" query, that is, display the difference between dates in subsquent records of a table.
  • MaxQueryProblem.mdb ( beginner )
    The Problem: You want to create a query which will return a MAX Date and the corresponding Amount, grouping them on the Customer Name.
  • NumberedQuery.mdb ( beginner )
    Sample with Documentation. This sample illustrates two different ways to create a query with a sequenced number column.
  • ParaQueryDelete.mdb ( beginner )
    Illustrates a couple of different ways to use Delete parameter queries from forms.
  • ParaQuerySelect.mdb ( beginner )
    This form illustrates how to send parameters to a parameter query via a form.
  • PreviousDateQuery.mdb (beginner)
    This sample illustrates how to create a Previous Date query.  It shows how to:
     1) Display the previous date in subsquent records of a table
    or
     2) Display the previous date in subsquent records of a table  over a group. 
  • RandomRecords.mdb ( beginner )
    This application shows how to return a specific number of random records from a table.
  • RecreateTableInSQL.mdb ( advanced )
    This sample will read the table structure of the table selected in the listbox and programmatically build a series SQL statements to recreate the table and all indexes. The SQL Statements are saved in a script file with a .sql extension.
  • RemoveDuplicates.mdb ( beginner )
    Illustrates a method for removing duplicate records from a table.
  • ReturnSQLfromQuery.mdb ( beginner )
    This sample demonstrates how to read the SQL statement from a saved query using the .SQL property of the querydef object.
  • RollingAverages.MDB
    This sample illustrates two different ways to create a "Rolling Average" query.
  • RunningSumInQuery.mdb ( beginner )
    Sample with Documentation. - This sample illustrates how to create a Running Sum in a query.
  • SelfJoin.mdb ( intermediate )
    Demonstrates a number of ways to use a Self-Join in Access.
  • SubQuery.mdb ( beginner )
    This sample illustrates how to use a Sub Query to return the Maximum value of a group and the values of the other fields associated with it.
  • SubQueryInFROM.mdb ( beginner )
    Demonstrates how to use a subquery in the FROM clause of a query.
  • TooFewParameters.mdb ( beginner )
    This sample illustrates two ways to open a DAO recordset based on a saved query with parameter values stored in a form.
    Sample with Documentation.
  • TopQuery.doc ( beginner )
    Document with sample database. Illustrates issues involving the TOP predicate
Reports
Utilities
 
Consider donating $1 per sample downloaded, find out why here.<
Back to Top
Sponsored Links
>
Want a good read? Try The Summer of His Life (available on Amazon!)

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.