Converting Access 97 DB to Access 2

Question

Can an Access 97 D/B be converted into Access 2?

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
To: ACCESS-L <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Re: Converting Access 97 D/B to Access 2
Date: 22 July 1998 19:40

Access 97 isn't backward compatible but if we assume that
we sacrifice the new features when backward convert Acc97 -> Acc 2.0 then:

- we can export Access 97 data tables directly;
- isn't a big problem to collect queries' SQLs into table and recreate them
in Acc 2.0
- isn't a big problem to collect forms/reports and their controls properties and partially recreate
them in Acc 2.0 (see attached code I've just typed). CBF can be also collected.
- we have to retype macros in Acc 2.0 or find this hidden (or I missed it?) collection/tables which MS uses
to generate VBA from macros
- we can copy and paste or auto-collect and recreate modules...

Resume:
- if Acc 97 app uses only a subset of Acc97 features which have equivalents in Acc 2.0 then
it shouldn't be a big problem to convert it backward
- third-party tools shouldn't cost more than $500 - $1000...

You can use Total Access Analyzer (both 2.0 and 97) versions to collect all your MS Access app
properties. (I'm not FMS Inc. employee)

Best wishes,
Shamil

P.S. Acc 97 code sample:

Public Function smsFormControlsCollect(ByVal vstrFormName As String)
'
' Purpose: Collect Access 97 form and controls properties
' Written 22/07/98 by sms - Shamil Salakhetdinov, e-mail: shamil@marta.darts.spb.ru
'
    On Error Resume Next
    
    Dim dbs As Database
    Dim rstFrmPrp As Recordset
    Dim rstCtl As Recordset
    Dim rstCtlPrp As Recordset
    Dim fld As Field
    
    Dim frm As Form
    Dim ctl As Control
    Dim lngCtlType As Long
    Dim prp As Property
    
    
    Dim colFrmPrps As Collection
    Dim colFrmPrp As Collection
    
    Dim colCtls As New Collection
    Dim colCtl As Collection
    Dim colCtlPrps As Collection
    Dim colCtlPrp As Collection
    
    
    DoCmd.OpenForm vstrFormName, acDesign
    
    Set frm = Forms(vstrFormName)
    
    Set colFrmPrps = New Collection
    For Each prp In frm.Properties
        Set colFrmPrp = New Collection
        colFrmPrp.Add frm.Name, "FormName"
        colFrmPrp.Add prp.Name, "PropertyName"
        colFrmPrp.Add prp.Type, "PropertyType"
        colFrmPrp.Add prp.Value, "PropertyValue"
            
        colFrmPrps.Add colFrmPrp, prp.Name
    Next
    
    Set colCtls = New Collection
    
    For Each ctl In frm
        Set colCtl = New Collection
        
        colCtl.Add frm.Name, "FormName"
        colCtl.Add ctl.Name, "ControlName"
        colCtl.Add ctl.ControlType, "ControlType"
        colCtl.Add ctl.Section, "Section"
        colCtl.Add ctl.Parent.Name, "Parent"
        colCtl.Add ctl.ControlSource, "FieldName"
        colCtl.Add ctl.Left, "Left"
        colCtl.Add ctl.Top, "Top"
        colCtl.Add ctl.Width, "Width"
        colCtl.Add ctl.Height, "Height"
        
        Set colCtlPrps = New Collection
        
        For Each prp In ctl.Properties
            Set colCtlPrp = New Collection
            colCtlPrp.Add frm.Name, "FormName"
            colCtlPrp.Add ctl.Name, "ControlName"
            colCtlPrp.Add prp.Name, "PropertyName"
            colCtlPrp.Add prp.Type, "PropertyType"
            colCtlPrp.Add prp.Value, "PropertyValue"
            
            colCtlPrps.Add colCtlPrp, prp.Name
        Next
        
        colCtl.Add colCtlPrps, "Properties"
        
        colCtls.Add colCtl, ctl.Name
    Next
    
    DoCmd.Close acForm, vstrFormName
    
    Set dbs = CodeDb()
    
    dbs.Execute "delete * from [tblFrmPrp]"
    dbs.Execute "delete * from [tblFrmCtl]"
    dbs.Execute "delete * from [tblFrmCtlPrp]"
    
    Set rstFrmPrp = dbs.OpenRecordset("tblFrmPrp", dbOpenDynaset, dbAppendOnly)
    For Each colFrmPrp In colFrmPrps
      rstFrmPrp.AddNew
        For Each fld In rstFrmPrp.Fields
            rstFrmPrp(fld.Name) = colFrmPrp(fld.Name)
        Next
      rstFrmPrp.Update
    Next
    
    Set rstCtl = dbs.OpenRecordset("tblFrmCtl", dbOpenDynaset, dbAppendOnly)
    Set rstCtlPrp = dbs.OpenRecordset("tblFrmCtlPrp", dbOpenDynaset, dbAppendOnly)
    
    For Each colCtl In colCtls
      rstCtl.AddNew
        For Each fld In rstCtl.Fields
            rstCtl(fld.Name) = colCtl(fld.Name)
        Next
      rstCtl.Update
      
      For Each colCtlPrp In colCtl("Properties")
        rstCtlPrp.AddNew
          For Each fld In rstCtlPrp.Fields
            rstCtlPrp(fld.Name) = colCtlPrp(fld.Name)
          Next
        rstCtlPrp.Update
      Next
    Next
    
    rstFrmPrp.Close
    rstCtl.Close
    rstCtlPrp.Close
End Function

P.P.S Acc 2.0 code sample:

Sub smsAcc20FrmCreate (ByVal vstrFrmName As String)
'
' Purpose: Partially (re)create Access 2.0 form and controls properties from the
'          info collected by smsFormControlsCollect(...) function
' Written 22/07/98 by sms - Shamil Salakhetdinov, e-mail: shamil@marta.darts.spb.ru
'
    
    On Error Resume Next
    
    Dim dbs As Database
    Dim rstFrmPrp As Recordset
    Dim strFrmPrpSql As String
    Dim rstCtl As Recordset
    Dim strCtlSql As String
    Dim rstCtlPrp As Recordset
    Dim strCtlPrpSql As String

    Dim frm As Form
    Dim ctl As Control
    Dim prp As Property

    Set dbs = CodeDB()

    Set frm = CreateForm()
    
    DoCmd RunMacro "mcrFormHdrFtrCreate"

    strFrmPrpSql = "select * from [tblFrmPrp] where ([FormName] = """ & vstrFrmName & """)"
    Set rstFrmPrp = dbs.OpenRecordset(strFrmPrpSql)
    While Not rstFrmPrp.eof
        frm.properties(rstFrmPrp("PropertyName")) = rstFrmPrp("PropertyValue")
        rstFrmPrp.MoveNext
    Wend

    strCtlSql = "select * from [tblFrmCtl] where ([FormName] = """ & vstrFrmName & """)"
    Set rstCtl = dbs.OpenRecordset(strCtlSql)
    
    While Not rstCtl.eof
        Set ctl = CreateControl(frm.name, rstCtl![ControlType], rstCtl![Section], rstCtl![Parent],
smsNb(rstCtl![fieldname]), rstCtl![Left], rstCtl![Top], rstCtl![Width], rstCtl![Height])
        
        strCtlPrpSql = "select * from [tblFrmCtlPrp] where ([FormName] = """ & vstrFrmName & """ and "
        strCtlPrpSql = strCtlPrpSql & "[ControlName] = """ & rstCtl![ControlName] & """)"

        Set rstCtlPrp = dbs.OpenRecordset(strCtlPrpSql)
        While Not rstCtlPrp.eof
           ctl.properties(rstCtlPrp("PropertyName")) = rstCtlPrp("PropertyValue")
           rstCtlPrp.MoveNext
        Wend
        rstCtl.MoveNext
    Wend

End Sub

Function smsNb (ByVal vvar As Variant) As Variant
    If IsNull(vvar) Then
        smsNb = ""
    Else
        smsNb = vvar
    End If
End Function