7 Replies Latest reply on Apr 13, 2012 2:12 PM by taylorsharpe

    ODBC connection problems with a specific Table in filemaker database.

    Marc_Hussey

      Hi all

       

      We have a Microsoft Access 2007 database installed on a local machine that uses an ODBC connection to our filemaker database hosted on our FMS11.

      The ODBC connection works for all tables, with the exception of one. Our "Client" table, which just has a number of Text fields and Calculated fields. About 100 fields all in all.

       

      We have installed the latest ODBC driver FileMaker 11 xDBC Update 11.3.81

       

      The server is also the latest Current Installed Version: 11.0.4.404

       

      The problem is when we try to view the data of the Clients table in Access, Access crashes.

       

      Anyone seen this problem? Any guidance/advice would be appreciated in trying to solve this.

       

      Thanks

       

      Marc


        • 1. Re: ODBC connection problems with a specific Table in filemaker database.
          beverly

          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.

           

          HTH,

           

          Beverly

          1 of 1 people found this helpful
          • 2. Re: ODBC connection problems with a specific Table in filemaker database.
            taylorsharpe

            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. 

            1 of 1 people found this helpful
            • 3. Re: ODBC connection problems with a specific Table in filemaker database.
              Marc_Hussey

              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?

              • 4. Re: ODBC connection problems with a specific Table in filemaker database.
                Marc_Hussey

                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...

                • 5. Re: ODBC connection problems with a specific Table in filemaker database.
                  Marc_Hussey

                  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.

                  • 6. Re: ODBC connection problems with a specific Table in filemaker database.
                    Marc_Hussey

                    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.

                     

                    Any clues?

                    • 7. Re: ODBC connection problems with a specific Table in filemaker database.
                      taylorsharpe

                      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.