Exotic Guitar Tunings in Access

Here one more Access application with cool GUI - Exotic Guitar Tunings. Impressive!

When RefreshTitleBar is not working.

Recently I came into this issue – when I change AppTitle property and call Application.RefreshTitleBar method – it does not work at a client, no error, just nothing happens. After bit digging into this – I found a reason: method fails when you also have AppIcon property pointing to icon file, which does not exist on PC. Workaround is simple –first you have to read AppIcon property, check that icon file exists, if no – delete this property and then proceed with AppTitle.

Access 97 'enabled' MDE databases are vulnerable!

Wayne Phillips from EverythingAccess.com discovered one more security hole in Access MDE. Read his tutorial Access 97 'enabled' MDE databases are vulnerable! to find out how to better protect your MDE.

Import or Link mixed type data from Excel

There is an interesting post “External Data - Mixed Data Types” in Daily Dose of Excel blog, which certainly applies to Access also. If you would import (or link, or select) data from Excel spreadsheet with mixed type of data and one column has numbers and text, then Jet makes a guess of what type of column should be in resulting set. And quite often could happen that this guess is wrong. The trick is to add IMEX=1 to connection string, then all resulting columns will have text type. Read mentioned post for more info.

How to sort ListVeiw control column by date

Recently I saw an interesting article by Ken Getz “Sort Items in a ListView Control” in Advisor magazine. Article discusses using IComparer class of .NET to make proper comparison for dates and numbers. This is certainly the right way for .NET, by what about Access/VB/VBA – what we can do here? And here we can achieve the same result with less coding!

Say you have a ListView control on a form and in Column 3 you show dates in dd/mm/yyyy format. If you click on column header in order to sort by that column – control will sort it by text representation of dates, like:

01/01/2005
01/02/2006
01/03/2007
02/01/2005
03/01/2006
04/01/2007

So, dates are not sorted at all! Now you have to add one more column to your ListView, say this will be column number 8, with zero width – so it will be invisible for users. And when you fill ListView – you have to fill this column with dates in yyyymmdd format, or for example in long representation of date – CLng(varMyDate), in other words – the way it can be sorted correctly. And finally in ListView_ColumnClick event you need to check for ColumnHeader.Index, if our date column is clicked – then you have to sort Listview on column 8:

If (ColumnHeader.Index – 1) = 3 Then
    ListView.SortKey=8
Else
    ListView.SortKey=(ColumnHeader.Index – 1)
End If
ListView.Sorted = True

Easy!

How to highlight "Filter by Section" field

Access has ApplyFilter Event, which helps us to do this. Here a sample code, which changes border of ActiveControl to red and other controls to default color. And when you remove filter – it will set all controls border color to default.

Private Sub Form_ApplyFilter(Cancel As Integer, _
            ApplyType As Integer)
Dim ctl As Access.Control, _
strActiveControl As String

If ApplyType = 1 Then 'Apply filter
strActiveControl = Screen.ActiveControl.Name
End If
If Len(strActiveControl) > 0 Then
Me(strActiveControl).BorderColor = vbRed
End If
For Each ctl In Me.Controls
Select Case TypeName(ctl)
Case "TextBox", "Combobox"
If ctl.Name <> strActiveControl Then
ctl.BorderColor = 8421504
End If
End Select
Next ctl
End Sub

You can extend it by highlighting all controls, participating in filtering, when you apply filter several times.

Fading Popup Forms in Access. Part 2

And here one more sample by Chris O'Brien. I saw this sample before with one more trick – how to hide main Access window, so your Access application can look like VB or whatever exe standalone application. I think you can easy add this code by yourself if you pass hwnd of main Access window to FadeForm sub (don’t forget to open popup form first – event everything will gone):

FadeForm application.hWndAccessApp, 0

Fading effect in Access forms

Graham R Search, Access MVP, has a very cool sample of how to fade popup forms in and out with transparency.

Wildcards in TSQL

Michael Kaplan tells about very interesting “feature” of SQL Server wildcards in Spanish collate.

Access and Chemistry

Access is a great tool to develop database applications. But not only! Sometimes you can not imagine how people can use Access. Here a great example - what you can do in Access - Periodic Table of Elements.

Got one more MVP award!

Yesterday I got email:

Congratulations! We are pleased to present you with the 2007 Microsoft® MVP Award!

Great! This means that I will be one more year with MVP family and will have opportunity to meet old and new friends (and perhaps Bill Gates!) at Global Summit and other events!