Optimizing Microsoft Office Access Applications Linked to SQL Server

New article was published on MSDN site - Optimizing Microsoft Office Access Applications Linked to SQL Server

Access 2007 compact bug

Several people already reported in newsgroups, that when they compact Access 2007 database – Access 2007 shows a message, that database will be saved as MDB and then it gone. Completely! Don’t know if this is a real bug, but Access 2007 users should be aware of this misbehavior. While we get some patch or workaround for this – I suggest to make a copy of database before compacting it, like previous version of Jet did, and if compact fails – then you have a backup.

If you get this problem – try to look at Temp folder, perhaps something left there...

How to build a Ribbon in Access 2007

Gunter Avenius, Access MVP, has setup a new site with lot of information about Access 2007 Ribbon – how to build one, customize it, capture events, etc.

Access 2007 specifications

Access 2007 specifications, not so many changes since 2003, database file size limit still 2 GB

How to retrieve data from web server

This can be done very easy using MSXML library, which is installed on any modern Windows OS. Below you can find a code to get exchange rates for USD from web server of Central Bank of Russia. You can easy modify code for your needs by changing sUrlRequest variable.

Function GetRateCBR(dDate As Date) As String
Dim sUrlRequest, intTry As Integer, _
    strResponse As String
Dim oXMLHTTP As Object
Dim oResponse As Object

Set oResponse = CreateObject("MSXML2.DOMDocument")

'Build URL for request
sUrlRequest = _
    "http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=" _
    & Format(dDate, "dd.mm.yyyy") _
    & "&date_req2=" & Format(dDate, "dd.mm.yyyy") _
    & "&VAL_NM_RQ=" & "R01235"

'Try to get a response, 10 tries
intTry = 1
Do Until intTry > 10
    Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
    oXMLHTTP.Open "GET", sUrlRequest, False
    oXMLHTTP.send
    If oXMLHTTP.Status = 200 Then
        If oResponse.loadXML(oXMLHTTP.responseText) Then _
            Exit Do
    End If
    If Not oXMLHTTP Is Nothing Then oXMLHTTP.abort: _
        Set oXMLHTTP = Nothing
    DoEvents
    intTry = intTry + 1
Loop
If Not oXMLHTTP Is Nothing Then oXMLHTTP.abort: _
    Set oXMLHTTP = Nothing
If intTry <= 10 Then
     GetRateCBR = Mid$(oResponse.Text, 3)
End If
If Not oResponse Is Nothing Then oResponse.abort: _
    Set oResponse = Nothing
End Function

Gridlines with PrintLine Class

Recently I had to make a report with borders around textboxes in detail section, some kind of invoice report. Some of textboxes have CanGrow property set to True, and in case of long text in one of textboxes - gridlines become looking very ugly. Fortunately I found Stephen Lebans (Access MVP) PrintLine Class, which helped me to make gridlines look nice in a few minutes! Here the code I added to report’s Open Event, it only draw lines in detail section:

Set mPl = New clsPrintLinesV2
mPl.VerticalLines = False
mPl.Border = False
mPl.InitSections Me
mPl.SelectControlsMode = True
 With mPl.plSection(acDetail)
      .VerticalLinesWidth = 10
      .VerticalLines = True
      .VerticalLinesColor = vbBlack
      .VerticalLineMargin = 0
      .BorderWidth = 10
      .Border = True
      .BorderColor = vbBlack
      .RightBorderPosition = Me.Sum.Left + Me.Sum.Width
End With

Thanks Stephen for your code, very cool!

Auto click Yes to Outlook security prompt

Here one more way to Get rid of security alerts in Outlook – there is a VB code to click Yes on security prompt, can be also adopted for Access VBA.