Error in loading DLL

Sometimes you can get strange "Error in loading DLL" error message running Access or other VBA application. In Access it happens when you call DAO methods or properties. First of all – try to register ACEDAO.DLL or DAO360.DLL, using regsvr32. Also you can try steps of this article: Microsoft Office Access: "Error in loading dll". But the easiest way to fix it at customer's PC - run Automatic Office repair:

• In Access 2003 run "Office Diagnostics" located in Help menu
• In Access 2007 click on Office icon, Access Options, Resources and run Microsoft Office Diagnostics
• In Access 2010/2013 open Program and features in Control Panel, Right-click on Office installation, Change and run Repair

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.