Daily Access Tips

If you’re new here, you may want to subscribe to my RSS feed. Thanks for visiting!

News Item From: Microsoft Access Team Blog | January 15, 2010

Today’s guest writer is Ric Lewis from the Access PM team. Here at the beginning of the year we wanted to take the opportunity to highlight our MVPs. These are folks whose tireless efforts produce the vibrant Access community, which ... Read more at Microsoft Access Team Blog –

Great list of helpful websites are contained in this well deserved recognition.

We all owe our gratitude to these fine professionals who supply so much help.

My thanks goes out to all who made this list and many more who have shared their knowledge on their Access sites.

I was involved with the Microsoft Access 2010 first non-public beta.  A non-disclosure agreement prevented me from discussing details but now there is a public version available.  This should not be placed on a production machine and also not on a machine having earlier versions of Access you need to use.  This version has the ability to publish an Access application to the web using “Sharepoint Access Services”.  I have heard from various sources that any time from March to June 2010 will be the first release.

Below are several references* so you can find out more about what is being released.

• *New features of Access 2010 – http://www.microsoft.com/office/2010/en/access/default.aspx
  

• *Sharepoint 2010 and Access 2010. http://www.youtube.com/watch?v=Dq-tDuPfgZc
  

• *Microsoft Office Access 2010 http://www.youtube.com/watch?v=P8cheNlRlh4
  

• *Microsoft Access 2010 Macro Designer http://www.youtube.com/watch?v=6zvbK9×8rGw
  

• *Design navigation UI with Access 2010 http://www.youtube.com/watch?v=vkMzkhVfK6Y
  

• *Access 2010 Layouts http://www.youtube.com/watch?v=1wtgk2nosJE
  

• *Access 2010 data macros http://www.youtube.com/watch?v=J3G0ivjpy9k
  

• *Microsoft office 2010- Access http://www.youtube.com/watch?v=qUsrAJ6B_D4

If you are planning to use the new Access Web Forms, you will want to learn the new macros that will be needed.  Any forms that will run in an internet browser, after publishing to Sharepoint, will not use VBA code and will only use Macros. These forms and reports will also require the form and report “Layouts” that you now find in Access 2007.

The new data macros and lookups will allow much of your business logic to reside on the back end data side where you can make changes inside a single central location.

If you simply want your end users to work in the Access program but have the data reside on a Sharepoint server, you may use all the forms and reports and VBA code you have developed, but still have the great advantage of using the internet as your “extremely wide area network” for sharing your Microsoft Access database data.  In this case, just publishing your Access tables to an internet Sharepoint server will do the trick.

Microsoft Online Services:  http://www.microsoft.com/online/support.mspx will have this hosting available next year.  Currently they are only charging about $6.00 a month for single person licenses for their other Sharepoint services now currently available.  We can only hope it will be that inexpensive when they offer “Access Sharepoint Services”.

Bob Heifler

Peter Vogel discusses Null Fields

I recall having numerous problems with this area until I got some help.  All Access developers who are not sure of the potential NULL problems and their solutions should read this article. It will save you several headaches!

Nulls give most database developers a lot of trouble. One example is that you can’t compare Nulls to anything and another is that a numeric field with Null in it won’t be less than, equal to, or greater than any other number. Yes Null fields are a difficult concept but you need to understand them to be a database expert.

Find the full article here.

Enjoy the articles,

Bob Heifler

In this article, Doug Steele demonstrates a useful formula to use with date calculations.

If I’ve got a date, can I determine the date of the Monday for the week that date is in? For instance, I’ve got a date of 17 November 2004 (which is a Wednesday), and I want to identify that the Monday of that week is 15 November 2004.

Find the full article here.

There is  another good article for you from Cindy Meister at above link when you click on “Access Answers: Date Tricks” in the link above.

Sometimes, as an Access developer you need a more powerful reporting tool than Access’s internal reporting engine. Microsoft Word can give you the power that you need. Cindy Meister’s article covers the topics that every Access developer needs to know about Microsoft Word.

Enjoy the articles,

Bob Heifler

Time to get some reporting basics in another classic article by Microsoft Access Chris Weber and Frank Sutherland

If you think that reports are boring, Chris Weber and Frank Sutherland are going to show you just how wrong you can be. Topics discussed include Reports on separate pages, showing group headers more clearly, memos reporting issues, overlapping sub-reports and report performance. They also throw in a tip to solve a common problem and show how to improve report performance.

Full Article Reprint Here.

Enjoy the article!

Bob Heifler

Michael Haag offers a tip this week on filling in the city and state after entering a zip code on a data entry MS Access form. Data is stored in a ‘Zipcodes’ table that contains 3 states for the New York area with a Dlookup function to retrieve the city and State.

Michael states “I think one of the main advantages is: The information doesn’t have to be stored in the “Customer” table, so it doesn’t take up space.” for Microsoft Access display. I think the main advantages are speed of data entry and elimination of spelling errors during data entry. MS Access allows for a Dlookup fuction to bring back data from a table or query.

He places in the control source property of the ‘City’ text box this:

=Dlookup(“[city]“,”zipcodes”,”zipcodes= ‘” & [Forms]![frmcustomer]![zipcode] & ” ‘ “)

He places in the control source property of the ‘State’ text box this:

=Dlookup(“[State]“,”zipcodes”,”zipcodes= ‘” & [Forms]![frmcustomer]![zipcode] & ” ‘ “)

An alternate approach would use the ‘After Update’ method of the ‘Zipcode’ form control with this code:

Private Sub ZipCode_AfterUpdate()

If IsNull(Me.City) Then

Me.City = Dlookup(“[city]“, “zipcodes”, “zipcodes= ‘” & [ZipCode] & ” ‘ “)

End If

If IsNull(Me.State) Then

Me.State = Dlookup(“[State]“, “zipcodes”, “zipcodes= ‘” & [ZipCode] & ” ‘ “)

End If

End Sub

This second solution would have the City and State form controls bound to the Customer table or the code could be placed in the On Current Event of the form to handle unbound controls for the City and State Microsoft Access display of data.

Both approaches still require the maintenance of the Zipcode table. Perhaps an easy to maintain solution would involve changing the Zipcode form control to a combo box which is feed by a Group By query (See below) that is based on all prior Zip codes, Cities, and States entered in the Customer table (Not limited to list.) This has the advantage of no maintenance of a Zipcode table and speed of data entry, however it can potentially have two or more Zip, City, State combinations of misspelled City or State names.

The combo box SQL would be:

SELECT Customer.ZipCode, Customer.City, Customer.State

FROM Customer

GROUP BY Customer.ZipCode, Customer.City, Customer.State;

The after update for the combo box would be changed to:

Private Sub ZipCode_AfterUpdate()

If IsNull(Me.City) Then

Me.City = ZipCode.column(1)

End If

If IsNull(Me.State) Then

Me.State = ZipCode.column(2)

End If

End Sub

Try out one of these solutions and please enter your feedback in the comments of this blog posting (Below).

Thank you Michael for your tip and article suggestion.

First let’s start with the definition of a split MS Access Database:

A Microsoft Database that is split into a front-end database that contains the application (queries, forms, reports, macros, and modules) that are linked to MS Access tables stored in a back end shared database file containing only the data tables.

You will split MS Access Database when moving an application to a multi-user environment rather that have everyone using a single file on a shared network drive.  Doing otherwise puts your data at risk of corruption.  However, as you will find out in the next article, even a single user database will benefit from being split.

When you split a database, creating a front end and a back end, the back end Microsoft Database file is stored in a shared folder on a network drive and you link Microsoft Access data to MS Access front-end file. The front-end database file is distributed to each user’s desktop.  You would link Microsoft Access front-end to MS Access back-end, just prior to distribution. Using this design, each user has a copy of Microsoft Database program installed on their workstation machine along with their front-end application database file. Each user interacts with the data by using a local copy of the front-end Microsoft Database file. This reduces network traffic since the front-end does not need to be sent over the network path for each use, and allows the front-end database to contain settings or temporary data specific to the individual user. This split database design also allows development of the application, the front-end, to be totally independent of the data.  This allows for front-end to be easily replaced without affecting the data in the back end database. You will at that time link Microsoft Access front end file to MS Access back end file using the Linked Table Manager or some code to do it. The Microsoft Database program has two built-in utilities (Database Splitter and Linked Table Manager) to aid in your linking to MS Access data.  Look-up tables that rarely change their data are also possible items for the front-end. However, if you have tables with fields that look-up to other tables, those tables should be in the same back end database. Otherwise, if you open that source database and that table, it’s look-ups will not appear and this can cause data entry and data integrity problems.

The next article in this series will go over the risks when you do not split MS Access Database or link Microsoft Access front-end file to MS Access back end file.  The pros and cons of using a wizard to do the split for you will be discussed along with how to manually accomplish this procedure when the wizard would not do the job adequately.  At the end of the series we will discover code written by an Access MVP Microsoft Most Valuable Professional by the name of Dev Ashish. This code ensures that the data linking is working each time the program starts.  You will also be directed to a Microsoft KB Knowledge Base Article to further illustrate a manual method of splitting and linking your database.

In the next article you find out why it’s vital to Link Microsoft Access Application to MS Access back-end data file even if you only have one person using the database.

Continue to part 2: http://www.dailyaccesstips.com/why-link-microsoft-access-application-to-ms-access-backend-data-file-part-2-of-4/

How well your computer consultant understands your business computer requirements determine your total computer consulting costs by minimizing midstream changes, corrections and additions. Things not fully clarified can get altered or totally omitted, requiring more costly labor to correct or redesign.

Don’t leave it to chance.  Here is a checklist that covers the key areas to clarify for yourself and your computer consultant before discussion starts:

A list of the things you need to keep track of in your database and how they relate to each other is a good start but here is a more complete list to make sure nothing is falls between the cracks between you and your computer consultant.

1. What are your major categories of data and what specific data is stored under each category. Listing out your various business processes, and the steps required to accomplish them will help answer this.
2. How will want to filter your data to get a just what you need for a report or a screen view.
3. Any examples of reports or other output you may need or are currently using. (Microsoft Word can be used to illustrate a report design.)
4. Define what data validation or business rules are needed during data entry.
5. Any special requirements involving linking to or importing data from data sources outside of your business software.
6. Any financial accounting requirements or other legal / liability issues requiring additional data storage and output.
7. What kind of hardware system exists and is anyone supporting this.
8. Will data access require any remote access permissions?
9. The level of security access required for various areas of data viewing and editing among the individuals given permissions.
10. Current data entry media and formats currently being used if any exist.
11. Any data migration requirements from existing data sources, such as available formats form these sources

I can tell you that a consultant’s time might not always be billable on that first consultation, but he or she will appreciate the business owner who is prepared and can answer these basic questions.

I have found that it’s rare that a business owner is sufficiently prepared to start a discussion in this area unless these points have been thought out.

If you have had any experiences with computer business consultants that went sour or did not meet expectations, your comments and lessons learned are welcome.  We all need to learn more about this vital area and that includes me.

Bob Heifler

MyAccessProgram.com

I have programmed in Microsoft Access for over 14 years and have found the following items most important in creating the best possible database design. These were discovered to be the main items that caused slow performance, errors, user difficulties, and data corruption. Violation of any of these guidelines will result in some minor or major problems. Apply uniform design layouts for forms. This will help with the learning curve for employees new to the application.

To increase performance of form and drop down list loading, use stored queries for your record sources because they have already been optimized by Access. Note: the Access form wizard will create Select SQL statements for record sources, and these are not optimum.

Use validation rules to help prevent data entry errors by restricting what information can be entered in a field. Use a message box or onscreen area to let the user know what is needed. Using the field’s tag property is an easy way to encode the type(s) of validation each field needs, one can then loop through the fields collection to do the validations. Index the fields that you will need for criteria selection in your queries and fields used in queries that join the tables, to increase performance speed.

Optimize the data types to use the least amount of memory and storage and increase processing speed.

Always put code error handling in your routines so you have control of what logic to apply. A MDE file will stop executing without telling you or your user anything useful about the error. Don’t use macros (that do not allow error trapping) except for custom menus for the same reason.

Allow users only a copy of the front-end access program file, which contains everything but the data, never using the original front-end program file. This prevents the possibility of unforeseen corruption. Always split the Access application between a front-end and a back-end (Data). Place the back-end data MDB on the root of the network share, for quicker reads and writes. Compact data back-end on a regular basis. This will check to see if any table indexing is corrupted also if any data records are corrupt. This practice also reduces the file size.

Assign one person, and in his absence a second, to back up the data and store it off site on a regular basis without overwriting the last back up. How often you back up is determined by how much data are you willing to re-enter and how much time might have gone by until the loss is detected.

When compacting, make sure your subversion of Access is up to date so there is no reissuing of previously issued auto numbers, and that you make a back-up copy of the original MDB. Do not use Memo data type fields over a network. They have different indexing that is much more susceptible to corrupting during a write-back to the server. In 14 years, I have only seen record level table corruption of Memo data type fields. Use a main menu form or some other non-data entry form that is always kept open and is linked to a small application table. This will stop the repeated opening and closing of the LDB (record locking file) when each form is opened then close before opening another form. This will prevent a slowdown over a network connection.

Use auto number field for linking tables to dependent children tables. They are difficult for users to modify and offer better indexing performance.

Use lookup dropdown lists for text fields, which store the long integer auto number of the lookup table. If historical data is needed in a lookup table whose text is likely to change, ensure that the text value in the lookup table is also stored in the main table along with the long integer key of the lookup table.

Instead of using a multi-field key that prevents duplicate records, use code to check for a potential duplicate before allowing the record creation to continue. If automating other parts of Microsoft Office, use late binding so you do not have to worry about with what version of other Office software, such as Word, Excel or Outlook, is being used on the individual desktop machines.

Use the same name in both parent and child table, when using an auto number field from the master table to the corresponding long integer field in the dependent child table to add in maintenance and understanding of the relationships. Don’t use record sets in a multi-user environment unless the record set is read only snapshot and there are no updates that could conflict with the concurrency of other users. Solution, wrap the updates in a transaction which upon failure notifies of write conflicts and asked the user to get other users off before retrying.

Here are just a few more important points. Always use Option Explicit in VBA code to ensure no undeclared variables. Keep your Jet 4.0 Engine version up to date. On large record set updates, use record set operations with BeginTrans and CommitTrans to speed up record set updates by temporarily storing them in a memory buffer first. Finally, avoid right joins in queries if at all possible for performance unless program or business logic requires it.

Robert Heifler, Microsoft Access Programmer/Database Developer of 14 years – Email:info@MyAccessProgram.com – Please visit http://www.MyAccessProgram.com for free video tutorials.