|
vb123.com |
|
|
Home Contact Us Order our Software
RSS &
Newsletter
Get Good Help
The
Workbench
Find out who has
your database open, start the correct version of Access, easy compacting and
backups, change startup options, mde compile, shutdown database
Read and Download
Garry's Blog
About The Editor
Garry Robinson
writes for a number of popular computer magazines, is now a book author and
has worked on 100+ Access databases. He is based in Sydney, Australia
or try our Aussie
|
Is Your Database Corrupt
?
Adding Database Tools To Excel Spreadsheets
by Garry Robinson
Microsoft Access supports Microsoft Excel Spreadsheets through the Link table manager. This article serves firstly to explain the techniques for utilizing this new functionality and also outlines the pros and cons of developing an Access to Excel application. Included with the demonstration is a Contact software database that allows you to manage Company and related Contacts and Call information. All the data is stored in an Excel spreadsheet and the Contact Manager has been written in Access.
WARNING: As of October 2005, an automatic upgrade for Access 2002 and
2003 made changes to Excel through linked tables impossible. If you rely on this
technology, your databases will fail when your users install this upgrade.
http://support.microsoft.com/kb/904953/
After applying the 2002 upgrade, all tables become read-only. More on this article in our Access Unlimited newsletter - Edition 61
If you wish to try the download to see what happens before and after the update, find it at the bottom of the article. This download is free.
Technology Introduction
Excel is a far more popular tool in the real world than Access and whilst most developers will find it a clumsy tool to develop in, the reality is that many systems are setup in Excel. Irrespective of the obvious technical merits of storing the base data in a database, spreadsheet users have learnt how to achieve good results in a spreadsheet and are generally enthusiastic about their own applications. This article shows that you can develop a conventional Access style interface for data in a spreadsheet. This interface can consist of most advanced queries and reports plus Access data management forms can be setup with some special visual basic code to allow deletion of data.
The Contact Manager Software and Data
The Excel 97 backend database is called EXCEL_BE.XLS Inside that spreadsheet are 4 worksheets that have been setup to allow Excel to act as a database. The Access 97 front end database is called EXCEL_FE.MDB and this database contains a introductory form, data management forms and sub-forms, a query and two related reports. The database was originally derived from the Access 97 contact manager database creation wizard. The data was then exported to Excel and remodeled to include a primary Companies table with two related Contacts and Calls made tables. The software was then adjusted to suit this new database structure and some new features were added to the software. When you first open up EXCEL_FE.MDB, you will need to re-link to the tables in the Excel database using the Access link table add-in.
Setting Up The Excel Spreadsheet
Initially you should configure the Excel Spreadsheet to a standard format. Figure 1 shows the worksheets that have been setup for this application plus the setup for the top of the worksheets to database enable them.
Firstly you should establish a worksheet for each table of data. Give the worksheet the name of the table as you want it to appear in your Access application. In this database we have setup tables for Companies, Contacts, Calls and tlkpContactTypes The numbered discussion points refer to the highlighted numbers in Figure 1.
In the top row of the spreadsheet, enter the names of the data fields for the table. (good naming conventions apply equally in Excel tables as they do for Access so avoid blank spaces, unusual characters and keep the field names terse but meaningful). It helps to highlight the first row so that the user is aware that this row is important and is not data.
Enter a few lines of example data into the spreadsheet prior to starting on the Access application. This step is quite important as Excel scans the first few lines to establish what sort of data type it will allocate the column of data. It is recommended that you setup a dummy line of data for fields that have no representative information in the first few lines.
<< Click to see picture
Figure 1 The backend Excel database showing the preferred structure for database tables
Linking to Excel
The table links were established by starting up the link table wizard as per normal and select Microsoft Excel as per Figure 2. All the tables that are relevant are shown in the wizard. It is also possible to choose Excel range names to define tables but this will require management of the size of the range when you add records.
Figure 2 Choosing the Excel link option
Figure 3 Excel link tables
Once linked, the table container will appear as in Figure 3. You may want to open the tables in design mode to see the data types.
The Field Type Conundrum
When establishing a data field in Access, we must choose whether a field is going to be a number, date or text and then what rules we want to apply to that field. The Excel linker tries to work all this out by sampling the first few lines of data in the Excel data table. To see an example of this, open the "Contact" table in design view in Access and notice that the field called LastMeetingDate has been established as a date field. This was hard to achieve as one of the Excel cells in this field tricked Access into interpreting that the field as a text type. The Author had to try many different styles of entries in Excel to convince Access that the field was date. But curiously, the dates in the "Calls" table were established as date fields automatically. This highlights the fickle nature of the field type classification algorithm.
The author has not found any actual documentation as to how Access establishes whether a Excel field type is text, number or date but it seems to be decided on by scanning the first few lines. Cell formatting in Excel can help to establish the favored date format for your application but the mixing of numbers and text can lead to confusion. The Author must admit that the rules that establish the field type in Access are very hard to understand.
Note: Formatting numbers as text in Excel
Select the cells you want to format.
On the Format menu, click Cells, and then click the Number tab.
In the Category list, click Text, and then click OK.
Enter the numbers in the formatted cells.
Queries And Reports
Once the links are established and working to a useful degree, you now can start adding functionality from Access. The most useful is select queries and reports. The SQL in Figure 4 illustrates the kind of technology that is not readily available in Excel that we take for granted in Access. I.e. An Outer Join query that retrieves the last call made to each company.
SELECT Calls.CompanyName, Last(CallDate) AS lastCall, WorkPhone, FaxNumber
FROM Companies
RIGHT JOIN Calls ON Companies.CompanyName = Calls.CompanyName
GROUP BY Calls.CompanyName, WorkPhone, FaxNumber;
Figure 4 "FX_LastCall" is a query that finds the last call that was made to each company
There are also 2 reports called "FXR_AlphabeticalContactListing" and "FXR_CallSummary"
Both of these use multi table queries for their record sources and show that all the normal Access report functionality can be deployed onto the Excel tables. If you were to only use Access for reporting and querying, you would be adding a lot of value to your Excel databases.
Modifying Data
If you want to start adding data management facilities through Access, you need to add visual basic code to give Excel full database functionality. The main issue is that the Excel linker will not allow you to delete a row of data. When deleting an Excel data row through Access, the following error occurs
Deleting data in a table is not supported by this ISAM
But Access will allow you to add new rows of data (INSERT) and change existing data (UPDATE). So the technique that was established to get around the delete record error was to set every field in the current row of data to null. See the code in Listing 5 to see how this is handled in the On Delete Event in the data management form.
Private Sub Form_Delete(Cancel As Integer)
' Using Excel as a backend database will not support deletions.
' So instead set all the fields in the current record to null and
' cancel the deletion
Me![ContactID] = Null
Me![FirstName] = Null
Me![LastName] = Null
…
Me![ContactTypeID] = Null
Me![ReferredBy] = Null
Me![Notes] = Null
Cancel = True
End Sub
Listing 5 On Delete Event in the sub form called "FX_CompanyContactsSFrm"
For changing and deleting the primary CompanyName data, you cannot fall back onto the Cascade Delete and Cascade Update features of the Access relationship model as each Excel table has to be managed independently. So a special form was setup to manage Company Renames and Deletes called "FX_CompanyMods". Open this form in design mode and review the code for renaming and deletions under the bulls-eye button. This technique also applies to managing one to many key fields in other databases such as dBase and Paradox which do not support cascading changes through Access. You will need to ensure that the user cannot modify the CompanyName on the main form after a new company entry has been Inserted. This was not coded in this application.
Managing The Excel Database
Whenever you delete a record through this Set All Fields to Null technique, you will find that a blank row will exist in Excel. You should go back to the Excel spreadsheet occasionally and sort the data on the key field to remove any blank lines. You will find this option in Excel under Menu Data … Sort
Conclusion
Unless you completely control the desktop computer environment that you work in, you will run into the Excel power user or supporter who will want to improve their Excel spreadsheet rather than transferring their existing system into Access. Rather than pushing database solutions as the only answer, consider using Excel as a backend database and improve the system with Access queries and reports. Also remember that the business intelligence within a spreadsheet can be expensive to replace with a database application as there generally are a lot of hidden rules inside a spreadsheet.
Unfortunately the way that Access decides the data field type in Excel is clumsy at best and unworkable at worst if you are mixing text and numbers in the one field. Only experimentation can solve some of these issues. Maybe there is a programmable way to control this problem and if so then deploying Access as a data manager for Excel could be a good solution for a lot of small desktop applications. So look into deploying the use of queries and reports with the likely hood of reasonable success but beware that extending your Access application to managing data may not be perfect. Despite those negative issues, our company has used this technique successfully for data entry for a client who wanted their results in an Excel spreadsheet.
Author Bio.
Garry Robinson is the founder of GR-FX Pty Limited, a company based in Sydney, Australia. The understanding of these methods of using Excel as a backend database came out of research into trying to expand Access skills into the other Microsoft Office products. Garry has written over 40 solutions in Access for clients in small business, mining, trucking and environmental industries.
Contact details … access@gr-fx.com +61 2 9665 2871 Web http://www.gr-fx.com/
This material was originally reprinted
Smart Access. Reprinted with permission from Pinnacle Publishing(http://www.pinpub.com).
This article first appeared in the November 1998 issue of Smart Access by Pinnacle and was written by Garry Robinson from GR-FX
Click here for the
download file
Note: The Access 97 version of this download is in the Access 97 Frozen pack
Published 1999-01