News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Name:Alex Dybenko

Location:Moscow, Russia

Tuesday, August 16, 2005

Started a new blog

I have decided to make a new separate blog, so this one will be on English and related to Microsoft Access. Other will be on Russian and related to everything else, perhaps also Access, but for Russian developers only. So if you know Russian – you are welcome: Alex & Alfa.

Translator bug fixed

One of our customer have recently pointed to a bug in Translator – if you collect text strings, then delete a label from a form - Translator produce an error while you write translated text to this form. Actually Translator detects when some control was deleted, and then it set a TranslationNotUsed flag to false for this control. What was missing – is filtering for this flag during write process. Updated versions available at Translator page, registered users can request an updated version by emailing me or just commenting this blog post. For already translated applications you have to delete talxmdlTranslate module, so next time Translator will add a new one.

Import/export in Access while running with /Profile

If you start your Access application with /Profile startup option, and in your application you are using Import/Export facilities - you have to add few keys in registry in order to have Import/Export working.

  • First you have to look in registry under
    HKEY_LOCAL_MACHINE\ SOFTWARE\ Microsoft\ Office\ 11.0\ Access\ Jet\ 4.0\ ISAM Formats
    Find a subkey which you want to add to your profile, for example Excel 8.0
    Export desired subkey to a text (*.reg) file
  • Modify this text file so the path of the key will be:
    HKEY_LOCAL_MACHINE\ SOFTWARE\ $COMPANY$\ $PRODUCT$ is a location of your profile
  • Add information from modified reg file to your registry

Actually same applies to Report Formats and Clipboard formats options

Monday, August 15, 2005

Intellisense for Query Analyzer

Recently Ivan Bodyagin pointed to PromptSQL - Intellisense solution for SQL Server Query Analyzer. Strange that Microsoft did not make a native one yet. Perhaps Access 12 will have something better. Currently SQL editor in ADP is almost useless with it feature to reformat T-SQL.

Comment spam

Just got a first comment spam. Deleted. As I see there is no any good way to avoid such spam. Well, perhaps could be a filter, like Outlook Junk mail filter, and if somebody enter "spam-looks" comment it will notify me and ask for confirmation to post this comment.

Thursday, August 11, 2005

VB.NET refactoring

Don't know if we ever have VB.NET in Access, but anyway this is worth to look at: Mark Miller shows Carl Franklin refactoring features of VB.NET. Impressive!

Pass-through queries in Access subforms and subreports

Recently, while working on Access application as s front-end to SQL Server I came into a problem that you can not bind subreport (and subform) to pass-through query, if subreport linked to main report with Link Master/Child properties. Error message I get is: "You can't use a pass-through query or a non-fixed column crosstab query as a record source for a subform or subreport". Microsoft KB article says that you can bind subreport directly to linked table and then it will work. Yes, it will work, but imagine what happened for a large table? Or if you need to join 2 tables for this subreport? I think better approach is to use a local temporary tables - so just copy your filtered pass-through query to some local table and then use it as a report source.

Wednesday, August 03, 2005

How to Maximize Access Form

In Access this is easy - just run Docmd.Maximize. But once you maximized one form - all others get maximized also. Of course you can run Docmd.Restore, but then forms start to show a lot of visual effects. I think the best approach - is to size form to maximum available size, so it will be looked like it is maximized. Below a sample code, just paste it in a new module, and in Form's Load event add: MaximizeForm Me.hWnd
You can also use same approach for reports, a sample download available at Point Limited site.

Option Compare Database
Option Explicit
'** Constant Definitions:
Private Const GWL_STYLE& = (-16)
Private Const SW_SHOWNORMAL = 1
Private Const SW_SHOWMAXIMIZED = 3
'** Window Style Constants
Private Const WS_DLGFRAME& = &H400000
Private Const WS_THICKFRAME& = &H40000
'** Type Definitions:
Private Type RECT
x1 As Long
Y1 As Long
x2 As Long
Y2 As Long
End Type
'** Function Declarations:
Private Declare Function SetWindowLong Lib "user32" _
Alias "SetWindowLongA" (ByVal hWnd As Long, _
ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Private Declare Function GetWindowLong Lib "user32" _
Alias "GetWindowLongA" (ByVal hWnd As Long, _
ByVal nIndex As Long) As Long
Private Declare Function IsZoomed Lib "user32" _
(ByVal hWnd As Long) As Long
Private Declare Function ShowWindow Lib "user32" _
(ByVal hWnd As Long, ByVal _
nCmdShow As Long) As Long
Private Declare Function MoveWindow Lib "user32" _
(ByVal hWnd As Long, ByVal x As Long, ByVal y As Long, _
ByVal nWidth As Long, ByVal nHeight As Long, _
ByVal bRepaint As Long) As Long
Private Declare Function GetParent Lib "user32" _
(ByVal hWnd As Long) As Long
Private Declare Function GetClientRect Lib "user32" _
(ByVal hWnd As Long, lpRect As RECT) As Long
Public Function MaximizeForm(ByVal lngHwnd As Long)
Dim rpt As Access.Report
Dim WinStyle As Long
Dim lngRet As Long

WinStyle = GetWindowLong(lngHwnd, GWL_STYLE)
Call SetWindowLong(lngHwnd, GWL_STYLE, WinStyle)
If IsZoomed(lngHwnd) <> 0 Then
Call ShowWindow(lngHwnd, SW_SHOWNORMAL)
End If
Call GetClientRect(GetParent(lngHwnd), MR)
Call MoveWindow(lngHwnd, 0, 0, MR.x2 - MR.x1, MR.Y2 - MR.Y1, True)
End Function

Want to improve your blog?

Todd Mintz shares few ideas how To Be A Blogging Idol Instead Of An Idle Blogger. Few ideas already implemented!

Monday, August 01, 2005

How to create a GUID autonumber field with DAO

The problem that once you create field with dbGUID type, and assign a default value to it as GenGUID() it starts working, but on new row it shows "#Name". The trick is to set dbSystemField attribute flag for this field, then "#Name" will gone.
Here a procedure which Access MVP John Spencer build to demonstrate this:

Sub BuildGUIDAutonumber()
'Test Procedure

Dim dbany As DAO.Database
Dim tdefAny As DAO.TableDef
Dim fldAny As DAO.Field
Set dbany = CurrentDb()
On Error Resume Next
dbany.TableDefs.Delete "A__A"
On Error GoTo 0

Set tdefAny = dbany.CreateTableDef("A__A")
With tdefAny
Set fldAny = .CreateField("GUIDFld", dbGUID)
fldAny.Attributes = fldAny.Attributes Or dbSystemField
fldAny.Type = dbGUID
fldAny.Properties("DefaultValue") = "GenGUID()"
.Fields.Append fldAny
End With
dbany.TableDefs.Append tdefAny
End Sub