3 Replies Latest reply on Jan 15, 2009 1:36 PM by smc

    ODBC Import from MS Access to FileMaker Server DB



      ODBC Import from MS Access to FileMaker Server DB

      Your post

      Long time MS Access user, short time FileMaker user.  Doing my best to figure it out.  But I've hit a wall.  I have an MS Access database that contains data that the user needs to 'push' to a FileMaker Server database.  Here is what I've done so far...


      1. I've configured and ODBC connection (DataDirect 32-BIT SequeLink 5.5).

      2. I've created an ODBC link within the MS Access DB that points to a table in my FileMaker Server DB.

      3. I've created a query that identifies the MS Access records that need to be 'pushed' into my FileMaker Server DB.

      4. I tested the query and it works fine as a 'Select' query (shows the appropriate records to be added).

      5. I changed the query to an 'Append' query (to push it into FileMaker) and it auto detected the appropriate field names.  Everything looks just great.

      6. When I try to actually 'run' the query, it fales with the following 3-line message:


         ODBC--insert on a linked table 'composition_FileMaker' failed.


         [DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker Driver][FileMaker]Cannot modify field(201)(#-511)


         [DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker Driver][FileMaker]QueryFailed (#-200)


      I'm at a loss.  If I take this data and export it to a file, I can then manually import that file via FileMaker.  So, why is it that I cannot 'append' directly via the ODBC???


      As I said, I'm not a FileMaker expert.  I'm more like a FileMaker 'Sophmore', which literally translates to 'smart fool' (I know just enough to be dangerous!).  I appreciate having a place where I can learn from those who have "Been there.  Done that.".





        • 1. Re: ODBC Import from MS Access to FileMaker Server DB

          So, some additional information.  I was originally trying to push data from an MS Access query that drew from 3 linked access tables.  It was failing completely.  I got to wondering if it was something about the query itself that it didn't like (although, I cannot see why the ODBC would care).  So, I used the original query that pulled from multiple tables and I just created a simple MS Access table.  Then... I tried a very clean query that pulled data from the new query into FileMaker and IT WORKED.  I have no idea why it would care wether the source data came from a single vs. multi-table append query, but it did.


          So... I have a work around.  But if someone has an explanation as to why, it would be helpful.


          Thanks to those who read the post in an effort to help.



          • 2. Re: ODBC Import from MS Access to FileMaker Server DB

            Thanks smc for the explanation,


            Im going to try and link up FM and Access in the not so distance future, so this is Interesting. FM's ODBC driver seesm to be a little flakey, we always seem to have problems with linking FM up with Word, and I tried recently to link FM up with an Accounting package and failed.



            • 3. Re: ODBC Import from MS Access to FileMaker Server DB

              Glad it was helpful.  One of my disappointments with FileMaker is that it "doesn't play well with others".  I really like the web functionality (IWP).  But in a University environment, everyone has their own favorite tool of choice.  it's important to be able to link and integrate will with other products.  The MS products do this pretty well.  Perhaps because they are all from MS, eh?  If the FileMaker ODBC functionality worked better, it would be less of an issue.  Maybe v10 will bring some improvements on this front.


              My lesson from this whole thing was to try a few different approaches (crazy or not) before giving up.  It worked out this time.  Hopefully, my luck will continue.