Results 1 to 2 of 2
Thread: Supporting 2003 with 2007 (2007)
2007-06-06, 15:31 #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, _
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"
'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".
2007-06-06, 16:06 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 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
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.