An MSForms treeview control now in Access

An MSForms (all VBA) treeview control you can now use in Access as any OCX control, e.g. insert into form or report. And it will work on 64-bit Access version! Thanks to Access MVP Ben Clothier, who made it happen!

An MSForms (all VBA) treeview control

Jan Karel Pieterse, Excel MVP, have built a treeview control on MSForms and VBA, which can be used as replacement for one from Microsoft Windows Common Controls. That means you can use it in both 32 and 64-bit Excel versions and also on Mac! As I see - you can also run it in Access, same way as in Excel, as dialog form, but not on Access form.

Long life Visual Basic!

Good article  by Chris Boss: Classic Visual Basic’s end marked a key change in software development. I still doing a lot of stuff on VB6 and of course VBA and I hope Microsoft will keep it running on new Windows versions

Autosize columns in datasheet

I have always used Stephen Lebans AutoColumnWidth to resize datasheet columns, but from recent discussion in Access MVPs email list I found a native Access property to autosize columns. According to KB article ACC: How to Use the ColumnWidth Property it was there since Access 1.1! So you just need to loop through all controls and set ColumnWidth property to -2:

 Dim ctl As Control
 For Each ctl In Me.CustomersDS.Form.Controls 
   Select Case ctl.ControlType 
      Case acTextBox, acComboBox 
         ctl.ColumnWidth = –2 
   End Select 
Next ctl

Very easy! And set it back to default width you can use .ColumnWidth = -1.

You can also autosize table and query datasheet using same way and  Screen.ActiveDatasheet form object

Stellar Phoenix Access Repair

I was recently asked to review Stellar Phoenix Access Repair tool. Using tool you can scan corrupted database to see what objects are still there, preview data in tables and SQL of queries, and repair database or selected objects. In most cases you can repair all tables and queries you see, but it is not true for other objects (at least for me).

I took a copy of Northwind.mdb and one of my largest frontend databases to test it, I tried to cut off parts of file, and run repair process.

When I cut off first 80 bytes I was not able to see (scan) objects in database, and even database was "repaired" – there were no objects in it, repaired database file becomes 96 kB. So file header is essential for that tool. Cutting off several bytes from the end does not affect database, cutting about 100kB makes it corrupted, but tool can scan most of objects, and repair all visible tables and queries. Cutting off more bytes gives less visible objects in database, and having file length less than 64 kb makes this tool useless.

I did not really managed to repair form or any other than tables/queries objects, in some cases tool tries to run Access in background with 100% CPU utilization, but looks like it hangs (or maybe it takes too much time).

One more useful option - Recover deleted records. If you have deleted records in the table, and then scan database using this tool, without compacting database – you can also recover deleted records. Tables, with deleted records appear as separate tables, like on then picture below.

As you see I have deleted records from Customers and Orders tables (and related Order Details), but tool recovered only Customers records, Orders and Order Details (deleted records) where empty, while in tool itself I saw deleted record in Orders (but not in Order Details). Still something to improve, this can be demanded feature.

Conclusion. Developer normally often makes a copy of frontend, means recovering forms and reports are not so important. Most sensitive objects in database are tables, and tool helps to repair them. Perhaps manual repair can give better result, but it is always good to try a tool like this first.

sp_BLITZ – SQL Server Takeover Script

sp_BLITZ – Stored procedure to do a fast SQL Server health check, it helps you rapidly assess configuration, security, health, and performance issues. Just copy script into SSMS query windows, run it to create SP and then run SP.

2003 verses 2000 format

Interesting post by Garry Robinson (and reply from Wayne Phillips):

I asked this question of Wayne Phillips from EverythingAccess.com
In your website you stated "When using Access objects (e.g. forms, reports, modules etc.) consider upgrading Access 2000 databases to Access 2002/2003. This is due to a much improved file-system for storing these objects in Access 2002/2003."
I am working on a huge system in a2000 format. Is it really worth the upgrade to 2003 ?

and Wayne answered
I generally go with 'if it ain't broke don't fix it' :) But if it is broke, or you're experiencing regular corruption, I definitely would recommend it. In Access 2000 the forms/reports/vba etc are all stored together in one big compound file called a DocFile, which is then itself split into 4000 byte or so chunks and each stored as records in the MSysAccessObjects table. This DocFile adds an extra layer of abstraction (and complexity) to the storage of Access-objects which is not needed and degrades performance. The 2002+ format removes the DocFile complexity and instead stores all the Access-objects data directly as separate records in the MSysAccessStorage table. Furthermore, in my experience, the 2000 format is more prone to corruption issues.