6 Replies Latest reply on Jun 22, 2009 8:47 AM by philmodjunk

    ODBC Connection and Table Not Found error

    KarlZ

      Title

      ODBC Connection and Table Not Found error

      Post

      I am creating an ODBC connection to FileMaker Pro 10.  I can successfully do this with several FM7 files however for one file I get  this error: [DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker driver][FileMaker]Table Not Found: ds09

       

      The initial connection to the database is successful.  it is only when I try and query a table that I get the error. (e.g. SELECT * from ds09) The file is setup of for ODBC sharing for All users.

       

      The file name is ds09 and I have confirmed in the Manage Database that the table is also named ds09 also.  I have tried copying the FM7 file and renaming both the file and table names just to eliminate Typos.  I have also tried a "Recover" of the database.

       

      Any ideas why this does not work for this one file?  .I did the same steps on the file and table as I did with several other and they all work.

        • 1. Re: ODBC Connection and Table Not Found error
          TSGal

          Karl Z:

           

          Thank you for your post.

           

          If the driver wasn't installed successfully, you wouldn't be able to access any file, so we'll rule this out.

           

          Launch FileMaker Pro 10 and open ds09.fp7.  Pull down the File menu and select "Sharing -> ODBC/JDBC..."   I'm sure it is turned on, but select the file in the bottom left and make sure access is set to "All users".

           

          Pull down the File menu and select "Manage -> Accounts & Privileges..."   Double-click the access privilege you are going to be using with this file, and then click the "Edit..." button next to the Privilege Set.  Make sure under "Extended Privileges" that "Access via ODBC/JDBC (fmxdbc)" is checked.

           

          You should now be able to connect to the FileMaker data source.  If not, please let me know.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: ODBC Connection and Table Not Found error
            KarlZ
              

            Thank you for replying.

             

            I check all the things you indicated and they are correct.

            It is setup for Sharing and access is set to All Users.

            I am using an account with Full Access and when I go to Extended Privileges the "Access via ODBC/JDBC (fmxdbc)" is already checked.

             

            It is behaving like it does not like the table name I am specifying.  Is there any query I can run a query through the ODBC connection that will tell me what table names are in the database so I can confirm what it thinks the table name is?  (There is only one table name in the database and it has the same name as the file name.)

             

            I also tried connecting to this from MS Access also and usually when do a "Get External Data" it will show you all the tables you can link to or import.  With MS Access it did not show my any tables either. 

             

            I found that I can get to the data in the table by exporting it as XML and them importing in to a new Filemaker database and them making an ODBC connection to the new database. 

            • 3. Re: ODBC Connection and Table Not Found error
              mrvodka
                

              Have you tried importing into an Excel sheet?

               

              I have Access 2003. I did New Table --> Import Table --> FileType --> ODBC --> Machine Data Source --> DNS --> OK --> Table

               

              This worked.

               

              • 4. Re: ODBC Connection and Table Not Found error
                TSGal

                Karl Z:

                 

                Thanks for checking the file attributes.  Let's back up a little further and make sure the DSN is set up properly.

                 

                I'm assuming you are using Windows, because you mention Access.  Therefore, go into the Control Panel and select "Administrative Tools -> Data Sources (ODBC)".  If you are using Windows XP, "Administrative Tools" appear in the "Performance and Maintenance" category.  With Vista, it appears in the "System and Maintenance" category.

                 

                Click the "System DSN" tab.

                 

                Find the data source for ds09 and click "Configure".  Make sure "Use LDAP" is cleared.  For "SequeLink Server Host", enter the location of your data source.  If the file is on your local machine, enter "localhost".  If the file is hosted by FileMaker Server, enter the IP address of where FileMaker Server is located.  The port must be "2399".  To the right of "Server Data Source", click the button to display the filenames of currently open FileMaker database files.  Click "Test Connect".

                 

                I don't have Microsoft Access, so I'm not familiar how it connects with an external source.

                 

                For testing purposes, if you change the filename to "Test.fp7" and reconfigure, does it work?

                 

                TSGal

                FileMaker, Inc. 

                • 5. Re: ODBC Connection and Table Not Found error
                  KarlZ
                    

                  I really think there is something unusal with just this particular file/database.  Since then I have been able to successfully connect to 7 other files and database from previous years from the same client.  So I know that both my ODBC driver and the approach I am using is correct. 

                   

                  I have what I need to proceed with the project so I do not want to take any more of anyone else's time on this. 

                   

                  Thank you for the responses.

                   

                  FYI: In my case I was not using a DSN entry.  I am using a DSN-less connection string in the format of:

                  "DRIVER={DataDirect 32-BIT SequeLink 5.4};SDSN=FileName;HST=localHost;PRT=2399;UID=UserName;PWD=Password"

                  • 6. Re: ODBC Connection and Table Not Found error
                    philmodjunk
                       Any time I get a case where one file works and others don't, I have to consider possible damage to the file. Have you tested a recovered copy? If the recovered copy works, replace it with an undamaged back up copy.