One more free installer

CreateInstall – installer made by Russian company. Also available freeware version. Nothing mentioned about Access – but I guess it should work, as with any other installer.

Other post on this subject: Freeware Installers

Via AccessExtra

Min and Max functions

These 2 are missing in VBA, but I found them both useful:

Function Max(ByVal d1 As Date, ByVal d2 As Date) As Date
    Max = d1
    If d2 > d1 Then Max = d2
End Function

Function Min(ByVal d1 As Date, ByVal d2 As Date) As Date
    Min = d1
    If d2 < d1 Then Min = d2
End Function

How to get into Outlook OST file

It can happen that Exchange server get crashed, together with active directory, and you have only access to OST file, as a file. What you can do? There is a Recovery for Exchange utility, which converts OST file to PST. If you know other tools, which works for you – please let me know in comments.

I personally had this problem once, but I often do backups to PST files, as well I read same mailbox in Outlook Express via IMAP. But unfortunately – not everybody do the same…

Live test: Reverse engineering MDE to MDB

Recently I was contacted by Karl Donaubauer, Access MVP, who assisting both German and Italian Access community. He mentioned that it was a discussion in Italian Access newsgroup about iTechMasters MDE to MDB Conversion Service. Italian colleagues guess that it's a difficult and time consuming task for iTechMasters to get the source code out of the MDE, and they think that an MDE is still safe because it would be very expensive to get the source code out. So I decided to make one more test. And we asked in newsgroups to prepare a test MDE with 10 VBA modules.

MDE was ready, and at agreed time I sent it to iTechMasters. Less than in 30 minutes I got MDB back. You can download and look at all 3 files (867 KB)

Here few comments from Karl Donaubauer
I can confirm that they've done an excellent work. The MDB is completely functional. There are only slight differences to the original file:

• certainly the comments are gone


• certainly the beautiful constant names are replaced by the values


• sometimes loops and other structures are replaced by different versions:Do Loop --> While Wend, Select Case --> If Then etc.


• Public Property Get CurDbC() As DAO.Database--> Public Function CurDbC() As DAO.Database

Nothing that would change the behavior of the app.

BTW, now iTechMasters offers a MDE Unlocker, so you can get access to forms design instantly by yourself.

And finally – some good news. Currently iTechMasters working on MDE protector, which helps to make MDE more protected from reverse engineering or decompiling.

Update for “AddItem and RemoveItem for Listboxes in Access 97/2000”

Some update on previous article. The function I made for ListBox/ComboBox with 2 columns. In order to have it working for one-column controls – you have to remove 4 lines, and here the final code:

Public Function RemoveItem(strRowSource As String, _
                    lngItemNum As Integer) As String
    Dim Pos1 As Long, strResult As String, Pos2 As Long
    Dim lngCount As Long, i As Integer
  
    strResult = strRowSource
    Pos1 = 1
    For i = 0 To lngItemNum
        If (Pos1 > 0) And (i = lngItemNum) Then
            Pos2 = InStr(Pos1 + 1, strRowSource, ";")
            If Pos2 = 0 Then Pos2 = Len(strRowSource)
            strResult = Left(strRowSource, Pos1 - 1)
            If Len(strResult) > 0 Then _
                strResult = strResult & ";"
            strResult = strResult & _
                Mid(strRowSource, Pos2 + 1)
            If Right(strResult, 1) = ";" Then _
                strResult = _
                    Left(strResult, Len(strResult) - 1)
        End If
      
        Pos1 = InStr(Pos1 + 1, strRowSource, ";")
    Next i
  
    RemoveItem = strResult
End Function
Public Function AddItem(strRowSource As String, _
        strItem As String, _
        lngItemNum As Integer) As String
    Dim Pos1 As Long, strResult As String, Pos2 As Long
    Dim lngCount As Long, i As Integer
  
    strResult = strRowSource
    Pos1 = 1
    For i = 0 To lngItemNum
        If (Pos1 > 0) And (i = lngItemNum) Then
            strResult = Left(strRowSource, Pos1 - 1)
            If Len(strResult) > 0 Then _
                strResult = strResult & ";"
            strResult = strResult & strItem
            If Len(strResult) > 0 Then _
                strResult = strResult & ";"
            If Pos1 > 1 Then Pos1 = Pos1 + 1
            strResult = strResult & _
                Mid(strRowSource, Pos1)
            If Right(strResult, 1) = ";" Then _
                strResult = _
                    Left(strResult, Len(strResult) - 1)
            Exit For
        End If
      
        Pos1 = InStr(Pos1 + 1, strRowSource, ";")
        If Pos1 > 0 Then
            Pos1 = InStr(Pos1 + 1, strRowSource, ";")
            If Pos1 = 0 Then Pos1 = Len(strRowSource) + 1
        End If
    Next i
  
    AddItem = strResult
End Function

Thanks to our reader Bob, who pointed to this issue!

How to export objects to secured database

Unfortunately, if you try to use DoCmd.CopyObject or DoCmd.TransferDatabase with secured database (secured with database password) – it will fail. The trick – is to open this secured database before export. Here a sample code:

Sub ExportToSecuredDB()
    Dim strDestinationMDB As String
    Dim dbsData As DAO.Database

    strDestinationMDB = "C:\Passworded.mdb"

    Set dbsData = DBEngine.OpenDatabase(strDestinationMDB, _
        False, False, ";pwd=MyPassword")

    'Export form
    DoCmd.CopyObject strDestinationMDB, "Form1", acForm, "Form1"

    'Export table
    DoCmd.TransferDatabase acExport, _
        "Microsoft Access", _
        strDestinationMDB, _
        acTable, _
        "Table1", _
        "Table1"
     'Close secured database
    dbsData.Close
    Set dbsData = Nothing

End Sub

And here about the same, but for Excel:
How to import password-protected Excel spreadsheet

Access – the best RAD

A great comment from Tony D'Ambra:
Access … “it is simply the best RAD for desktop databases”
I would like to add – Access also the best RAD for client-server databases! If server database is SQL Server :-)

Access 2007 in action

Microsoft has published a nice demo on Access 2007, which shows benefits upgrading to it. But unfortunately – it is mostly focusing on moving from Excel to Access. More details on new features you can find at Top 10 Benefits of Microsoft Office Access 2007 page.

Access RunCommand Constants

Everything about Access RunCommand Constants you can find at Terry Wickenden “Conquer Access RunCommand Constants” site

How to open document in OLE field...

In associated application window. Say you have the same setup, like in this sample. To open document – you need to run following code:

Me.oleExcelObj.Verb = acOLEVerbOpen
Me.oleExcelObj.Action = acOLEActivate

Jet is dead?

2 weeks ago I was in Cambridge, UK, on MVP Open Days. It was great! Ok, one night we had an opportunity to drink a beer with Clemens Vasters in a pub, who recently joined Microsoft and now working in WCF area. So we had a good chat (and good beer) with our Russian MVPs, who are mostly .Net developers. Then he asked me, what is my expertise – Access, oh, Access, you know – Jet is dead! ??? The further questions brought some light on this – Clemens personally thinks that Jet does not have future, the only database engine should be SQL Server. Well, I can not agree with Clemens, I think Jet is a great engine, and looks like Access product team thinks the same way, as you can see in Erik Rucker blog. Or, perhaps, the name Jet is dead, but engine will get a new life under new name?

Windows Scripting Host and Regular Expressions

Howard Tanner, a guest author in ACCESS Watch, put some light on Regular Expressions syntax in last issue. Must admit that RE syntax was always unfamiliar to me, so I found this article quite useful.