Maximum Number of Indexes

[InyoTom]

Access won't allow more than 32 indexes in a table.

Any way to get around this? Tom Budlong
TomBudlong@Adelphia.net

 

[Frink]

Hallo,

Not that I know of. Why do you need more than 32 indexes? Sounds a bit inefficient to me. Are you sure your database table structure can't be simplified?

- Frink

 

[InyoTom]

Inefficient design is a continual thought - each time I examine the design it looks OK. It's an accounting app, and the field being relationed is the account number. Lots of other tables use account number, some in several places. An example is the asset ownership table. Fields hold account numbers for principal, interest, receivables and prepaid interest - four indexes used up. The only solution I have come up with is to reduce functionality, which violates the first rule of computer servitude. Tom Budlong
TomBudlong@Adelphia.net

 

[Frink]

Hallo,

I suppose you could create a second table to just hold the account number and link it to the original in a 1:1 mapping. You'd have to keep the second table in step with the first for additions/deletions/changes but it would give you another set of links and still keep some referential integrity.

- Frink

 

[InyoTom]

Frink, thanks for your interest. I think you have the germ of a solution. I will try relationing all fields in the table to an identical parallel table, with cascade update and cascade delete set for all the relations. That should keep the two in sync. Fun and games. Tom Budlong
TomBudlong@Adelphia.net

 

[JeremyNYC]

Tom,

Yikes! That sounds like a scary thing to do. I still don't understand why you need so many idexes. Can you explain again, for the not-so-bright?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[InyoTom]

Scary, yes, but I would try it on some test tables before exposing the app to such risk.
"Need" for >32 indexes devolves into two questions:
1) Theoretical: How to have >32 indexes, independent of cause?
2) Practical: My app - does it need >32 indexes? The home field is the AccountNumber field in the Chart of Accounts table. Relationed to this field are account number fields in other tables. For instance:
Companies table: Equity and Retained Earnings account numbers. Also, presuming account numbers for various income categories are consecutive, first and last account numbers of the consecutive sequence must be in the companies table and be relationed back to the account number field in the Chart of Accounts tabel. Then the income report can find out what account number range to report. Same for Receivables, Payables, etc.

I can imagine other designs, but this is a viable design and I would prefer not to be forced into a different design due to the >32 limit.

Still looking for other ideas. But I'm ready to start testing the parallel table idea should no others appear.

Thanks for your interest. Tom Budlong
TomBudlong@Adelphia.net

 

[Frink]

Hallo,

I've had a little play. I set up a 'master' table with an autonumber Id field. Then set up 4 other tables, each containing 8 Id fields.
I could only add relationships with referential integrity to the first 3 tables, I could only complete about half the 4th.
I created a new linking table, containing only the Id field, Indexed, no duplicates and created a 1-1 relationship to the master table.
I could then link the 4th table of 8 ids to my linking table and it was fine.
As you suggest, cascade update and delete takes care of 2/3 of the integrity problems. The way to deal with added records is probably a bit more tricky and would depend on how you add records. A bit of code in a form should do the trick. I'd call the following function to run a bit of SQL:

if not ysnRunSQL
Function ysnRunSQL(ByVal pstrSQL As String) As Boolean
On Error GoTo Err_ysnRunSQL
  ' Comments  : Runs a SQL string action query
  '           : Note: SetWarnings is set to True by this function
  ' Parameter : strSQL - SQL string to execute
  ' Returns   : True if successful, False otherwise
  '
  DoCmd.SetWarnings False
  If ysnQueryExists(pstrSQL) Then
    DoCmd.OpenQuery pstrSQL, acViewNormal
  Else
    DoCmd.RunSQL pstrSQL, True
  End If
  ysnRunSQL = True
Exit_ysnRunSQL:
  DoCmd.SetWarnings True
  Exit Function
Err_ysnRunSQL:
  ysnRunSQL = False
  Resume Exit_ysnRunSQL
End Function

I would suggest that an identical, parallel table to allow the linking is going to create a lot more problems than it solves. A linking table just containing the Primary Key field(s) is probably your best bet. Then you keep all your data in one place.

- Frink

 

[InyoTom]

I could certainly isolate adding records to one place. Then the risk reduces to making sure the code adds idnetical data to both tables, (and power doesn't go off at the wrong time.) But I like your suggestion of duplicating only the relationed fields - in this case there are two - the Company Name and the Account Number.

I'm off duty for a week for Thanksgiving - will get to fuss with it when I get back. Tom Budlong
TomBudlong@Adelphia.net