News, links, downloads, tips and tricks on Microsoft Access and related
Tuesday, October 31, 2006
Friday, October 27, 2006
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.
Wednesday, October 25, 2006
Access 97 'enabled' MDE databases are vulnerable!
Monday, October 23, 2006
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.
Thursday, October 19, 2006
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:
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=(ColumnHeader.Index – 1)
ListView.Sorted = True
Wednesday, October 18, 2006
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
If Len(strActiveControl) > 0 Then
Me(strActiveControl).BorderColor = vbRed
For Each ctl In Me.Controls
Select Case TypeName(ctl)
Case "TextBox", "Combobox"
If ctl.Name <> strActiveControl Then
ctl.BorderColor = 8421504
You can extend it by highlighting all controls, participating in filtering, when you apply filter several times.
Saturday, October 07, 2006
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
Thursday, October 05, 2006
Fading effect in Access forms
Wednesday, October 04, 2006
Wildcards in TSQL
Michael Kaplan tells about very interesting “feature” of SQL Server wildcards in Spanish collate.
Tuesday, October 03, 2006
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.