1 of 1 people found this helpful
Marc, I don't know if this helps, but I've heard it to be the solution to other ODBC connections.
Make your fields limited to lengths (rather like varchar in SQL).
Click on Options for the text field.
Select the Validation button.
Check the "Maximum number of characters" and specify the length.
If you already have data, you may want to find the lengths of these fields first to make sure you have enough characters specified. This will "validate" upon new records, but the setting may help with the ODBC access.
I have not tried this, but heard it works.
As for the calculations, you may wish to make them "auto-enter" or scripted (triggers?) entry, so that they are "text" which can be indexed and set to maximum number of characters.
1 of 1 people found this helpful
Out of curiosity, try the same connection with another program like Excel and see if it crashes or if this is just an Access problem. If it is an Access issue, you might want to seek assistance in an Access forum instead of FileMaker. Also, maybe limit what fields it looks for in the ODBC call and try to narrow down which field(s) are causing problems and which ones are working. I know it is a pain, but sometimes you have to do these things slowly. You could also duplicate the table in FileMaker and erase half of the fields and see if works. Sometimes it takes playing around to figure these things out.
Good idea, tried to run it on a number of different applications and none managed to import the table. Excel just gives up, Access crashes, and a program we use called Inspire returns nothing.
As soon as we try linking to another table in the same database it works fine for that other table.
Tried just adding a few fields rather than the whole table, and result it imported....
After trying adding more and more, it gets to about 60 fields and starts to crash again. It doesnt look like a paticular field is causing the crash, but the combination of fields.
Do you know of any limitations on number of fields the ODBC can cope with?
I've tried set the maximum, but still does the same. I've also tried removing all the data and just created one new record in the database table and it still does the same. Also tried setting all fields to Text, but still does the same. Starting to think it may be a corrupt table...
Just to add to this...
I've tried copying the table in Manage Database, to a new database. So have just the field structure. It still wont let me ODBC to this.
I'm going to try recreate the table from scratch, adding in a field at a time and keep checking the ODBC connection. It'll take a while, but think it'll identify the problem.
Just tried to create a new table with 97 fields, it appears Access and Excel are crashing while trying to access via an ODBC connection.
It works with 61 fields, anymore it seems to fail.
Interesting. Does this happen similarly on more than one machine? How beefy is the client machine? Could it be a memory limit?
Well, there are always work arounds. I find tables with 97 fields frequently have been normalized and could probably be broken down into several related tables. If you did this and each related table remained under 61 fields, you would be good. I know this is not a great solution, but it is a way of working within the limits of whatever the technical problem is limiting the number of fields brought over.
I personally would suspect the ODBC driver, but that is just a guess. Sorry I don't know more because it should work as you describe.