Various MS Access and VBA textbooks

Last updated 26/03/11

home

Some Access 2007 books that I have come across that you might like to explore further:

Reviews of various books can be found at http://blogs.msdn.com:80/access/archive/2007/02/28/access-2007-books-list.aspx

"Pro Access 2007"  By Martin W. P. Reid - preview of sample pages at http://books.google.co.uk/books?id=61Ps8-AU12IC and there's a review at http://hubpages.com/hub/Pro-Access-2007

"Brilliant Access 2007 Pocket Book"  by S Slack - http://www.pearsoned.co.uk/Bookshop/detail.asp?item=100000000243677

Alison Balter's Mastering Microsoft Office Access 2007 Development (Kindle Edition) - good reviews at http://www.amazon.com/Alison-Balters-Mastering-Microsoft-Development/dp/B00142KQ7A/ref=dp_kinw_strp_1/103-1930685-1651808

Special Edition Using Microsoft Office Access 2007 (Kindle Edition) by Roger Jennings - good review at http://www.amazon.com/Special-Using-Microsoft-Office-Access/dp/B000RH0DNU/ref=pd_sim_kinc_2

Access 2007 VBA Bible: For Data-Centric Microsoft Office Applications (Kindle Edition) by Helen Feddema - no review at http://www.amazon.com/Access-2007-VBA-Bible-Data-Centric/dp/B0014JR2IY/ref=pd_sim_kinc_1

March 2008 - I am no longer teaching database design and implementation to beginners so have not explored books using Access 2007.

Essential for CSCI1403 (module last ran in 2006/2007)

Further Reading

All of these should also be in the library.

DMU LE students can also obtain copies of some VBA Trainers to help them with learning VBA for MS Access.

Essential:           

• Access 2000 Further Skills, Smart Guide series
  Nat McBride
  ISBN 0-8264-5648-0
  Published by Continuum, 2001 - sold summer 2003 to Thomson Publishing Services
  price approx. £11.99. (October 2005)
   

  Click here for a copy of an MS Excel data file for the database table data. use File-->Get External Data to import the data. The file is Excel 97 and imports OK into Access 97 and 2000-2003. Added 30/9/2004 as several students are unable to access the Blackboard CSCI1403 website in week 1 2004/2005.

  
  Previous students recommended the earlier (Access 97) version of this textbook. It has the advantage of being reasonably priced and of taking students gently through the steps needed for the basic functions of the software. However, the Access 2000 version has several irritating points:

  ·         It won't stay open on a desk

  ·         It has several errors - see Click here for list of known errors and omissions

  ·         It has a 'read then do' format in many places. Students often (and quite reasonably) work through the software while reading the book, then come to a 'task' that repeats what they have just done, which they (understandably) find confusing. The book only intends sections headed 'task' to be worked through with the software; the rest is to be read and then used for reference as necessary.

  ·         Sometimes you are asked to do a task then not save the result. This annoys some students, but is not as silly as it may at first seem, as it is just using the software to demonstrate a point. However, there is nothing to stop you saving the result if you want - you could think up a name of your own, or use the unit and task numbers.

  We have passed on comments from previous students, and the list of errors, to the Publishers for the next version of the book. If you have any constructive comments, or find any more errors, please let us know, then we can post the information here for the benefit of all.

  A copy of the database with just the tables and some initial forms is available on the VBA Trainers page of this site.

Further Reading:

• Data Analysis for Database Design (3rd edition)
  David Howe
  ISBN 0-7506-5086-9
  Published by Butterworth Heinemann, 2001
  price approx £22.00. (2001)
   

• The Essence of Databases
   F.D. Rolland
  ISBN 0-13-727827-6
  Published by Prentice-Hall, 1998
   

• Microsoft Access/Visual Basic, Step by Step series
  Evan Callahan
  Published by Microsoft Press, 1995
  price approx. £30.00. (2000)
  Easy step-by-step approach to using VBA with Access. Doesn't cover a lot of ground, but opens up the topic so that you should be able to work out more for yourself. Recommendation by student
   

• Access Database Design and Programming
  Steven Roman
  Published by O'Reilly
  price approx. £25.00 (June 2001)
  Overview of database design, queries and programming, homing in on the important points only. Good complement to lectures - not a self-study book to replace lectures! Recommendation by student
   

• VB and VBA in a Nutshell: the Language
  Paul Lomax
  Published by O'Reilly
  price approx. £21.00 (June 2001)
  Good statement reference with examples. Recommendation by student
   

• Running Microsoft Access 2000
  John Viescas
  Published by Microsoft Press, 1999
  price £41.99 in 2002
  Good overview of Access 2000 features, including: SQL; database design; some VBA; publishing data on the web. Looks to be a useful reference book to consolidate existing knowledge and see what (else) Access offers. Rather too high-level (my opinion) for a year 1 set text. Written in an accessible style. Recommendation by student
   

• Using Access97
  Roger Jennings, published by QUE.
  Expensive (£65!!)  (2000)
  Very comprehensive reference on Access features, but not too much on VBA. Plus CD.
   

• ‘Access 97 Power Programming’
  F. Scott Parker, published by QUE. Recommended by Jennings - see above.
  2000 version may also be available now?

• Microsoft Office 97 Visual Basic Programmer's Guide
  Advertised in Access Help. 2000 version also available.

• Real World Microsoft Database Protection and Security
  Garry Robinson, published by APress
  ISBN 1-59059-126-7
  £43.00
  Reviewed in BCS Computer Bulletin November 2004
  Star rating **** (good)

• Access VBA Programming for Dummies
  Alan Simpson, published by Wiley
  ISBN 0-7645-7411-6
  £16.99 in 2006
  Assumes you know the basics of point-&-click Access, but does not assume that you know about programming. There are websites that accompany the book.
  The book is written in a nice friendly style but seems to have some fairly heavy topics right at the start of the book. Chapters 2, 4 and 5 in particular may probably be better used as references on a 'need to know' basis. Chapter 3 looks OK, then event code proper starts with chapter 6. There is no reference that I can find for Option Explicit - rather an odd omission. The Debugger is not discussed until right near the end - I think it should come a lot nearer the start. It doesn't really come at VBA from a business problem-solving point of view. On the plus side, it uses the newer ADO rather than DAO for Recordsets.
  My personal opinion is that the Step By Step book (or a 2000+ edition, if available) may be better as a first introduction to VBA for Access for those of you who have no knowledge at all of programming.
  Copies for the library ordered December 2006.

Known errors and omissions in Access 2000 Further Skills   Listed in page number order

You may find it useful to note these in your textbook before starting your work. As I add new errors to the list each year, I will date them, so that you can then spot which ones are new for that (academic) year.

These errors apply only (so far) to the units used in module CSCI1403.

Please email the site owner if you find any more.

See also Access and VBA FAQ page

Unit 1

1. The opening windows for Access 2003 are not as shown in McBride. Click here to see how to open Access 2003 and create a new database.

Unit 2

1. Pages 11 and 14, Unit 2. There are two task 3s

2. Page 11, Task 3. Reference to Fig 2.1 here should be to Fig 2.2 on page 12.

3. Page 12, Unit 2. The third item in the Membership Category table should read Membership Fee, not Membership No.

4. Page 12, Unit 2, Fig 2.2. The diagram here shows how the tables are joined, but it should be noted that there are several errors in the database design. The classical approach via Entity-Relationship diagrams, Functional Determinancy Diagrams and Table Types would not result in these tables. However, the design is useful for demonstrating features of Access.
   
   In particular, the Bookings table is poorly-designed:

   • The Bookings and Classes tables both show a time field. These times would appear to be for the same thing (booking time), so this may be an example of data redundancy. So the next question is: which of the times should be removed? It is a required field in the Bookings table, which seems logical, but the attribute more naturally ‘belongs’ to Class. Or is it that the Classes table holds the normal time and day whereas the Bookings table holds the actual time and day, which could be different.

   • The structure of the Classes table would appear to be that it shows details of regular classes except for the actual dates on which the classes run; e.g. a class runs on Tuesday nights at 19.00. It may be better for the User if this table also had the start and end dates and then the system would work out the individual bookings, but this is probably beyond the capacities of the Access ‘click and point’ features and would require VBA code, which is beyond the scope of this book. See the 'Getting Started' VBA Trainer section 8 for examples of using VBA for Bookings.

   • The Bookings table has a very weird layout – it is trying to cater for both Member and Class bookings in the one table, although both bookings are different types. The foreign key fields of Membership No and Class No are not required fields; they can’t be, as the table should have just one or the other of these fields. (In fact the table structure will allow you to enter neither or both for any one booking! A Record Validation Rule could be useful here). It does, however, allow for referential integrity checking with the relevant table when the foreign key is present. (Note that, with referential integrity enforced, it is possible to leave a foreign key field as Null, indicating that there is no match).

   • The actual process of booking via the Bookings form (Unit 16) does not check for double-booking, and double-bookings can therefore be made! Checking for double-booking probably requires VBA code, but is not even mentioned in the book. And the Class bookings have to be entered for each occurrence of a class. It can be done via a composite Index of Room, Date and Time but the Access error message here is very unfriendly, however you can use VBA to trap the error and display your own error message. See the 'Getting Started' VBA Trainer section 8.2.8 to see two methods that can be used to prevent double-bookings.

   •  I think a design where Class and Member bookings are in separate tables may well be better, as classes book for periods between two dates, on a set day of the week at a set time, and members book for odd hourly sessions. It also appears that Courts are booked by members only and the other rooms by classes. If it is required to see all bookings together, then this could be done via a UNION query, but SQL and UNION queries are also beyond the scope of the book.

   • So – for demonstrating what the book intends, this design works, after a fashion. But do not take it as an example of good database design!

    

      Some other items are:

• Many of the table names are inappropriate; a table name should indicate what is recorded by each row, not by the whole set of data for that table.

•  The tutor last name is repeated in both the Tutor and Class tables. It would be better to have a Tutor No field, so that the Centre can employ tutors with the same last name.

• While a Yes/No field may be appropriate for the Smoker field on the Membership table, a drop-down box (easily defined with the Look-up Wizard) may be more appropriate for the Sex field. Unit 22 suggests that an option group be used for the Membership Category; such a group would also be useful for Sex, and can be done with a Yes/No field.

• A drop-down box (again can be created with the look-up wizard) may be useful for the Category field on the Membership table, this time based on the Membership Category table. The LimitToList property should be set to Yes so that only the values in the list are used.

• it may also be better if there was a separate table for the Room/Hall/Court names, so that this could be used as a look-up for the Room/Hall/Court field and thus remove redundancy from the Bookings table. It could also ensure consistency throughout the system whenever the room names are used.

Unit 3

1. Page 15, Unit 3, Task 1. If you click on create table in design view (point 2) you will go straight to the second screen shown in Fig 3.3. If you click on the New button, then you will get the first screen in Fig 3.3 from which you can choose Design View to get to the second screen.
    

2. Page 19, Unit 3, Task 3. The end of this section mentions that if you want to have Access create your primary key it will create a key field called ID. This is correct if you do not have an AutoNumber field in your table, but if you already have an AutoNumber field then Access will assume that this to be the primary key.
    

3. Page 20, Unit 3, Fig 3.4. The screen shown here will not match what you have got. At this stage, you will only have the Membership table in your database.

Unit 4

1. Page 24. Unit 4, Task 1, Defining Field Properties. Points 3 and 4 for the Category No properties. The change of datatype to Byte is required here because the Primary Key for the Category table (see page 242) is a Number field, set to Byte. Later in the book you will need to create a relationship between the Membership and Category tables, and you will not be able to do it if the datatypes or field sizes are different. See point 2 in I've chosen referential integrity when setting up a relationship, but get an error message - why?

2. Page 25. Unit 4, Creating Custom Display Formats. Date. New feature for Access 2000.  If you look at the standard display format for Short Date you will see that it shows only 2 digits for the year - the example shown in the drop-down box for the table field property box is 19/6/94. But you can now set this to show 4-digit years, via Tools-->Options, General tab, and click in box Use four-digit year formatting. Simple!

3. Page 25. Unit 4, Creating Custom Display Formats. Time. The book states that the placeholder for minutes is 'm' - for example enter   hh:mm   to display time as hours and minutes (e.g. as 10:05). The correct placeholder is actually n (not m) but if you use m Access will correct it for you automatically (try it and see what happens)!

Unit 5

1. Page 30. Unit 5, Fig 5.1.  The instructions in task 1, point 3, ask you to create indexes for two date fields. These indexes are not, however, shown in the Figure.
    

2. Page 31, Unit 5, Task s. This should read Task 2.

Unit 7

1. Page 39. Unit 7, task 1, point 3.  Enter 'No' for the sex column. This is a Yes/No field, and has been set to show 'Female' for the value No.

Unit 9

1. Page 50. Unit 9, task 1, point 2.  The word Currency for the field Membership Fee should be in the Data Type Column, not the Description (looks like a tabbing error).
    

2. Page 52. Unit 9, after setting up the relationships. New feature for Access 2000. Open your Membership Category in table view and have a look at the contents. Notice the little + sign in the first column. Click on one of these and you will see a 'subdatasheet' showing you the table rows from the membership table of all members with this category number. Use Format-->Subdatasheet to 'expand' (show) or 'collapse' (hide) this information for all entries in the table. This is useful if you just want to check up on linked information for one entry, or to checked all linked information to check that a form/subform or grouped report is showing the correct information.

Unit 10

1. Page 55. Unit 10, task 1, point 4.  (also applies to the text just before the task). Two icons have not printed correctly here. They should be (in order): Next then Finish. Note also that the columns shown in Fig 10.3 are not in the suggested order in point 3 of the task - they will show in the order selected. The number of records in your table may not = 21; there are 20 records in the supplied Membership Data 97 file, and you may have added records of your own.

Unit 12

1. Page 68. Unit 12, task 2, query 1. (with thanks to the student who spotted this and told me).
   The dynaset shown here bears no relationship to the data shown in the back of the book! Your dynaset should look like:
    

   Addresses of Male Smokers

   Member No	Title	Lastname	Street	Town	County	Post Code
   13	Mr	Gray	4 The Parade	Chelmer	Cheshire	CH1 7ER
   7	Mr	Harris	55 Coven Road	Chelmer	Cheshire	CH3 8PS
   20	Mr	Jones	17 Mayfield Avenue	Chelmer	Cheshire	CH2 9OL
   1	Mr	Walker	16 Dovecot Close	Chelmer	Cheshire	CH2 6TR

    

2. Page 70. Unit 12, Task 3, query 1 - querying text fields. The dynaset shown here does not match the data shown at the back of the book. The records displayed should be for Member Nos 1 (Walker) and 16 (Robinson).
    

3. Page 70. Unit 12, Task 3, query 2 - logic using AND.
   Should look for 1992 joiners as there are several for this year but only one (Jason Perry) for 1996. [The previous version of the book used 1992 - goodness knows why this was changed]. Note that this also means changing your criteria test in point 3 to
        >=1/1/92 and <1/1/93     
   (the textbook seems a little mixed up here between 1992 and 1996!).
   And, as in many of these items, the dynaset in the book is not correct:
    

   When Joined

   Title	Firstname	Lastname	Telephone No
   Mrs	Sandra M	Davies	01778 891441
   Miss	Donna	Jameson
   Miss	Alison	Locker
   Mr	Liam	Locker
   Mr	Imran	Shangali	01777 561553
   Mr	Andrew J	Walker	01777 569236
   Mr	George W F	Weiner
   Ms	Aileen	Young	01778 894471

    

4. Page 71. Unit 12 Task 3, query 3, point 8.  Wrong data set is shown - it does not match the data in the back of the book.

Unit 13

1. Page 74. Unit 13 Task 3, screenprint under point 3. The 'Total' entry for the 'Age' column is incorrect. It should read Avg not Expression.
    

2. Page 76. Unit 13 Task 5, query 2. This query asks you to show the initials of the selected members, but there is no initials field on the table. I suspect this is an omission due to a change of mind somewhere along the way. Use the Firstname field instead. You may also like to look at How can I work out initials from a name field? (with thanks to the student who spotted this and told me).

Unit 14

1. Page 80. Task 2, at bottom of page. The reference to the Classes table should be to the Membership Category table.

Unit 19

1. Page 114. Task 1, second part of task to define the label report. You are asked to create a query to select members who joined since 1999, but, if you look at Quick Reference 1 at the back of the book you will see that there are no such members. So, either change your data, or use the year 1995 instead. With 1995 you will pick up three members (membership numbers 2, 3, 13) plus any other members that you may have added for yourself (for example, in Unit 15 Task 3).
    
2. Page 114. Task 1, second part of task to define the label report. Look at the postcode in the labels that you have created. Are they converted to upper case? My data was typed in as lower case, relying on the field format as defined in the table to display the data in upper case. This display works fine on forms and reports, but apparently NOT on labels. If you look at the labels in design view you will see how the data on the labels is formatted, and it is easy to add a conversion to upper case:
   
                     =Trim([County] & " " & UCase([Post Code]))
   
   The text in blue shows the addition needed to convert the postcode to upper case.
   It is interesting to note that there is no example screen-print of the labels in the textbook…

Unit 24.

1. Page 143. Task 4, Point 2. This should read: Select Yes for Group Header and Group Footer.

Unit 31.

1. Page 167. There are two Task 2s. The first task 2 is relevant to the Unit but the second one does not seem to be.

Unit 32

1. Page 172. Unit 32, task 2, point 4. The name of your renewal field may be [Date of Renewal] if you created it using table 3.1 in unit 3. Elsewhere in the book it is [Date of Last Renewal].
    

2. Page 172. Unit 32, task 2, point 6. The figure of 183 here refers to the number of days (approx.) in 6 months. Much of the data is older than that, and, as the book says, you may need to adjust your data to get some meaningful output from the query. Alternatively, simply look for a longer time-period. The SQL shown for this task in the labwork handout uses a figure of 2000 days (slightly less than 5.5 years). Experiment with different time-periods, looking carefully at the data in the table, and see what you get (better still, try to predict in advance, and see if you were right).
    

3. Pages 172, 173, Unit 32, Fig(s) 32.1. There are three figures all called 32.1 here. They should, of course, be 32.1, 32.2 and 32.3.

Appendices

1. Pages 237 to 243 - Quick Reference 1 - there is a table definition for the tutor table but no data. The data may well be buried in a unit somewhere (I can't find it). An Excel file with the data is available; ask your module tutors.

2. Page 238, the 'Town' field for the Membership table. The Town for Member 12 is shown as 'Branford'. This should be 'Bradford'.

3. Page 238, the 'sporting interests' details for the membership table. The tabbing is incorrect for member numbers 5, 7, 15, For example, member 5 has interests of 'Aerobics, squash yes'. This should read' Aerobics, squash' for the sporting interests, 'yes' for smoker and 'No' for Sex.

4. Page 240 - the booking table is of a rather strange design and the data here is confusing. If the booking is for a member, put the Membership/Class No shown in the Membership No column. If the booking is for a class, put it in the Class No column.

5. Page 240 - booking number 22 is for 'Court 1' not 'Court!'.
    

6. Page 241 - the definition for the Bookings table has several lines that are incorrectly aligned, but you should be able to work out what the correct information should be.

7. Page 242 - you are instructed to use a data type of Number with a size of Byte for the Category No. However, if you have used Autonumber (size LongInt) for the Category Number in the category table, you will also need to use LongInt here or you may not be able to join the tables. (This seems to cause a problem in Access 2000, but is OK in XP (?) ). See also Access and VBA FAQ.