New Translator version released

Additional error handling added. One of our users submitted an error report he get, when Translator starts collecting text. As we found – this happens because VBA project was password protected. Now Translator shows a following error message in this case “Can't create new module. Please check that your MDB file is not read-only and you have permissions to add new module”. If you get this message – just try to add a new module to your MDB file and see what happens.

How to print relationship window

Allen Browne, Access MVP, has published a very useful tip for Access developers: Relationship Report with extended field information. Actually this is a code, which generates a new Access report to print relationship diagram, together with field’s type information. Well done, Allen!

Merge Access and Word

This is a very popular topic in Microsoft newsgroups discussions – how to merge Microsoft Access record or table to Microsoft Word template. There is a very nice solution by Albert D. Kallal, Access MVP - Super Easy Word Merge. Helen Feddema has published a lot of samples to run this task in Access Watch newsletter. Some samples you can also find in Microsoft Knowledge base.
Also you can use native Microsoft Word mail-merge mechanism.
If your are not sure, that your application user can have Word installed – then you can build mail merge solution based on Microsoft Richtext control, like our Messaging sample. Or using Stephen Lebans, Access MVP, RichText ActiveX control.
Happy coding!

Be careful with Kaspersky Anti-Virus!

Yesterday I got a phone call from my client, he told that a new version, I sent recently, does not work and produce a lot of errors. Weird! I changed only a query in last version, and did not even touch VBA code. So I start to ask him what he did before <g>, with his PC, and he told me, that he just installed (or upgraded) Kaspersky Anti-Virus. Yes, this is it! Kaspersky Anti-Virus blocks some VBA code in order to prevent macro viruses run. So this was a source of the problem. Fortunately – there is an option, where you can switch off VBA blocking.

First time I noticed this long ago, in Access 2000 time. I have installed my application on a new PC with Office 2000 – and it did not work. I started to debug, and found that some function simply not working. It took me some time to find out that is was Kaspersky Anti-Virus – I had reregistered VBA libraries, reinstalled Office. I think default behavior should be – than Anti-Virus gives user a message that it blocks VBA, when application starts.

How to read ADP custom properties

This was really a nutshell. In MDB this is very easy – using currentdb() you get access to all available properties. There is no currentdb in ADP, there is a CurrentProject, but you can get only properties you add yourself, using CurrentProject.Properties. Also you have to start Access in order to read these properties – not a good idea for such a small application like VersionCheck.

Fortunately Google Groups knows everything! I found a following tread, discussing this issue. Sylvain Lafontaine (MVP) points to article Tales from the Script, which brings some light on this problem. Furthermore – there is an article in MS KB “Dsofile.dll lets you edit Office document properties without Office in Visual Basic .NET 2003 and in Visual Basic .NET 2002”, which shows samples using Dsofile.dll.

Currently I am improving our VersionCheck, so it can also work with ADP files, and let you know on results.

Command button on datasheet form

Tony D'Ambra published a nice tip – Command Button on a Datasheet Form.
Actually – this is a link button, but still very useful solution for Access developers. Well done!

More on Trees

A common table design to store hierarchical data structure – is a parent field, with reference to Primary Key, like in the picture below:



There is one more interesting way to store hierarchy – SQL Trees. You can look at Joe Celko articles to find out what it is:

A Look at SQL Trees
SQL Lessons
Nontraditional Databases
When Good Data Goes Bad

Later edition of this article - Trees in SQL, and code samples to it.

Few more articles on SQL Trees:
Storing Hierarchical Data in a Database
Trees in SQL: Nested Sets and Materialized Path
Trees in SQL databases

Michel Walsh, Access MVP, made a sample database in Access.

There are also Russian translations of these articles:
Древовидные структуры в SQL
Деревья в поддерживающих стандарты SQL базах данных

Personally – I never used such structure in my projects. Once I decided to use it, I’ve made a table structure, start building triggers to update data in new fields to correspond old, parent field, approach – the idea was to have both structures work together. But then project suddenly stopped. This was TehnoPro, a CAD system.

Want to grow a Tree?

Treeview control is a popular solution in Windows and WEB applications, used for navigation or selection. Personally I use a Microsoft Comctl32.ocx or MSComctl.ocx in Windows applications, which works fine in Access and VB. The only problem with this control – is version incompatibility (versions are binary incompatible). If you build your application with older version of control – then it can stop working with newer version. But this issue was mostly in early versions of Comctl32.ocx, and now it solved. Treeview sample shows some techniques using this control in Access.

Some programmers don’t like Microsoft controls, in this case you can look at other solutions – for example VBAccelerator.com offers some.

Other programmers don’t like ActiveX controls, but still there are some solutions for Treeview. For example SmartTree: Native Access Treeview Functionality by Lauren Quantrell – looks quite interesting. There is a sample download of this solution. Other solutions you can find at M&P page.

What about WEB? Now you can find a lot of staff for ASP/ASP.NET. Long ago I used Java Treeview, same you could see at MSDN Library page several years ago. Recently I used one from Obout Inc., and now I mostly use R.A.D. Treeview from Telerik. Well done control with AJAX loading on demand and good client API.

Screen capturing with TechSmith SnagIt

We all need to make screenshots. The way everybody knows – is to use PrintScreen and Paint or Word. But this takes some efforts to cut necessary region, save image, attach to email. Once we made a VB6 small program to capture windows, which I used last time. But…

I recently got a chance to install and try TechSmith SnagIt. This is awesome! It was never so easy to make screenshots! You can capture whole screen, window, custom region, and even web page, which does not fit on a screen! Here you see how screenshot of this blog looks like. Then you can add several effects, resize, add hotspots and more. Utility is integrated into Word/Excle/Outlook/PowerPoint and Internet Explorer. Check it out, I think this is definitely best offer on the market!

How to automatically update Access application

You have build a nice database application, installed it to number of users at the client, and now everybody happy. Client likes your application and wants to improve it, so you made a new version. How to install it at all users PC? Imagine you have 20 users on the same LAN and you made update every week!

Ok, if client has a good LAN Administrator – he can setup for example Microsoft Systems Management Server or something similar. But normally clients don’t have such smart Admin, so Access developer has to solve this. Here an overview what you can do.

Very popular solution is Auto FE Updater, built by Tony Toews, Access MVP.

FMS Inc also build a Total Access Startup. Very expensive!

If you like to have everything in Access – you can look at “Distributing an Updated Access Application to a Network” by Steve Schapel, Access MVP. Other solution in Database Journal - Automatically Deploy a New Access Client by Danny Lesandrini. And one more here.

Ok, we also made something in this area - Point VersionCheck. Besides Access files – it also works with EXE files. So basically – when we deliver our application to client – we always include VersionCheck in setup package. Actually we also have a version, which gets new version over internet, but this one we still testing.

Thanks to Joan Wild, Access MVP, for links!

Other this blog post on this topic - Alex & Access: Preparing Your Access 2003 Database for Deployment

Bound form’s unbound controls do not get updated

Bound form’s unbound controls do not get updated, when underlying recordsource has no records. I just noticed this in Access XP, and found a workaround. Say you have a continuous form, with 2 textboxes on form’s header to filter by date range. I also added few buttons to set these dates to predefined ranges – say this week, last month, etc. So once you have no records in underlying query, and press one of these buttons – values in the textboxes get changed (debug.print shows this), but form still shows old dates! The trick – is to run Me.Recalc at the end of button click event.