Access / VBA 2

Monitor page
for changes

Components of Access / VBA in More Detail

VBA is a glue language - it is the way you bind an Access object's event (e.g. the On Click event of a Command Button) to some action - like showing another Form, sending an Access Report to the printer or changing the data.

As I keep saying to my students, Access may appear as one program, but it is a combination toolkit. Therefore, to be conversant, a programmer needs to understand elements of:

VBA (the glue).

the VBA IDE - see MSDN: Microsoft Access 2000: A Look at the New Visual Basic Environment

the data model of your choice - the traditional one is DAO (I grew up with it). See MSDN: Microsoft Access 2000: Data Access Models. MSKB Q225048 discusses ADO vs DAO. You can also contrast the coding in Q146607 vs Q142938. You might be interested in the Jet engine's ADO Provider Properties and Settings

while the data model and VBA allows you to move the data between the tables/querys to Access objects, in actual fact, the Jet engine or SQL Server only really understands SQL. See:

MSDN: Fundamental Microsoft Jet SQL for Access 2000
MSDN: Intermediate Microsoft Jet SQL for Access 2000
Advanced Microsoft Jet SQL for Access 2000

how use the DoCmd object to manipulate Access itself when some properties and methods are not directly exposed - e.g. Docmd.OpenForm

how Access Events work. See FunctionX Tute and an old whitepaper - WX1121 (a self extracting zip file).
Programming very powerful and friendly forms is a real art and science - most of us do it by gut feel but if this is becoming a tangled web, you can Represent the Interactions with UML. (UML software can be very expensive. An inexpensive one is Enterprise Architect.)

when you know which event you want to trap, you then start thinking of how your programming should flow or sequence - the most traditional way is to use a Flow Chart, but UML has things called UML Activity Diagrams. There are tons of Flow Charting programs that can produce the traditional diamond/box charts - Corel/iGrafx Flowcharter, Visio, Paraben's or RFFlow. Smartdraw even has a tute on making one.

Amidst all this, it is of actual and long term benefit to have some coding conventions in writing VBA code:

If Data Access is the Key...

MSDN: Data Access Resources is a compilation of links to articles about ODBC. There is also another compilation called MSDN: Microsoft Data Access Technical Articles Overview. A general compilation is MSDN:Access Resources. If you are moving on to SQL Server, there is an MSDN compilation of links here.

MSKB Q167179 discusses how to suck data via DAO into a VBA variant variable containing an array.

We see scattered references to the MS DataShape Provider or the OLEDB Shape Service.

Newcomers often believe that new version software will be better, faster etc... Well, Access 97 is slower than Access 2000 because of the new Jet 4.0. Q240434 tells you how to optimise Jet 4.

Ok, so that's the plumbing, but before you start...

If you have developed in dBase, Paradox/DOS, ANSI C, Pascal, COBOL, you should a little about Software Engineering before you embark on a feature laden software project.

Most database developers should be familiar with an ER Diagram - When All You Want is an ER Diagram. It's always better to start off well by having the design of the database take care of gross functions and features.

ER Diagrams are good for databases whose main objective is to store data and then to retrieve based on some criteria. However, if you are going to design a workflow system, where data is secondary and you are more interested in the context and meaning of the information then you should do some Use Case Modelling to document the request. Microsoft has on board, Terry Halpin, a real thinker on ORM

Traditional Programming Projects (Predictive Projects) define a waterfall or formal - design-develop-release-production phases. Current thinking emphasises Agile methodologies - more iterative and more responsive to the client changing their mind. See The XP Project in Start this XP thing.