Microsoft Access Programming

Here are my notes and links on using the Access database product from Microsoft.

Click here for an Alphabetical Index of Topics on this page Components Editions of the product Executables OCX UDL - Universal Data Link name of OLE DB data sources vbp files VBX files Versions of the product Wizards

Take the Brainbench certification test on Microsoft Access

Topics:

Site Map  
About this site  

Product History

Microsoft Access is a product in Microsoft's Office suite of tools

Microsoft offers the product in different editions.

snpvw.exe, the MS-Access Viewer enables you to view but not modify databases and snapshot reports created by run-time versions of MS-Office Access 97 through Access 2003.
Newsgroups for Access at Microsoft

MSDN of Access Developers

10 Tips for Access Developers

Lycos on Microsoft Access.

Run Time Environment

To number each detail record in a report:
Add a calculated text box to the detail section.
Set the ControlSource property of the text box to =1.
Set the RunningSum property of the text box to Over All.

Development Environment

• The Watch Window
• The Object Browser Window
• The Call Stack Window
• The Immediate Window

  To display the current system date and time in the immediate window:

  ?Now()

Sites for Tips and Tricks

Data Access VBA Programming

When a form is first opened, the following sequence of events occurs: Open (form) - Load (form) - Resize (form) - Activate (form) - Current (form) - Enter (control) - GotFocus (control)

Field names are encased in brackets: [FieldName]

A new class instance (cXX) is created by declaring an object variable based on the class. The Set command with New keyword creates an instance of a user-defined object:

Dim objXX as cXX
Set objXX = New cXX

To print the value result to the debug window:

Debug.Print rslt

When variables are initialized, a numeric variable is initialized to 0, a variable-length string is initialized to a zero-length string (""), and a fixed-length string is filled with zeros. Variant variables are initialized to Empty.

DAO (Directory Access Objects)

The AddNew Method adds a new row to the recordset.

Between Processes

Access provides a QBE (Query By Example) to provide a GUI for specifying queries:
• tables to be used are shown in the top pane.
• fields output from a query is designated by each column.
• sorting is specified by the left-to-right order of columns.
• data filtering is specified in the "Or" row.

Fields and Functions

Here are some fields you can define. Fields from a database are encased between [] brackets. For example: [XXX]

• CStr([XXX]) to convert a numeric to alpha field.

• Left([XXX],3) to display the leftmost 3 characters of a alpha field.

QBE vs. SQL Views

Access automatically creates standard SQL code when you switch from QBE (Query By Example) to the SQL view.

Free Online Advice: Tips and Techniques

Introduction to Microsoft Access 97: A free course of study from free-ed.net.

Fee Based Component Offerings

• TegoSoft has ActiveX controls (OCX) for turning exe's into ASP, for IP to IP Chat, to allow installation of setup files over the web, to upload files, and manipulating maps.

• DevPartner for Microsoft Access from NuMega helps with testing

• Sheridan provides a popular calendar and data widgets, plus a library of controls to mimic Microsoft Outlook and Microsoft Office 97 and 2000. These controls resize automatically.

• Data Type Functions for VB

• Tab Pro is popular. Farpoint also offers calendar and list objects too.

VB6 For sale

Programmer Certification

• Microsoft's

User Groups

• Orange County VB Users Group
• Microsoft's TechEd conferences
• Wrox VB Users Conference
• VB On-line Users Group

  Newsgroups:

  alt.lang.basic
  comp.lang.basic.misc
  comp.lang.basic.visual
  comp.lang.basic.visual.misc
  comp.lang.basic.visual.announce
  comp.lang.basic.visual.database
  comp.lang.basic.visual.3rdparty

• Acky.net's General Microsoft Access Support Forum

  Microsoft Access Programmer's Journal

  vb-zone

  Microsoft Access Code Archive

  The VB Pro

  The VB FAQ at Ohio State

  Advanced Microsoft Access at Duke.net

  Carl and Gary's Microsoft Access Home Page

  ZD Journal's IVB

  Visual-basic.com

  Microsoft Access Shop

  Microsoft Press Microsoft® Office 97/Microsoft Access® Programmer's Guide

  Devnetwork.com

  Code of the Week for Microsoft Access

  OReilly's VB Controls in a Nutshell

  Microsoft Microsoft Access 6.0 Resource Guide

Utilities/Products

• Configuration Management: Intersolv's PVCS (Program Version Control System)
• Database Design: Logic Works'
  • ERwin (Entity Relation diagramming) for Microsoft Access designs data models. “ERwin Server FRE (forward-and reverse-engineers) applications, generates triggers and quickly builds Watcom SQL databases. ERwin's open dictionary allows for bi-directional synchronization of Microsoft Access Extended Attributes and domains throughout an application development efforts.
  • (Business Process) BPwin version 2 “a powerful modeling tool for analyzing, documenting and improving complex business processes.”
  • Model Mart to facilitate sharing and collaboration of data models.
• Modeling:
  • Rational's Rose for Microsoft Access and Rose for Windows
  • Popkin Software's System Architect family of Data Architect, Enterprise Modeling, and Business Process Modeling tools integrates with Microsoft Repository
• Web application development system Haht HahtSite and Haht SDK
• Generators:
  • Multi Soft's WCL/QFG generates VB and PB applications linked to 3270 and 5250 host screens from legacy systems.
  • Multi Soft's WCL/Toolkits
• Testing Tools:
  • Rational Software's SQA Team Test and Load Test
  • Logic Works' TESTBytes version 4
  • Among Compuware's suite of tools is QARun to capture and playback and Xpediter/SQL for Microsoft SQL Server.
  • Cyrano's Cyrano ClientPack and Winscope TQM
  • Datatect - Automates generation of realistic test data
  • KogoSoft has database debugging tool for Microsoft and MS-SQLServer
  • Mercury Interactive offers LoadRunner and WinRunner
  • Seque's QA Partner
  • PBCURE source code examination and correction tool
• SQL Databases:
  • Informix
  • Intersolv provides “DataDirect” ODBC drivers
  • Microsoft SQL Server 6.5
  • Oracle 7
  • SQLBase - Centura's (Formerly Gupta's) Relational DBMS
  • Microsoft - Microsoft SQL Server and SQL Anywhere
  • Watcom SQL is a development environment used in Logic Works products such as ERWin.
  • XDB
• SQL editors:
  • SQL-builder from CASTsoftware
  • DBArtisan 4.0 by embarcedero.com supports Microsoft, Oracle, and Microsoft.
• Robohelp for developing help files.
• XcelleNetTM for data synchronization of remote and mobile field force applications.
• Application Packages:
  • linar.com (an Intrinsyc company) has a pure Java-to-COM bridge.
  • Powercerv ERP Plus ( The IDC White Paper)
  • Accounting software package from Accura Software, Inc. 1735 Analog Drive Richardson, Texas 75081 USA Tel: (972) 671-2001 Email: info@accurasoft.com
• Security: Migration for discontinued PADLock release 6.X tool which provided client-side, object-level security for Microsoft Access applications. It secures access to windows, custom controls, DataWindows, DataWindow columns, menu items, etc.

Job Boards

• JFP's Microsoft Access jobs

Consultants

• Microsoft's Certified Solution Partners