Do not forget to download a copy of VB6...

... if you MSDN Subscriber. Visual Basic 6 (and whole Visual Studio 6) will gone from MSDN Downloads in June 2006. Microsoft will continue to support products throughout each product’s support lifecycle, but will no longer offer these products.

How to deploy Access project (ADP)

Once you ready to deploy a new version of your Access project (ADP/ADE) – you should delete existing connection string, else, when Access starts the project on client’s PC - it will produce a message that it can not connect to SQL Server. The trick – is to clear connection string using Application.CurrentProject.OpenConnection "". Also you can look at Microsoft KB article How to deploy an Access 2003 project that connects to an existing SQL Server 2000 database for more details on ADP deployment.

More rules for naming in Access

As I already wrote – developer could have problems, if he uses non-English characters in object names and VBA code. What else developer should aware? First of all - List of reserved words in Access 2002 and Access 2003 and List of Microsoft Jet 4.0 reserved words, these two always good to have in mind and avoid using reserved words. Also do not forget about Special characters that you must avoid when you work with Access databases.

You can completely avoid using reserved words, if you follow some naming convention. I personally prefer using Reddick VBA Naming Conventions, which actually is recommended in our bible - Access Developer's Handbook, by Litwin Getz, et al. Access MVP Tony Toews published his Table and Field Naming Conventions, which also good to follow.

Another Access MVP Jeff Conrad has a page with all latest links to naming convention articles, which is good to check from time to time.

Why do we use Access to develop custom solution?

Luke Chung, President of FMS, wrote a great article “Database Evolution: Microsoft® Access within an Organization's Database Strategy”, a number of perfect arguments to show our customers and their IT guys when they ask “Why Access?” I think with new Access version we could add some more arguments, for example we can publish Access data on web server in few clicks!

Replacement for Linked Tables Manager

David Fenton has built a Reconnect Utility Database – good replacement for native Linked Tables Manager, so you can connect your tables to multiple backends, even different types of backends, not just Jet.

AddItem and RemoveItem for Listboxes in Access 97/2000

Access XP and 2003 listboxes already have these methods. I made 2 functions which manipulate Listbox’ RowSource property. You have to set Listbox Row Source Type property to Value List and then you can add items using:
ListBox1.RowSource = AddItem(ListBox1.RowSource, "key1" & ";" & "New Item", 0)
And remove:
ListBox1.RowSource = RemoveItem(ListBox1.RowSource, 0)

These 2 functions assumes, that Listbox has 2 columns. For one-column code version please look at Update for "AddItem and RemoveItem for Listboxes in Access 97/2000"

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, ";")
            Pos2 = InStr(Pos2 + 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, ";")
        If Pos1 > 0 Then
            Pos1 = InStr(Pos1 + 1, strRowSource, ";")
        End If
    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

How to clear SQL Server database

Database Journal just published a very useful script to Truncate Database by Shane Lively. What I found also interesting there – that you can disable constraints/triggers for certain table using ALTER TABLE MyTable NOCHECK CONSTRAINT ALL and ALTER TABLE MyTable DISABLE TRIGGER ALL, and reset identity seed using DBCC CHECKIDENT ("MyTable", RESEED, 0). But what I don’t understand – why author uses Delete instead of Truncate, which is faster and more efficient?

Preparing Your Access 2003 Database for Deployment

Two parts MSDN article, discussing several aspects developer can aware when he plan to deploy application:
Preparing Your Access 2003 Database for Deployment, Part 1
Preparing Your Access 2003 Database for Deployment, Part 2

I would like to add 3 more advises:

• Spit your application into FE and BE
  
• Implement a backup solution
  
• Consider using version controlling, for example our VersionCheck

Access to PDF conversion tool

Stephen Lebans, Access MVP, just posted a first beta of Access to PDF conversion tool. This is an Access 2003 database containing a function to convert Reports and Snapshot files to PDF documents. No PDF Printer driver is required. We all know now that Access 12 will natively support export to PDF, but still this is quite useful tool, as people likes using older versions and also like to output reports to PDF. Access 2000 and Access XP version coming soon.