Problems linking to FMP from MS Access via ODBC

[martyrl]

I'm trying to link/import FMP 5.5 data into Access via FMP ODBC (FileMaker Pro FMFMP50.DLL). It fails with "Invalid Field Definition '<field_name>' in definition of index or relationship". I delete the index in FMP but I continue to get the error. I deleted the field in FMP, and now I get the error on the next indexed field. But I'm not even sure if the problem really has to do with indexed fields, when I deleted this field and tryed it again, I got the error on a non-indexed field. Is it actually possible to link via ODBC to a FMP dataset?

[Anatoli]

In some degree it is, but in general -- ODBC implementation in FM sucks and it is slow.

[_ian]

You probably have more than 33 indexed fields in your FileMaker file...

from the ReadMe document:

7.2.4. A Microsoft Access ODBC client will generate an error message when trying to perform a SQL import from a FileMaker Pro database containing more than 33 indexed fields.

Ian

[martyrl]

thanks - what ReadMe document are you referring to?

also - I 'deleted' all of the indexes in the FMP database and continue to get the errors - but yes, there were well over 33.

[_ian]

The readme document from FileMaker Pro.

Perhaps you have the option selected in Define Fields to automatically index fields if necessary. that could be recreating the index for those fields that are used in relationships. Which does rather open a can of worms, in that you will not be able to un-index those fields without breaking relationships and causing potential problems.

If the problems carry on, you might want to have a look at issuing SQL queries from FileMaker to insert the records into Access. Vrtually certain to be faster and simpler to set up. Access is much more reliable as an ODBC data source and FileMaker can INSERT, UPDATE & DELETE records there via ODBC.

Ian