MS Access and VBA

Last updated 26/03/11

Please email the site owner with any comments, errors that you have found here, ideas for new items, etc.

This page is an attempt to bring together various things that may be of interest and help to students studying on modules that use MS Access and VBA as the implementation and programming software, and to students using MS Access and VBA for Project work or on Placements.

Many of the items here have arisen from Project and Placement work.

List of contents

Retirement!

Using Access 2010

Using Access 2007

Textbooks

VBA Trainers and other Documents

Table Planning Sheet

Frequently Asked Questions

New features for Access 2000

Example databases

Advice for Report Design

Links to other websites

Crash Course in Access Basics

VBA Trainers and other Documents

There are two VBA Trainers available for students to obtain free of charge. These were written due to an apparent lack of textbooks taking a 'problem solving' approach to database programming. At DMU Leicester these are available from the CSE Student Advice Centre. Students at Associate College Centres should enquire of their tutors. Electronic copies are now available as PDF files, following a request from a student, so that students who have left, are on placement or have simply lost their copy of the Trainer can access the documents from outside DMU.

New (and improved, I hope) versions of both Trainers are now (August 2005) available from the Student Advice Centre at LE.
These have more detailed explanations and increased content from the previous versions, plus exercises and suggested test plans, and have both been upgraded to Access 2002 from Access 97 (and DMU now has Access 2003, but all code appears to work just as well in 2003).

I have no plans to produce versions for Access 2007 or Access 2010. You may find the information on the Using Access 2007 and/or Using Access 2010 pages useful for some of the differences between the old and new versions of Access.

Click here for a starter database: Chelmer Leisure with tables, data and three forms. This may save you some time setting up to use the Trainer.
If you are using Access 2010 then use this version of the database instead. The original version will give a 'missing or broken reference' error - see VBA FAQ 43.

Items covered are summarised in the table below.

click on these links for details of additions, error corrections, Access 2007 problems, etc:
    Getting Started with VBA latest entry 4th August 2008, section 7.6.2.
    Further VBA latest entry 4th August 2008, section 1.

See also:

• Differences between VBA in Access 97 and Access 2000

• Different ways of doing things in VBA

Trainer content

Electronic Copies:

Getting Started with VBA - Cover

                                                   Document

Further VBA - Cover

                           Document

List of errors, extras, etc.

Getting Started with VBA  version 5.1, July 2005

25th October 2005 - extra

Section 1.8 Documenting your code.

I have just been playing around with JPadPro and have discovered that this looks as though it will print the code in colour (assuming that you have a colour printer, of course).

Do the following:

1) In your Access database, open your code window for the module that you wish to print, highlight all the VBA code and copy it.

2) In JPadPro, open a new VBScript file and paste your VBA code in.

The blue (keywords) and green (comments) font colours appear to be used in the same way as with VBA in Access.

String literals are shown in red font.

The rest is in black font.

7th February 2006 - extra (and reference to section 6.3 added 4th July 2008)

Section 3.3 Validations

The examples shown here are all for textbox fields. However, it may be that the field you want to validate is a combo box. If you set the combo box LimitToList property to Yes then Access will validate the user entry against the list and will respond with an error message of its own if the user enters some other value. If you want to replace this Access message with a message of your own, and/or do something else (such as disable a command button) then use the NotInList event for the combo box; control is only passed to this event if the user has not chosen a valid entry from the list. The BeforeUpdate event is not invoked if the entry is invalid, but is invoked if the entry is valid or null.

Look at Exercise 4.2(c) on Crash Course in Access Basics for an example of using the NotInList event to provide your own message etc. Note that the code to drop the list down is not strictly necessary as Access will do that anyway.

Section 6.3 of the 'Getting Started' VBA Trainer shows how to use this event to add a row to a lookup table, and there is further information about this lower down on this page.

12th January 2007 - extra

Section 3.6 Using a list box to select records and change contents at run-time

The VBA Trainer shows how to do this after the user has entered all the search characters into a textbox; the list box is requeried in the textbox AfterUpdate event.

The example database List Box Filter shows to change the list box contents with each character entered into the textbox; this is much neater HCI.

10th November 2010 - extra

Section 5.6 Using a query criteria parameter at run time

If you are using Access 2010 then you need to be aware that the Calendar Control object is no longer supported in Access. See VBA FAQ 43.

7th February 2006 - extra

Section 5.8 Suppressing Detail Lines

A better example than the one shown here could be for a report that is based on an Outer Join query. Queries like this can result in blank entries for groups where there is no data to be printed. See Exercise 6.5(c) on Crash Course in Access Basics for an example of suppressing blank report detail lines for such a report.

4th June 2008 - extra

Section 6.3 Adding a row to a table

The simple example shown in this section is for a lookup table with just one field.

Many lookup tables have ID fields as well, to reduce space in the main data table and to make changes to the lookup text simpler and easier to reflect throughout the main data table; for example, a table for a list of company department names.

Section 6.6 (fig 6.6.2) shows how to add a row to a table with an AutoNumber key, but many students miss the relevance of this for lookup tables with IDs.

So - suppose your Title lookup table is called tblTitle, and has an AutoNumber Primary Key ID plus a field called Title. The only code change that needs to be made here is to the SQL itself, which should now read:

       strSQL = "INSERT INTO tblTitle (Title) VALUES ('" & NewData & "')"

This now specifies the name of the field of the table into which the value in NewData is to be put. Access will work out the AutoNumber key value automatically.

The combo box RowSource SQL should be changed to ensure that the entries are sorted in Title order (the default will be ID order).

For further information on the format of the SQL INSERT statement see Appendix G.4. If you don't specify the target field names, then the statement should have data values listed for every field in the same order as they occur on the table. If you want to put data values in just some of the fields, then the field names need to be listed (comma-separated) before the VALUES clause, with the data values in the same order.

The code for processing adding to lookup tables is the same in each case, so this would suggest that a common procedure could (should?) be used. A suggestion is to code the following in an Access module:

Public Function myComboNotInList(prmNewData As String, _
                                                    prmID As Boolean, prmTableName As String, _
                                                    prmFieldName As String, prmfieldDescription As String) _
                                                    As Byte
'add to a lookup table via a combo box
'   the table can consist of just a single field, or a field plus an AutoNumber PK
'prmNewData = the value passed by Access to the combo box NotInList event
'prmID = True if the lookup table has an AutoNumber PK. False otherwise.
'the function then returns a value for the Response to be passed back to Access

Dim strSQL As String 'for the SQL INSERT statement

    'first ask if the new data is to be added
    If myYesNoQuestion("'" & prmNewData & "' is not in the list" _
                                    & vbCrLf _
                                    & "Do you want to add this " & prmfieldDescription & " to the list?") _
                                    = vbNo Then             'not to be added
        myComboNotInList = acDataErrContinue       'do nothing - user must choose again

    Else             'new entry for table - prepare to add
        If prmID = True Then         'table has an AutoNumber PK
            strSQL = "INSERT INTO " & prmTableName & "(" & prmFieldName & ") VALUES ('" & prmNewData & "')"
        Else                                 'table has just the one field
            strSQL = "INSERT INTO " & prmTableName & " VALUES ('" & prmNewData & "')"
        End If
        'now add the row to the table
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        myComboNotInList = acDataErrAdded         'tell Access new value now added to table
End If

End Function

Then all you need to do for the Title combo box is to code:

Private Sub cboTitle_NotInList(NewData As String, Response As Integer)
'see if new title needs to be added to the list
'uses common public function myCombo_NotInList
'    which returns the response value to pass back to Access

    Response = myComboNotInList(NewData, True, "tblTitle", "Title", "Title")

End Sub

And this makes it very easy to process any other lookups, as you have already done most of the work! (Reusable procedures take a little more effort initially, but can save a lot of time in the long run).

12th June 2008 - extra

Section 7.3 Importing/Exporting spreadsheet data

There is now an example database demonstrating this on the Example Databases page.

13th October 2005 - clarification

Section 7.3.6 (d) Run-time error '2391': Field 'Fn' doesn't exist in destination table '<name>'

• If the message references 'Fn' (F1, F2 etc) then the spreadsheet column in error may not have a name.

• If the message references a name such as 'Course1' (instead of Fn), then this could mean one of two things: (i) there is  a column in the spreadsheet called Course1 but no such column in the table; or (ii) there are two columns called Course in the spreadsheet.

• If you really can't work out where these extra columns are coming from, then simply adding columns of the appropriate names to the table may allow the spreadsheet to be read in OK. (Probably not the best way round the problem, but it does sometimes appear to solve it, and it doesn't matter if there are unused columns in the table).

• You should also check to see if there are several used sheets in the file. If you do not specify which sheet you want to read then it is assumed that you want to read the first sheet.

17th June 2008 - extra

Section 7.6.2 creating MDE files

You might also like to look at the information at http://www.databasedev.co.uk/microsoft-access-mde.html and http://www.databasedev.co.uk/converting-ms-access-files.html for further info.

4th August 2008 - things are DIFFERENT in Access 2007...

30th March 2006 - extra.

Section 8.2.5 Using the IIF function in the CourtAvailability query

I forgot to mention that "Like '*' " will not select fields where the content is Null.

The code here works because [Lastname] always contains a value (the Required property = Yes so that it cannot be left empty).

Depending on the purpose of the field, it could also be useful to set a default ("None" or "N/A", for example). Examples of fields where this could be done are [Sporting Interests] and [Occupation].

21st March 2006 - extra

Index: Error message, can't assign value

There should also be a reference to section 5.4 (see the second bullet in the section).

Further VBA version 5.0, July 2005.

3rd June 2008 - extra

Section 1 - password protection

This section discusses various methods of adding password protection to a database. Project students seem to like adding login forms to their databases, but that's all they do. A login form by itself is not much use - you need to allow the users to: change their own passwords; have a procedure for forgotten passwords; database admin procedures for adding/removing users and allocating initial passwords. The exercises at the end of this section were intended to make you think about those issues and implement them appropriately. There is a lot more to password protection than just a simple login form! The data security issues are important, too...

4th August 2008 - Look at the information and links for how to restrict access to the data and tables in your back end database on the Access 2007 page - the link for the Access 2007 Security Paper by Garry Robinson for MSDN looks especially useful. Note that an Access executable file (MDE/ACCDE) does not prevent a user from seeing the data in back-end database tables, or seeing where these tables are located.

19th February 2008 - Access 2007

Sections 1.4.3: Login filter problems

Some students have been having problems with the code when using Access 2007. It seems to work with some students but not others.

Sometimes the filter would not find any matching records at all (this happened when I tried running the code on my Access 2002 database with 2007). Sometimes it would work if a correct login was entered first time, but not if a correct login was entered after an incorrect one.

One student who had problems reported that the code worked on his home machine with Access 2003 but not at DMU with Access 2007. He had created his database using 2003.

Changing the line:

        DoCmd.ApplyFilter , "[LogInId] = forms![Chelmer Leisure Log-on Control]!txtLogin"

to

        DoCmd.ApplyFilter , "[LogInId] = '" & txtLogin & "'"   'another way of saying the same thing

seems to fix the problem; txtLogin is a field on the current form so can be referenced directly.

Note the apostrophes (')and quotation marks ("), as this field has a String datatype.

I used this form filter method as it seemed pretty straightforward for students to use. However, if you can't get it working then two other methods are to use (a) DCount/DLookup or (b) Recordsets. With each of these versions you will not need a bound form, but will read the information directly from the tables to look for a matching login then to read and check the password. Once these are both correct then you will read any name and access level and process these as required. These topics are both covered in the Further VBA Trainer. DCount/DLookup are also used in the Getting Started VBA Trainer.

Advice for report design

When considering producing a report from your database system, the first thing you need to consider is "what is the purpose of the report?". 

Many students produce simple reports, each from data from just one table, for no better reason than that they are easy to do! Some reports are just lists of key identification fields. These reports are often just a waste of time and paper.

It is normally best to create a query on which to base a report, rather than letting the report wizard choose fields from different tables. That way you are in control and can test the query before creating the report, and will find that it's also easier to change the query later. You can check that all parameters are used properly by the query (most good reports will need parameters).

Report Wizards are very useful for getting a basic report, but the report layout often leaves a great deal to be desired (and Access 2007 seems markedly worse at layout than Access 2003). You will be expected to customise the layout yourself.

Ask yourself: 

• What is the purpose of the report? (Don't start creating the report until you know exactly what it is for!)

• What fields are needed? and from what tables? (most reports will need data from several tables)

• Does the user want key fields or data? (e.g. student no or student name, or both)

• Should the data be grouped? (This is often useful, and usually omitted by students).

• Should the data be ordered? (don't just list things in primary key order!).

• Are totals or other calculations needed? (they usually are).

• What is the most appropriate layout for the report? (Portrait, landscape? Columnar (rarely) or tabular (more often)? Lines between sections? How is the information best ordered on the page?  - the wizard layout is normally pretty awful. Should groups be kept together to avoid a heading on the bottom of one page and the detail at the top of the next page?).

• How large is the report likely to be? (e.g. a Computing course may have approx.120 students on a year; the Chelmer Leisure Centre could have thousands of members).

• Does the report need a chart? (not always essential but can often provide useful visual information).

Then draft out your report on paper so that you have a plan of what you intend to implement. The draft should include the following items (not an exhaustive list), which are all standard good practice for reports:

• Organisation/Company name or logo in the report or page header

• Report title in the report or page header

• All parameter values in the report or page header. Use VBA code or the forms collection to pick up parameter values from parameter dialog forms.

• Headings, sub headings and column headings

• Page numbers in page footer or header (or both) - page x of y is a useful format.

• Date of printing in page footer or header (or both)

• An 'end of report' line, or (better) the total number of records printed plus any other grand totals, in the report footer

• Group totals - all groups.

• Clear layout - note that a detail line can actually print over several physical lines, e.g. address lines can be positioned underneath each other.

• Good use of fonts (size, bold, underline, Italics, colour, etc) for headings, sub headings, totals, etc, to highlight important items and make the report easy to read. VBA code can be useful here.

• If you have a column that has the same value for all entries in that section or sub section, then the value should be in a heading!

• What the report should look like if there is no data to be printed ('empty reports' are discussed in the Getting Started VBA Trainer).

• Charts should have a title, and the axes should be labelled.

There is information about layout, using parameters and VBA for reports (including how to cater for an empty report) in the Getting Started VBA Trainer and the Crash Course document.

The Multi-Purpose example database shows how to use parameters (noting the parameter values on the report header) and change some settings (sort order and text colours) at run-time.

There are some example reports at http://www.databasedev.co.uk/reports.html.

Other websites

If you find any useful links please email the URLs to the site owner  - thanks

• Matthew Dean's website

• Sites for Excel VBA

• VBA Tutor

Another site that you may like to look at (recommended by a Placement student) is http://www.mvps.org/ 

My thanks to Bob D at Drexel University, Pennsylvania, for recommending the following links:  

• Converting Access databases to/from MySQL http://www.softcities.com/Access2MySQL-Pro/download/10866.htm  

• Building a Database-Driven Web Site Using PHP and MySQL http://dev.mysql.com/tech-resources/articles/ddws/ and http://www.php.org/forums/ 

• MS Access How-To Articles (this looks really useful! - lots of Q&A) http://www.kayodeok.btinternet.co.uk/favorites/kbofficeaccesshowto.htm

My thanks to more people for the following links:

• Matthew Dean for the link to http://www.tek-tips.com/
  There is a VBA FAQ page at http://www.tek-tips.com/faq.cfm?pid=705

• Two students on CSCI1403 for http://www.techonthenet.com/access/

Other sites that look useful:

• http://www.access-programmers.co.uk/

• http://www.mvps.org/access/resources/othersites.htm

• http://groups.google.com/group/comp.databases.ms-access/topics

The links below were taken on 4th January 2001 from Nigel Roberts's old site for the year 1 HND database module.

• Access 2000 conversion to Access 97
  How to convert Access 2000 databases to Access 97 no longer available?

• MS Access version of Library Database
  library.zip

• Online MS Access Trainers. Useful resources to learn more about MS Access - these are all useful tutorials

  1. Microsoft Access '97 Tutorial - Prof. Holowczak

  2. http://www.craneis.co.uk/access/

  3. http://north.matc.edu/access/lectures.htm  needs password (is this new?)

  4. http://cru.cahe.wsu.edu/training/Access97/

• VBA

  MS Access 97 for Windows 95 VBA Programming (very detailed)

  Access BASIC Tutorial (Simple overview)

• Various links to other institutions on database modelling

  CONCEPTUAL DATA MODELLING CONCEPTS

  Data Dictionary and Entity Modelling

  Data Modelling Finding the Perfect Fit

  Entity relationship modelling  no longer available?

  Entity-Relationship Modelling slides

  ENTITY-RELATIONSHIP MODELLING

   

Table Planning Sheet

Click here for a copy of the Table Planning Sheet. 

It is always advisable to plan things on paper before implementation. Use of this sheet will help you think carefully about your tables, will save you time in the labs setting up your tables and should reduce the likelihood of you having to make changes later because you missed something out!

'Crash Course' in Access basics