Sunday, June 26, 2011

Hide Your Input, and it’s Pretty Common

Doug Steele looks at how you can program your own InputBox, as well as how to programmatically call a couple of the standard Windows dialogs (the Color and Font dialogs),  Read the article here

Tuesday, June 21, 2011

Error Trapping with a Code Builder

If you code for a living, you know that error trapping is a drag. But its also an essential part of any serious Access development. This article is a heavy read but it will give you an idea about how to manipulate lots of code using code rather than cutting and pasting.  Read the article here

Peter Vogel's take on writing easy to read code and error handling

Peter Vogel takes a quick look at two topics that will help you create code that can be maintained: error handling and program documentation.  Here is the article in PDF format

Sunday, June 19, 2011

Dates, Data Access, and Presentation

Peter Vogel looks at a problem in managing dates and displaying information using conditional formatting. He starts with a solution to the problem, but uses that as a springboard to discuss what processing should be done in the different parts of your application.  Read the pdf article here

Tuesday, June 14, 2011

Navigation Through Recursion: Part 2

In this article, Christopher Weber demonstrates recursive query and reporting techniques he uses to generate a tree navigation map of the database. Along the way, he builds a reusable module that uses Access graphics to draw hierarchies in reports.  Read the article here

Navigation Through Recursion: Part 1

Christopher Weber takes us through a navigation map generating algorithm he uses to populate a table that describes how the forms and reports in an Access database relate to each other. This is a precursor to discussions on recursion theory in part 2 of this article..   Read the first article here

Reporting from Two Tables with Common Data

This month, Peter Vogel looks at reporting from two tables where one table overrides entries in the other table.

Here is the problem: I have a master table of data that I want to report on. However, I also have a table of daily data. Where there’s a matching record in the daily table for a record in the master table, I want to use the daily table’s record.  Read the article for the solution.

Blog On Your Mobile

If you want to read this blog on your mobile, use this address

Sunday, June 05, 2011

Is Dropbox useful for an Access Developer / Office Power User

Introduction: For quite a while now I had been contemplating a very popular specialised online tool called DropBox for one of our important projects.  Finally I have taken the plunge and I like what I see. This article explains how I think it is useful for developers and users of MS Access and Office.
Figure 1
Online Backup:  Dropbox is a secure file syncing product that moves the latest version of your file to the Internet as soon as it is saved on your hard disk (see figure 1). It does this (as far as I can see) as follows. When the file is saved / created, the online DropBox database is updated. Then DropBox goes about moving the files. You have the ability to pause the movement of the files and you can also make it move the files at a slow bit rate. DropBox will also slow down the movement of the files if it senses other Internet activity going on. Once the files are on the Internet, you can browse the files using a Web Interface.  You can store upto 100 gigabytes on the Internet as a single user.  There is a corporate plan as well that is far more extensive.
Figure 2
Two Computers: Probably the most appropriate thing for developers is the fact that you can use the same DropBox account on two computers and have files synced between the two computers plus have the files saved on the web (as in figure 2). This only works on files that are located in a folder tree that has a folder named DropBox at the top.  You cannot rename DropBox folder but all the other folders are yours to arrange as you will. To show you how smart this is, consider that you have a powerful desktop for most of your development and a lightweight laptop for field visits and occasional work. When both machines are turned on and you are logged in to an account on one of those computers, DropBox recognises when the Local Area Network is available and synchronises the computers quickly using that.  This will allow you to start developing on the beast (desktop) rather than the dainty little laptop. The visual clues that DropBox provides makes this pretty seamless. Remember that working on two computers on an Access database or a Excel spreadsheet in this environment relies on one thing. The SYNCING BETWEEN THE COMPUTERS MUST BE COMPLETE otherwise you are working on the old version.  The LAN syncing makes this quite practical and the Internet sync in this case is your backup and can progress in the background.  This functionality is a very good reason for having DropBox.  

Folders: DropBox will happily handle folders and subfolders and if you want to get smart you can either stop the syncing of a folder or cut and paste the folder back into your normal environment when you are finished with a project.

Inviting Another Person: In your DropBox folder, you can right click on a folder and invite a friend to share that folder. DropBox generates a special link which is sent in an email to that person. When they accept the invitation and install DropBox on their computer, that folder will end up in their DropBox folder. From then on if either of you change, delete or add a file, hey presto, that change moves to your computer and your folder on the web. You can later remove that person from sharing the folder at a later date.

If You Both Change The Same File At Once:  I believe that I am just about smart enough not to change a file if I have already changed it on another computer and am waiting for it to sync. I am not smart enough to know when someone else is modifying a file at the same time as me. So to my simple mind, you need simple rules to make this environment work. One rule that may be easy to manage is that one person does 95% of the work on the files and you simply have the files on your computer for help when they need it. At this stage you would notify them that you are going to change the files. The risk of this is reduced as DropBox maintains all the modified files online for a month. It also makes it easy to find these files as shown in Figure 3. This could be appropriate for database prototyping.

Figure 3
Practical Implications For Access:  When you open a database in MS Access, DropBox thinks that you have changed the file even if nothing actually is changed. As a result the file is then synced to the web and any other shared computers. This clearly is only OK with smaller files and only OK when everyone understands the rules for opening the file. This is why you have to think this through carefully. Another consequence of opening the file is that a laccdb or a ldb file is created in the folder to manage object sharing in Access. This file is rapidly transmitted across the internet or LAN to other computers. If you are smart enough, this can act as a visual cue that the database is in use as in Figure 4.  I would not grapple with multiple person file sharing until everyone really understood what the issues are and can communicate file ownership rules effectively.

Transferring Files Using Public Folders:Dropbox automatically sets up a folder called Public. If you place a file in this folder, DropBox can generate a hyperlink to that file on the internet.  You can then add that hyperlink to an email and send it to someone to download the file. This link is open to anyone but the link is encrypted so you would need to have a copy of the email to download the file.  As soon as you delete the file from your local Public folder, the file is deleted from the web. This approach will reduce Inbox / Sent email bloat by 90%.

Why Not Try It Out:  To get started follow this link to and download the installation file or look at the video's that explain the product.  By following this link, you will receive some bonus free shared space. I recommend that you start DropBox gradually until you fully understand how it will benefit you and your associates. It is possible that you may only end up using DropBox for sharing photos and doing some backups.   I am sure that there are other products out there that do a similar job but this one is quick, simple, smart and well worth thinking about.  In a future edition of this newsletter, I will outline how we are using DropBox.

Garry Robinson

See Comments by Glen Lloyd"The other caution is that folder sharers are able to delete files, so when it matters, copy files to those folders, rather than move them there."