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: Access 2003, Commandbar

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) 
ExitHandler: 
If (oApp Is Nothing) Then 
'skip 
Else 
Set oApp = Nothing 
End If 
Exit Sub 
ErrHandler: 
MsgBox Err.Number & vbCrLf & Err.Description
Resume ExitHandler 
End Sub

Technorati tags: Access, format, createdatabase

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: SQL Server, Like operator

Access-Freak.com

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

 

Technorati tags: Access 2007