1 2 Previous Next 16 Replies Latest reply on May 20, 2009 3:35 PM by TedMH

    New FMP user needs to link to FMP 10 from xl2004

    TedMH

      Title

      New FMP user needs to link to FMP 10 from xl2004

      Post

      Hi.

       

      I'm an experienced MS Office user, including Access, and I need to create, under Mac OS, xl2004 PivotTables using a FMP 10 table as an external data source.  My experience is with Excel to Access under Windows, and it's about as simple as can be--from Excel I just start the PivotTable create and tell it I want an external data source and it walks me through the MS Query steps to my Access table.  

       

      It looks like it's a great deal more complicated in the xl2004-2008 world.  I've spent the better part of a day trying to get a data connection established between xl2004 and FMP 10, and I'm still coming up empty handed.  I've read several fairly technical posts, studied xl and FMP Help and read some relevant documents--including the FPM ODBC/JDBC user guide--and my head is sort of spinning.

       

      Here's my question:  where can I find simple, straight-forward instructions for bringing a FMP 10 table into an xl 2004 PivotTable as an external data source?  I'm happy to buy any additional products that are required, but I'm given to understand that FMP provides the necessary drivers, etc., to do what I want to do.

       

      Any help will be much appreciated.

       

       

        • 1. Re: New FMP user needs to link to FMP 10 from xl2004
          TSGal

          Ted M H:

           

          Thank you for your post.

           

          The "ODBC and JDBC Guide" is fairly straight-forward.  Did you have problems installing the driver?  If so, were the minimum requirements met?

           

          If the installation was fine, did you have a problem accessing the database file?  If so, were there any error messages?

           

          Unfortunately, I'll need a lot more information before I can progress further.

           

          TSGal

          FileMaker, Inc.

          • 2. Re: New FMP user needs to link to FMP 10 from xl2004
            philmodjunk
              

            If the ODBC approach doesn't work for you. You might try exporting the data from FMP into an excel format file. That option is extremely easy.

             

            I realize that your data is now a copy and not subject to continuous updating, but if you can work with that, this approach may work for you.

            • 3. Re: New FMP user needs to link to FMP 10 from xl2004
              TedMH
                

              Hi TS Gal,

               

              <!-- StartFragment -->

              Thanks very much for replying.  I think I followed the instructions for the ODBC driverinstallation.  I understood it tosay that I have to copy the file SequeLink.bundle to one of two libraries.  I put it in my User library ODBCfolder, which also has odbc.ini and odbcinst.ini in it.

              I skipped the JDBC stuff, assuming that I only need ODBC todo what I’m trying to do.

              Next I go to Excel 2004 and on the Data menu I click GetExternal Data and then Import from FileMaker Pro.  This opens the Choose a Database window, and I can see mydatabase file (extension .fp7), but it’s grayed out so I can’t choose it.

               

              After looking at the ODBC Guide again, I’m guessing thatthere’s another step that I’m missing: Configuring client drivers. But I can’t find any clues as to how I configure the client driversother than that I must specify 2399 as the port. 

              Again, I’m basically a Windows person trying to work on aMac project.  I’m guessing thatmost Mac users know what SequelLink.bundle is and what 2399 is and what the ODBC administrator is, but I’mlost.  What I ultimately want to dois to have an Excel 2004 pivottable link to my FMP database table so I don’thave to replicate the data in Excel (I’m assuming that if I can get the Importfrom FileMaker Pro function to work I’ll be able to get the pivotTable thing towork as well). 

               

              Any help you can provide to me will be greatly appreciated.

               

              <!-- EndFragment -->

              Ted

              • 4. Re: New FMP user needs to link to FMP 10 from xl2004
                TedMH
                  

                Hi PhilModJunk,

                 

                Thanks for the suggestion.  My problem is that I want to link to the external database, for two reasons.  1) it's cleaner to have just one image of the data I so I can keep my Excel PivotTable current just by refreshing and not have to repeatedly copy the source data into Excel.  2) My database has more than 75,000 rows so it exceeds the limit of Excel 2004.  In Windows Excel 2003 I get around the limitation by linking my PivotTables to Access database tables.  I'm assuming (hoping!) that I can do this same thing between FMP and Excel 2004.

                 

                All the best. 

                • 5. Re: New FMP user needs to link to FMP 10 from xl2004
                  TSGal

                  Ted M H:

                   

                  You were correct to skip the JDBC instructions.

                   

                  You placed the SequeLink file in the appropriate place.

                   

                  Within FileMaker Pro, open the file you want to share, pull down the File menu and select "Sharing -> ODBC/JDBC..."   Turn on ODBC/JDBC Sharing, highlight the file in the lower left quadrant, and set the ODBC/JDBC access to All Users.

                   

                  This should then get you to see the file now via ODBC.

                   

                  TSGal

                  FileMaker, Inc. 

                  • 6. Re: New FMP user needs to link to FMP 10 from xl2004
                    TedMH
                      

                    Hi TSGal,

                     

                    Thanks so much for the quick reply. I truly appreciate your help.

                     

                    I followed your instructions exactly and I get the same result I was getting before. I can open the file just fine in FMP, but when I try to access the file from Excel to import it, it's still unavailable (grayed out).

                     

                    I don't know if this is relevant, but I looked in my System Library ODBC folder and there were several "Actual" files:  Actual SQL Server.bundle, Actual Oracle.bundle, etc.  When I try to run the Excel New Database Query command it opens a window called iODBC Data Source Chooser.  All the "Actual" files are listed there and the SequeLink file is not listed.  I moved the contents of the System Library ODBC folder to trash and then tried to import data into Excel via FileMaker Pro.  Same result again--my database file is unavailable to Excel.

                     

                    Any additional suggestions or things I can try?

                     

                    Many thanks.

                     

                    Ted 

                    • 7. Re: New FMP user needs to link to FMP 10 from xl2004
                      TedMH
                        

                      Hello again, TSGal,

                       

                      Just a quick note to ask if you're going to keep trying to help me with this, or if you've given up and moved on...

                       

                      One way or another I have to find a solution to this issue, and so far you're the best I've got. 

                       

                      Ted

                      • 8. Re: New FMP user needs to link to FMP 10 from xl2004
                        TSGal

                        Ted M H:

                         

                        Sorry for the late reply.  We had a major release on Tuesday (Bento for iPhone), and the Bento forum became overwhelmed, so I was moved there for the past few days.

                         

                        Give me a couple of hours as I catch up on other things, and I'll post a response a little later.

                         

                        TSGal

                        FileMaker, Inc. 

                        • 9. Re: New FMP user needs to link to FMP 10 from xl2004
                          TSGal

                          Ted M H:

                           

                          Sorry...  I ran out of time today.  I'll work with you on Monday.

                           

                          TSGal

                          FileMaker, Inc. 

                          • 10. Re: New FMP user needs to link to FMP 10 from xl2004
                            TSGal

                            Ted M H:

                             

                            I'm still on the Bento forum until Thursday.  Until then, try this:

                             

                            1. iODBC Administrator adds the ability to "Test Connect" the System DSN created to interact with FileMaker Pro.  This will provide additional information as to why the connection is failing.  Download the iODBC admin from:

                             

                            http://download.cnet.com/iODBC-Driver-Manager-Administrator-SDK/3000-2070_4-51225.html

                             

                            2. While setting up the driver, make sure that you do not copy and paste the Driver File Location from the PDF.  Sometimes, junk characters are pasted along with the text.  This causes the connection to fail.  It is best to type out manually, verifying the string before committing.

                             

                            I'll connect with you on Thursday.

                             

                            TSGal

                            FileMaker, Inc.

                            • 11. Re: New FMP user needs to link to FMP 10 from xl2004
                              TedMH
                                

                              Hi TSGal,

                               

                              Thanks for hanging with me on this problem.  It's really frustrating and I appreciate your willingness to help.

                               

                              I think I already have iODBC installed.  When I try in Excel 2004 to import external data the iODBC Data Source Chooser opens, and it shows my data source.  When I choose my data source and click Test, I get a message:

                               

                              IM002

                              [iODBC][Driver Manager]Data source name not found and no

                              default driver specified.  Driver could not be loaded.

                               

                               The only option is OK, which I click, and that returns me to the Chooser.  I click OK to exit back to Excel and then I get the "Could not establish connection" message followed by the "Microsoft Excel did not receive any information from Microsoft Query" message. 

                               

                              Then I'm back where I started with no data in my Excel file.

                               

                              Do you recommend that I download the iODBC stuff anyway?  If so, can you tell me how I'd install it on my Mac?  Do I need to uninstall anything first?

                               

                              I may have copied and pasted information from the PDF...I don't remember for sure, but if that was an option I probably did--I never type when I can copy!  Should I delete all my driver and DNS configuration work so far and do it over again, making sure I don't copy and paste?

                               

                              Again, many thanks for your continuing help with this.  

                               

                              Ted 

                              • 12. Re: New FMP user needs to link to FMP 10 from xl2004
                                TSGal

                                Ted M H:

                                 

                                I'm still assigned to the Bento Forum, but coming back to try and answer FileMaker posts from over a week ago.  Hopefully, I'll receive some help soon.

                                 

                                Yes, please don't copy and paste.  This has caused a lot of problems.  As you have found out, it could not establish a connection.  I would reinstall and manually enter the location from the PDF file.

                                 

                                I will check back again tomorrow.

                                 

                                TSGal

                                FileMaker, Inc. 

                                • 13. Re: New FMP user needs to link to FMP 10 from xl2004
                                  TedMH
                                    

                                  Hi TSGal,

                                   

                                  I tried doing all the setup again from the start.  I documented the steps.  Can you look at them and see what I'm doing wrong?  Here's what I did:

                                   

                                  <!--   StartFragment   -->
                                  • Remove previous driver and DNS entries
                                  • Remove all files from folders ODBC for System and User libraries
                                  • Copy from folder ODBC Client Driver Installation SequeLink.Bundle to User library ODBC (as on P13 in ODBC guide).
                                  • Checked user library ODBC folder is set to Shared and that Read and Write privileges are set for accounts that will access the database.
                                  • In the database file, confirmed accounts set up with Full Access and Extended Privileges set like this:  Keyword fmxdbc, description Access via ODBC/JDBC, privilege sets Full Access, Data Entry Only, Read Only access.
                                  • Database file set to Sharing on and Access to file set to All Users
                                  • In the ODBC Administrator I create a new driver called myODBCdriver.  I choose SequeLink.Bundle and add the rest of the path information from P19 in the ODBC manual. /UsersTMH/Library/ODBC/SequeLink.bundle/Contents/MacOS/ivslk20.dylib
                                  • I leave Setup box blank and do not enter any keywords.
                                  • For User DSN I name it myTESTDB1 and choose driver myODBCdriver.  Description:  This is my user DSN
                                  • I add the three keywords as instructed on page 20 of the manual.  I type them in manually.  I do not copy and paste.  For ServerDataSource the value is simply the name of my FMP DB file without the extension:  TESTDB1    I don’t enter any Path information for the file—just the file name.
                                  • Now I go to Excel 2004 and try to create a Pivot Table with External Data source. 
                                  • Excel prompts me to Get Data, and this launches the iODBC data source chooser. 
                                  • I select User DSN (it’s empty) and click Add.  This launches the Choose an ODBC Driver dialog box, which is also empty.
                                  • From here I have no option but to cancel.
                                  • I repeat this same steps in Excel 2008 and I get identical results.
                                  • I tried these steps in Excel with my FMP database file open and then with it closed.  It makes no difference—same result whether open or closed and whether 2004 or 2008.
                                  • I then try to set up a New Database Query instead of going through the PivotTable steps.  This produces the same results that I get when I try to create a PivotTable with External Data.
                                  <!--   EndFragment   -->

                                   

                                  I'm getting desperate.... Any help you can give me will be appreciated.  At this point I just have to get this resolved.  I've called FMP tech support and they just haven't understood the problem, and have just told me to read the manual, which I've done, several times.  Is there a way to get direct access to your level 2 tech support?  Even if I have to pay for the help, I have to get this resolved.  Suggestions?

                                  Many thanks.

                                   

                                  Ted 

                                  • 14. Re: New FMP user needs to link to FMP 10 from xl2004
                                    TSGal

                                    Ted M H:

                                     

                                    I am back on the FileMaker forum, but I don't have enough time to devote to it today.  I will work with someone tomorrow and document everything.

                                     

                                    I will be using the manual plus the troubleshooting steps in the Knowledge Base.  In specific, Knowledge Base article #6581.  I'll try to find a copy of Microsoft Excel.

                                     

                                    http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/enduser/std_adp.php?p_faqid=6581

                                     

                                    TSGal

                                    FileMaker, Inc. 

                                    1 2 Previous Next