Too Many Indexes To Link

I am trying to link a Progress table into Access. However, the Progress database table has 41 indexes. I have even tried to import and get the same error - Too many indexes - . Is there a way to get around this? Currently I am importing to Excel then linking Excel to Access. The table is time sensitive so a live link is the best. Any ideas? Thanks

View Replies



I am a beginner and can someone please help me with creating indexes( I think this is what I want to do)

I have a table for entering realtors

the primary key is realtorID (AutoNumber)

is there anyway to find if the realtor has been entered twice or prevent the realtor from being entered twice



View Replies View Related

Too Many Relationships / Indexes

Sorry to add to the number of 'relationship' posts but a thorough search has failed to turn up anything similar to my problem.

I have a table tblSite
Each geographical site (defined by a PK 'SiteNo') in tblSite can contain multiple occurrences of a large number of types of geographical features (ie multiple 1-many relationships).

Eg each Site can contain
100 trees
10 ponds
5 streams
2 springs and so on.

I could have anything up to 50 or so different types of feature that are related on a 1-many basis to the site, linked by 'SiteNo'

Access prevents any more than 32 indexes per table so in practice that means that you can't have more than 32 relationships with referential integrity enforced on any one table. I thought that I was correct in having a separate table for each feature type and creating 1-many relationships to the central table. The fact that I can't have more than a certain number of relationships suggests that my structure is wrong.

I thought about having one large table with a 'feature type' field plus all the other fields required for each feature type but the info recorded about each feature type is different, leading to a large table with many fields, many of which would be blank. This seems to be un-normalized to me.

So, my question is twofold really.
Have I got my structure right? Is it one of those special cases (we all want to be a special case?) and if so, how do I get round it?
If not, where have I gone wrong?
I thought I was comfortable with my design but coming up against this limit has thrown me a bit and I'm not sure how best to proceed.


View Replies View Related

Indexes - Version: 97 (8.0)

Hi, any ideas on how to resolve the following error "Operation invalid without a current index" which apperas when I try to open the Table Tab, no tables, queries etc are visible in the db window although it is fully populated (or was) . The db was fine until it crashed while running repair database?

cheers in advance


View Replies View Related

Too Many Indexes Error Message

I am a beginner when it comes to access. The project that I am working on is tracking Travel claims. The claims are submitted ever month for 4 years so I have created 48 tables for each month and tried to link them via the SSN. The member can also submit other claims during this period, ie supplementals (for reimbursements that were missed in the monthly) and Concurrent duty claims (for the member going to additional sites then where they are assign).

I have a

MBR tbl - that holds all the MBR's info (SSN, name, unit, city, state, POC, POC email and Remarks)

(48) Partial tbl - linked by the SSN.

(5) Suppplemental tbl - Linked by the SSN ( I need more of these but I started with 5)

(5) Concurrent tbl - linked by the SSN ( I will need more of these)

These are all one-one relationship.

How can I get this to work. I can upload a blank copy of this database.

Thanks for any help

View Replies View Related

How To Add Indexes For My Customer Table?

Hi guys. I got unbounded form that I want to add searching functionality by utilizing indexes. I would like to know how to add indexes for my customer table so that I can use it for searching records. I be happy if some one help with make indexes. Thanks

==> pic of my customer table in design view

View Replies View Related

Duplicate Indexes? - Version: 97 (8.0)


I wrote a function to gather info about the indexes in my database. I'm using the following format:

In a few of my tables the only index visible in the table design view is the autonumber primary key.

On a few, my function reports one index, named the name of the field:


However, it often appears that there are 'duplicate indexes':


Do these 'duplicate indexes' serve any purpose? Should I not delete one of them?

Thanks in advance.


here's the function I used, in case it might be useful to somebody else using DAO:
call (since the output doesn't fit in the immediate window):

Private Function getIndexes()
On Error GoTo errGI
Dim t As TableDef
Dim i As Index
Dim f As Field

For Each t In CurrentDb.TableDefs
getIndexes = getIndexes & t.Name & ":" & vbNewLine
If t.Indexes.Count Then
For Each i In t.Indexes
getIndexes = getIndexes & vbTab & i.Name & vbNewLine
For Each f In i.Fields
getIndexes = getIndexes & vbTab & vbTab & f.Name & vbNewLine
getIndexes = getIndexes & vbTab & "{No Indexes}" & vbNewLine
End If
Set f = Nothing
Set i = Nothing
Set t = Nothing
Exit Function
Select Case Err.Number
Case 3110
getIndexes = getIndexes & vbTab & "{System Table}" & vbNewLine
Resume nexttable
Case Else
MsgBox "error " & Err.Number & vbNewLine & Err.Description
Resume exGI
End Select
End Function

Sub printindexestofile(filepath As String)
Open filepath For Output As #1
Print #1, getIndexes()
Close #1
Application.FollowHyperlink filepath
End Sub

View Replies View Related

Too Many Indexes??!! - Version: 2002 (10.0) XP

This is an error I've never experienced before.

When I create a lookup I follow the default join it creates and then I enforce referential integrity.

However, I found a few things that I find mysterious:

1) When I delete the relationship/join b/n tables, the lookup is still valid. Is this OK? Or will I run into problems?

2) What's the difference b/n a lookup and a relationship anyway?

3) When do I use relationships v. a lookup?

The reason I ask is because I was creating tables yesterday and on the last join that was created, when I tried to force referential integrity, it told me there were too many indexes on the table and I should remove some. If I didn't enforce referential integrity, it was fine with the join. Personally, I'm wary of the join b/n tables if no referential integrity is enforced which leads me to the final question:

4) When is it appropriate to enforce referential integrity?

View Replies View Related

Need To Create Indexes - Version: 2003 (11.0)

I've inherited a database that contains 130 tables. None of the tables have any realationships when I view them through Tools/Relationships. Out of all these tables only seven have a PK. Some of the tables do have indexed fields.

The database is running very slowly; I believe that any forms opened are doing a brute force search for data since nothing seems to be linked. How would I approach this database to start linking tables and applying indexes where needed? Are there any pitfalls I need to be wary of?

Any advice you can offer is appreciated. Wasn't sure which forum to place this question. Let me know if you need more information.

Thanks again,

View Replies View Related

Question About Indexes - Version: 2002 (10.0) XP


I've been going nuts doing something programmatically that I now think might be something I could do with an extra index in a child table or some other function.

I have a parent table called Parent table linked to a Child table in a one-to-many, not referentially-enforced join.

My Child table fields are:
ID (Autonumber Primary Key)
ParentID (Foreign Key to Parent table)
SequenceNumber (This is what I think I may be able to turn into an index)

What I would like to do is:
When a record is first added to the child table for a specific related Parent record, it be given a sequence number of 1. When subsequent records are added that are related to the same parent record, the number populated into this field is incremented by one.

for example a snapshot of the child table might look like this:

ID ParentID Sequence Number
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 2 3
7 2 4
8 3 1
9 3 2

I know I could use an autonumber but I also want to be able to manipulate the SequenceNumbers manually and reorder them so I don't want to have missing numbers.

Any idea on how I can accomplish this?

View Replies View Related

Multiple Indexes - Version: 2003 (11.0)

Is there a way to use multiple indexes when using a table in a VBA module?

e.g. Set MYdb = CurrentDb.OpenRecordset("MyDatabase")
MYdb.Index = "Last", "First", "MI" - This does not work....

I am not able to use a compound key because there may be null fields and duplicates.

View Replies View Related

Seek And Indexes - Version: 2002 (10.0) XP

I have a text field. The text that I have in the field is m-1234.
I am running a >= seek.
My first seek is m and it returns m-1234.
My second seek is m- and it does not find it.
My third seek is m-12 and it does not find it.
My last seek is m-1234 and it finds it.

Why does it not find m-1234 when I am doing a >= seek and my text is m- or m-12?

This works with an Access 97 database not with an Access XP database.

View Replies View Related

Indexes, Database Size - Version: 2003 (11.0)

I've put myself in a dilemma and I was wondering if I could get some advice.

I have a student table (TBL_STUDENTS) containing student id, names (first middle, last, nick), date of birth and gender.
I have a student profile information table (TBL_STUDENT_PROFILE) that contains certain aspects of the student (this is a facility that monitors multiple disabled students).

The relationship is TBL_STUDENTS 1-->many TBL_STUDENT_PROFILE. The columns in the TBL_STUDENT_PROFILE contain data that may change from year to year, and the client decided recently that they wanted it tracked. So I inserted a School Year column.

This being said, a lot of the other tables related to TBL_STUDENTS that have information (like personal contacts, demographic, abilities, etc.) also have year to year tracking and I inserted School Year columns as well in the respective tables and forms (subforms)

THAT being all said, the form for Student Profile was originally set up with subforms with all the subforms being linked to the parent form via STUDENT ID. The parent form draws from TBL_STUDENT_PROFILE which has its own PK.

Here are my options:

Option 1: If I go back and relate the tables originally related to TBL_STUDENT to TBL_STUDENT_PROFILE, I will only have to query the school year on TBL_STUDENT_PROFILE, and reduce the number of indexes on TBL_STUDENT. Will this benefit performance or reduce the data back end size?

Option 2: If I don't, I have to relate the parent form and subforms not only by STUDENT ID but by SCHOOL YEAR. This will increase the size of my GUI front end, correct? Will it also it take a hit on performance?

View Replies View Related

Clustered Indexes In Access - Version: 2003 (11.0)

Hi all,

How do you create a clustered index in access? Is the PrimaryKey index automatically clustered?

-Andy Moyer

Edited by: andeezlemoyo on Mon Sep 4 21:13:55 EDT 2006.

View Replies View Related

Indexes How To Trap Duplicate Errror? - Version: 2003 (11.0)

When an index is created on a field no duplicates allowed, is it possible to trap the error that pops up and replace it with a message of your own choice

View Replies View Related

Add Loop To Skip Missing Indexes - Version: 2002 (10.0) XP


im using this vb code:

Function MySplit(myfield,separator as string,index as long)

if not isnull(myfield) then
mysplit = split(myfield,separator)(index)
' what value?
'mysplit = "XXX"
end if

End function

to break up an address field which is separated by chr(13) & chr(10) - works fine until i get to an index that doesn't exist...

basically most have four lines but some have three menaing the index (4) can't be found- i know there is a way round this using loop and ubound but can't get my head round the instructiosn ive found as they pertain to soemthing slightly different and every combination i try gives me errors

can anyone help?

cheers dubs
Edit/Delete Message

View Replies View Related

Too Many Indexes & Allow Multiple List Values - Version: 2007 (12.0)


I am at the table building stage of my database. I have a main table which looks up values in around 50 other tables. Some of them are 'multiple value list' lookups. Does anybody know if there is a limit in a table on how many of these multiple list value lookups you can have? This is because when I try to add one at the end of my table, I keep getting the error message "There are too many indexes on 'tblSurvey'. Delete some of the indexes on the table and try again'.

However, when I change the 'allow multiple lists' to NO, the table will save. Basically, I can't save the table unless I change it to NO.

PS Should I upload what I have so far?

Thanks in advance for those who chose to reply.

View Replies View Related

Import Specifications Not Creating Indexes When Ran By Macro - Version: 2003 (11.0)

I have import specs saved and I designate in those specs which fields to
index. If I import the data manually the indexes are created but if I use a
Macro the indexes are not there. In the macro I give the name of the specs
to use.
Any ideas?

View Replies View Related

Assigning Record Source, Link Child Fields And Link Master Field - Version: 2000 (9.0)


I'm trying to programmatically assign the record source, link child fields and link master fields of a subform using VBA in Access Reports. Does anyone know if this is possible? And will the code be in the "On Open" event ?


View Replies View Related

Composite Indexes Vs. Composite Primary Keys For Acc 97 - Version: 97 (8.0)

This is the same example as my other post but contains an Access 97 version of the db.

You will often hear that Composite or Multi-field Primary Keys is a poor design technique and usually violates the 1st normal form of Normalization. Typically it is recommended to use a composite Index instead to accomplish the effect of the combination of 2 or more fields to create a unique record. The problem is it's not often explained how. The attached db does just that. It walks you through the bad design of a composite PK, the Inproved design of adding an Autonumber Field, and ultimately the best design of having a single, autonumber primary key and a composite Index.

Hope you find this helpful,

View Replies View Related

Update Queries Vs Append Queries, Indexes?

This is a very simple problem most likely for the masses, but I am new to access. I have employees who enter will enter information about specific tests on electronics components into separate tables by a form. I know my method is poor, but this is how it works. I got assigned this database at my internship this summer because they ran out of thigns for me to do.....I've never even used access before, so as long as it works, they're happy. I have a form which which writes to a first table with 20 fields. I then have separate update queries which take the data in the first table and put it in all the other tables. Trust me, i know this is pretty much the opposite of the whole point of a relational database, but I am and was limited by time, for the size of the database they want, there was no time to learn about normalization etc etc. Sorry this is wordy, but THE QUESTION is......if they run multiple tests wtih the same information for some of the fields I have fields named "Run#, Unit#, Date" etc, even if there is records wtih the same information, hwo do i get it all to show. Right now, it seems to be rewriting over the same records. If they run 3 tests on unit 10 on August 1st, how do i get it so all those show up. I think its somethign wtih in the table, for the primary keys, changing the Index: No, Yes(Duplicates) Yes(No Duplicates) but I could be way off. Thanks, if the question doesnt make sense i'll try to rephrase it, I apologize I'm running on about 45 min of sleep:confused:

View Replies View Related

How Do I Refresh/update My Indexes? - Version: Any Version

I'm sure there's an easy answer, but I'm not having much luck thinking/searching on my own.

Let's say I've defined an index on a secondary key of a table, but the table isn't static so the indexes need to be rebuilt.

How do I do that in Access?

View Replies View Related

Indexes - Info And Links - Version: Any Version

There is not a whole lot of information on creating / using Indexes for Access. So in the quest for knowledge on Indexes I found the following info and links - actually this is a re-post from another site. If anyone has more information and links please add to this post.

I found the following suggestion regarding ‘non clustered’ indexes:

“Always design your indexes with the most sparse columns first and the least sparse columns last, e.g, Name + Province + Sex.”

Interesting… because originally I thought that would be backwards, evidently not true.

Here are some tips regarding indexes, but these are geared for MS SQL Server, not Access (although I would apply whatever I could) from here:

View Replies View Related

Indexes In Access 2003 - Version: 2003 (11.0)

Can anyone help me with how to move through a RecordSetClone using a non-primary index. My table has two indexes defined but when I do a FindFirst using the field defined as the non-primary index and then do a MoveNext, it appears that it uses the primary index to do the MoveNext. This causes an EOF where there shouldn't be one!

View Replies View Related

Question About Relating Tables Vs. Link Child/link Master - Version: Any Version

I've always learned to build tables; relate tables; add data (in that order).

In playing around with forms/subforms, I asked a question about posting comments to a form that aggregates data and received an excellent set of helps from this forum to create a table to store the contents, create a subform to enter the comments, and place the subform on the aggregated data form and link them through link child/link master.

In experimenting with all of this, I never got around to actually creating a relationship between the comments table and the table that stores the raw data.

Do I need to relate these tables? Are there pitfalls involved in NOT relating them (the entire project works great right now and the linked tables only need to communicate with each other, no other tables)? What might happen if i relate them now? (Data is in both tables).

Always learning from the good folks here!!

Slipping along the Access learning curve....

View Replies View Related

Indexes And What's Really Going On... - Version: Any Version

Even if we index these four fields below we can still get a conflict...

If we have these two appointments.


I'm thinking the before update event will be the best form property to eliminate the following problem. Is there an easy way? I don't neccesarily want to run a query to capture overlapping time....

Just a good one for refreshing or thought! Thanks....

View Replies View Related

Link Me Up.

i have a small problem wizzing my nut. I copy two tables from mycurrent database to an external database,now i am trying to link these two tables in the external database to the ones in the currentdb. I get a run-time error 3012 telling me that an object 'tblLanguage' is already present.which iam failing to track. Any ideas Please??
Here is my linking code.
Sub LinkMeUp()
Dim tbl As DAO.TableDef
Dim sTableName As String
Dim sThatTblpath As String
Dim dbName As String
dbName = "my_ExternalDatabase"
sThatTblpath = ";Database=" & db_Path & "" & dbName

For Each tbl In CurrentDb.TableDefs
sTableName = tbl.Name
Set tbl = CurrentDb.CreateTableDef(sTableName)
tbl.Connect = sThatTblpath
tbl.SourceTableName = sTableName
CurrentDb.TableDefs.Append tbl
Next tbl
End Sub

View Replies View Related

How Do I Get Rid Of This Link.


The current database I am working on I made it by copying the one I was working on originally. I just realized on the VB window that there is still some relation between them. Because, if I look at the tree on the left of the code window I see it says "GCB (miniGCB)", being GCB the one I copied, and miniGCB the one I am working with.

How can I get rid of the link with GCB?? I want the tree to just say "miniGCB".


View Replies View Related

Can't Link

I have a MySQL DB full of tables I exported from Access (2000). I want to create a new Access DB that links to the MySQL DB. I can connect to MySQL successfully with ODBC. When I try to link tables I select ODBC(), then choose the MySQL DB. Then nothing happens. I just have my list of Access tables. If I try to link to another .mdb I get a list of tables to link to. What am I doing wrong? I have the latest MySQL ODBC.

View Replies View Related

Link Pdf

Hello all,

I created a table and linked a pdf file to one of the fields, but when I created a form to display the table contacts, I could not see the attachment or icon or any reference to the linked object. The field in my access table is an OLE object.

Thanks for helping out.

View Replies View Related

How To Link FE To BE Using VBA?

I have packaged an Access DB (Split) as a run time solution so I can distribute to users without Access.

When I install it on a PC it runs.

If I move the back end to a network, the solution can't find the back end because it has been moved.

Access run time does not include the linked table manager in the toolbar.

Is there code in VBA where I can refresh the link between front and back end.

Ideally I want to write an error handler that will call a sub to prompt the user for the new location of the back end if it has been moved. Then have the user browse to the network location and click on the back end and store that location as a string. Using that string refresh the links.

Any ideas on that?

View Replies View Related

Link To Bloomberg

I link my sheet to bloomberg via excel the problem that I have is that some of the value are "#n/A N " and I can't filter it. Any Idea??


View Replies View Related

Excel Link

Hi all,

I have added about 15 links on a form, linking to word and excel documents. The word documents open fine but all of my excel documents pop up with a messae saying the file cannot be open, yet when i just go to the document on my hard drive they all open fine...does anyone have any ideas please. Thanks

View Replies View Related

Copyrights 2005-15, All rights reserved