Error 3039: Couldn't create index

[AngelaD]

I am accessing a FoxPro Free Table from Access by linking to it using ODBC. In order to be able to update the FoxPro table, I need to define a unique index on the FoxPro table when I link to it.

When I do this using File/Get External Data/Link Tables it works OK.

However, I am having trouble coding it in VBA. I create the index and append it to the tabledef, however, when I append the tabledef I get Error 3039 "Couldn't create index; too many indexes already defined".

Is this because I cannot define and index on a linked table? If this is so, why is it possible to do the same operation using File/Get External Data/Link tables with no problem? How do I get round this?

Thanks, in anticipation,

Angela

 

[WildHare]

1) I'm assuming that Access is going to create the index WITHIN your access db file, not the foxPro file.
2) There is a limit to the number of indices on a table or within a database, not sure what it is. Have you tried a Repair and Compact on your Access db and then tried to recreate the index?

There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[AngelaD]

Wildhare,

Thanks for getting back. By the way, I am linking to FoxPro using ODBC instead of the built-in Jet driver. This is in preparation for upgrade to Access 2000. Microsoft (in their wisdom!?!) have removed the built-in driver in Access 2000, so I am forced to use ODBC!

1. I presume that Access is trying to create an index within my access db. At any rate, it is definitely not creating a FoxPro index (since that would involve the creation of a .cdx or a .idx file).
2. For testing purposes, I moved the code out of my application into a separate db with no other tables. I therefore cannot be hitting a db limit on indexes. In addition, the table itself has no indexes as yet.

I believe that, on linked tables, you cannot change the table definition, and therefore cannot create indexes (which may mean that the number of indexes allowed on the table is zero). However, as part of the File/Get External Data/Link Tables dialog, you are invited to define fields that uniquely identify a record. If you look at the table properties once the link has been created, you will see that a primary key called '__uniqueindex' has been created on the selected fields. I am therefore trying to repeat this process using VBA. Note that if a unique index is not defined on the linked FoxPro table, then it is read-only.

Does this give you a bit more info to go on?

Angela

 

[WildHare]

Angela, you sound like you're much more advanced in the workings of FoxPro and ODBC than I am, so I probably will be worthless to you at this point.

Have you tried searching the MS knowledge base on this? One would think that both products being from Redmond, you aren't the first person to attempt this operation...

Good luck.

There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[AngelaD]

Wildhare,

Thanks for trying to help, anyway.

As far as experience is concerned, I have about 2 years Access experience, and about 10 days of Access/ODBC/FoxPro experience. 10 frustrating days of searching the web (including the Microsoft kb and various forums) with very little results. I am beginning to think that no-one out there references FoxPro tables from Access.

I shall continue my search...

Angela