Problem Converting a back-end from Access 2 to 2003

[Rubens]

I tried to convert a back-end mdb file created a long time ago with Access 2
( the system is still working fine ! ) to Access 2003 and after it finishs
there are some errors related to indexes.

There is one table in the Access 2 file that has about 40 relationships
using referential integrity. When it comes to Access 2003 it says that the
maximum is 32 indexes.

Is it correct that this feature was bigger in Access 2 than is today in
Access 2003 ???

Thanks in advance.

 

[Douglas J. Steele]

The 32 index limit existed in Access 2.0 as well.

One thing to check is that newer versions of Access automatically create
indexes on fields ending with certain names. Look on the Tables/Queries tab
under Tools | Options. Have you checked that there aren't duplicate indexes?

 

[Rubens]

Hi Douglas,

I had not checked so i cleared the auto index feature and convert my access
2.0 file again. The result is exactly the same error ( 8 relationships not
converted ).

In my original access 2 file using the print definition feature it shows me
33 relationships e 20 indexes.

In the access 2003 converted file it shows me 25 relationships and 18
indexes ( and of course 8 not converted ).

Is every relationship counted as 1 index ? How can i see the 32 indexes in
the table ?

 

[Douglas J. Steele]

Each relationship should have an index. It's been a while since I worked
with Access 2, and I thought it had the same limits.

To see all of the indexes, you can use the following code:

Dim tdfCurr As DAO.TableDef
Dim idxCurr As DAO.Index
Dim fldCurr As DAO.Field

Set tdfCurr = CurrentDb().TableDefs("NameOfTable")
For Each idxCurr in tdfCurr.Indexes
Debug.Print idxCurr.Name & " contains:"
For Each fldCurr In idxCurr.Fields
Debug.Print " " & fldCurr.Name
Next fldCurr
Next idxCurr

 

[Rubens]

Hi Douglas,

Your code to show all the indexes failed. I used this I found in the
internet instead :

Dim DB As Database
Dim Ndx As Index

Set DB = CurrentDb

For Each Ndx In DB.TableDefs(TAB_name).Indexes
Debug.Print "[" & Ndx.Name & "]", "("; Ndx.Fields(0).Name & ")",
Ndx.Foreign
Next Ndx

Set DB = Nothing
Set Ndx = Nothing

This one shows me 18 indexes in the access 2003 file. So I'm starting to
think that not all the relationships have an index since it shows 25
relationships or some indexes don't swow up with this code. I'm really
confused...

Thanks.