Migration considerations for Access 2007

MSDN article Migration considerations for Access 2007 lists all issues you can have when upgrading to new Access version.

AOIndex is not an index in this table

Dirk Goldgar, Access MVP,  have recently made a procedure to fix "AOIndex is not an index in this table" corruption in Access database. This is what you can certainly to try in case you get such error.

Access 2007 cool features

You have to see this presentation – Tim Getsch shows new cool Access 2007 features at Microsoft Office System Developers Conference 2006. Download available from this page.

ActiveX controls on Tab control page

If you insert an ActiveX control on Tab control page, like any other Access control, - then you could have a problem, like ActiveX control jumps from place you insert it, or control itself is not displayed properly. The workaround – is to insert control directly on a form, move and size it at desired place, “Bring it to front” above the tab control and then set it visible property to False. Now you can use Tab control Change event to make ActiveX control visible, when certain page is selected:

Private Sub TabCtl0_Change()
    Select Case Me.TabCtl0.Pages(Me.TabCtl0).Name
        Case "MyPageWithActiveXcontrol"
            Me.MyControl.Visible = True
        Case Else
            Me.MyControl.Visible = False
    End Select
End Sub

How to compact password protected database

Normally you can use DBEngine.CompactDatabase method, but for password protected database you have to specify password:

DBEngine.CompactDatabase "C:\SomePath\MyDatabase.mdb", _
"C:\SomePath\MyDatabase_1.mdb ",,,";pwd=SomePassword".

You can also set a new password for destination database in second argument.

Note that you can not compact database into same destination like in Access, but you can compact to new file name, then delete original and move compacted database to original location

How to import Access 2000 table to Access 97 database

ADO can help. ADO and Jet 4.0 files are installed with operating system since Windows 2000, so we can certainly use it, even we have only Access 97 installed. Here a sample code:

Dim oCon As Object
Set oCon = CreateObject("ADODB.Connection")
oCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
CurrentDb.Name & ";Persist Security Info=False"
oCon.Execute "Delete * From Clients"
oCon.Execute "INSERT INTO Clients SELECT * FROM Clients IN 'C:\Access2000.mdb'"
oCon.Close