6 Replies Latest reply on Mar 10, 2010 5:53 AM by JoshO.

    FMP/ODBC Record commit question

    Kittermaster

      Title

      FMP/ODBC Record commit question

      Post

      Apologies for what is probably a very basic question, but I can't find a clear answer.

       

      Using FMP 10 connected to Sequel Server tables, I notice that -- although I am able to edit info in a SQL field and (apparently) commit the change in FMP -- the change does not appear to be pushed to the tables themselves unless or until the FMP file is closed and reopened.

       

      What's happening (or rather, not happening)? 

       

      [Mac OS 10.6.2, ODBC drivers up-to-date, SQL Server permissions apparently OK....]

       

      Thanks.

        • 1. Re: FMP/ODBC Record commit question
          Kittermaster

          I'm still looking for a solution or explanation to this. Has anybody got any ideas??

          • 2. Re: FMP/ODBC Record commit question
            kapitaen_1

            hmmm, until now i have seen that changes in fmp will go immediately to the sql server.

             

            try the commit script step

             

            greetings from germany

            chris

            • 3. Re: FMP/ODBC Record commit question
              JoshO.

              How are you looking at the "tables"?  If you are viewing the records from another workstation and are waiting for a change to happen, you won't see it without a "Refresh Screen".  Which happens a number of ways.  

               

               

              • Manual screen refresh (Records menu | Refresh Window).
              • Change to a different Layout and come back.
              • Change to a different Record and back.
              • Include a Refresh Window step in a script.
              • Close FM and reopen.
              Since FM is not constant broadcasting the ESS data the same as its own native data table, there is a not a call back from the SQL server to tell FM to update the screen.  So nothing changes until FM "looks" at the data again and displays the changed values.
              If you are talking about the data on the machine you used to make the change, it is probably a connection problem.  Have you updated FM to v3?

               

              • 4. Re: FMP/ODBC Record commit question
                Kittermaster

                Thanks Chris and Josh.

                 

                The SQL tables are on a separate server. There are several different tables with a common key field.

                The Mac has the FMP database on it, and this contains a few hundred FMP records with the same key field, so I am able to 'pull in' data from the SQL tables which behave as related files. This works fine and pretty quickly.

                I believe I checked whether going to a different record in FMP, or refreshing the screen, would trigger a change in the SQL tables. I don't think it did. However, I will have another look tomorrow and report back.

                 

                Thanks again.

                 

                Terry

                • 5. Re: FMP/ODBC Record commit question
                  Kittermaster

                  Well, as luck would have it, the MacBook Pro owned by my partner on which the FileMaker database was sitting went down yesterday with a completely non-functional screen. I had a fairly recent back-up, but what really saved the day was the ability to boot it in target disk mode. She now has a new user account on my inferior white MB -- which will have to do until we can get a replacement out to Malawi....

                   

                  As a result, I had to install the Actual ODBC drivers on my Mac and configure a new connection to the SQL Server tables. Before doing that I took the precaution of setting her SQL access up again from scratch.

                   

                  The good news is that one of these events seems to have cured the problem: editing SQL table fields is now working as expected, and changes seem to be committed to the SQL tables more or less immediately without resorting to the 'Refresh Window' step -- though I've added that too, just in case.

                   

                  My next task is to tackle creation of new records in the SQL tables, which I think will have to be scripted.

                  • 6. Re: FMP/ODBC Record commit question
                    JoshO.

                    It's alway strange when a disaster solves a problem...but then again I'm used to that (I'm a Windows user).

                     

                    You should be able to create records in the SQL exactly the same as you do for FM tables.  The new record commands generate a record the same way, whether it is a native table or an ESS Shadow table.