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

About Me Search
Name:Alex Dybenko

Location:Moscow, Russia

Monday, July 30, 2007

Commandbar with hidden control is deleted after compact in Access 2003

I have an old application, which was mostly used in Access 2000 recently, and there is a shortcut command bar where I show/hide controls depending on selected value.

Listbox has different communication items – phone, email, skype and if user right-click on, say skype, it shows buttons to start skype call, delete and copy number, other buttons – phone call, email, web are hidden. Only today I found that once you compact this application in Access 2003 – this commandbar disappear. Completely! Did anybody see this and have a solution? So far I implemented a following fix – I made a copy of commandbar and at application startup I check if commandbar exists, if not – the restore it from copy using Commandbarcontrol Copy method.

Technorati tags: ,

Friday, July 27, 2007

How to create Access 2002-2003 mdb in code

Doug Steele, Access MVP, has recently pointed to this conversation, discussing how to create Access mdb file in 2002-2003 format. The problem that dbEngine.CreateDatabase method can only create 2000 file format, which is equal to 2002-2003 format from Jet point of view, but not for Access. The trick is to set default file format in Access to 2002-2003 using SetOption method and then create mdb in Access using NewCurrentDatabase method. Below I posting routine from mentioned thread, just in case Google "forgets" it.

Private Sub CreateMDB2()

On Error GoTo ErrHandler

Dim oApp As Access.Application

Dim sFile As String, sDFF As String

Dim sSetting As String

'sSetting represents clicking on menu Options in Access

sSetting = "Default File Format"

Set oApp = New Access.Application

sDFF = oApp.GetOption(sSetting)

Call oApp.SetOption(sSetting, "10")

'9 is 2K mdb, 10 is XP mdb

sFile = CurrentProject.Path & "\Test.mdb"

Call oApp.NewCurrentDatabase(sFile)

Call oApp.SetOption(sSetting, sDFF)


If (oApp Is Nothing) Then



Set oApp = Nothing

End If

Exit Sub


MsgBox Err.Number & vbCrLf & Err.Description

Resume ExitHandler

End Sub

Technorati tags: , ,

Monday, July 23, 2007

Like operator on Integers in SQL Server

I sometimes use Like operator on numeric fields – integer, long, for me it looks convenient to build a filter on combobox selection, where you pass % (or * for Access) for all records and specific value to show filtered records. And seems that Jet nicely handle this (or perhaps somebody has different experience?). But recently I found that in SQL server, both 2000 and 2005 versions, this is quite inefficient operation. A sample query with condition Store Like ‘1’ runs 3 seconds, and with Store = 1 – only one! Explicit casting integer to char does not help, actually SQL Server doing the same operation itself. Now I have to adjust such filtering in several applications.


Technorati tags: ,

Monday, July 16, 2007

New site focuses on comparison between Microsoft® Access 2003 versus the new 2007 version, maintained by Oliver Stohr, Access MVP.


Technorati tags: