Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Thanked 0 Times in 0 Posts

    Supporting 2003 with 2007 (2007)

    I'm in the situation of having to support Access 2003 users with Access 2007 installed on my machine. I use DAO code to create a connection to our SQL data base

    Dim td As TableDef
    Dim stConnect As String

    stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" _
    & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword

    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, _
    stRemoteTableName, stConnect)

    CurrentDb.TableDefs.Append td

    and then I use ADO code to remove the connections when the Access database is closed (I think this is because ADO can tell if a table is linked whereas DAO cannot)

    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim intTableCount As Integer

    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection

    'Debug.Print cat.Tables.Count & " Table Objects Before Removing Linked Tables"

    For Each varElement In strTablesOrViews()

    cat.Tables.Delete ("dbo_" & varElement)
    'Debug.Print varElement & " deleted"

    Next varElement

    'Debug.Print cat.Tables.Count & " Table Objects After Removing Linked Tables"

    Set cat = Nothing

    I've found that this still works if I do the following -

    1)Create the mdb as a 2002-2003 format
    2)Opening the mdb from the Access 2003 users computer, open a code module and uncheck the missing reference to Microsoft ADO Ext. 6.0 for DDL and Security, save the module. Open the module again and select either the ADO 2.5 or 2.8 reference (whichever one is installed).

    Is there a way to avoid having to do this? What if I installed MDAC 2.8 on my Vista machine?

    On a side note - If I convert the mdb to an mde, the 2003 users get the message about the "database in an unrecognized format ... created in a later version".



  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Supporting 2003 with 2007 (2007)

    There is no need to use ADO/ADOX. You can use DAO to determine whether a table is linked: if the Connect property of the TableDef is not empty:
    Dim dbs As DAO.Database
    Dim i As Integer
    For i = dbs.TableDefs.Count To 1 Step -1
    If Not dbs.TableDefs(i).Connect = "" Then
    dbs.TableDefs.Delete dbs.TableDefs(i).Name
    End If
    Next tdf
    You woiuldn't need a reference to ADO/ADOX for this.

    I don't have Access 2007, but in general, an MDE created in any version of Access can only be used in that version and later ones, not in earlier ones. So you'll have to create your .mde on a machine with Access 2003.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts