4 Replies Latest reply on Mar 5, 2014 3:47 AM by ranja

    MS Access through ODBC - you can write but you cannot read

    janfm

      Title

      MS Access through ODBC - you can write but you cannot read

      Post

           Hi,

            

           Now I know that there's been a lot of discussion about FM not supporting some ODBC sources and MS Access is not one of them, but I have a question which answer I couldn't find...

            

           After setting up System DSN for particular MS Access file on Windows7x64 FM detects that DSN in External Data Source and there is no problem importing that file, so I guess it can READ data. When 'Execute SQL' script step is used to send SQL query like UPDATE and INSERT INTO that MS Access file it works also, you can see when you open file that data has been written, so I guess it can WRITE data.

           Now, what I cannot understand. If you want use SELECT in query you have to use it in 'ExecuteSQL' function, and to refer to table that you want to read from you have to have that table in relationships graph, but of course if you try to add you'll get 'The ODBC data source you have selected is not supported."

            

           Like title said - FM can write to external data source but it cannot read from.

           I would say that this is like Beethoven's situation - He can compose, but he cannot hear :)

        • 1. Re: MS Access through ODBC - you can write but you cannot read
          janfm

               Forgot to say that in Windows7x64 it was FM Pro13, and another try was on WIndows8x32 with FM Pro12 - same result

          • 2. Re: MS Access through ODBC - you can write but you cannot read
            philmodjunk

                 As I understand it (and I haven't needed to work with ODBC so I'm going from memory on what I have read in several sources), You'd need to use the ExecuteSQL script step, not the function to query the data source and this would be used to copy data into a field in the database so you'd copy data into FileMaker, manipulate it and then use a different query to push it back to the data source.

                 The ExecuteSQL function, on the other hand, was used to facilitate SELECT only queries of actual FileMaker tables.

            • 3. Re: MS Access through ODBC - you can write but you cannot read
              janfm

                   Thanks for answer !

                   I was looking how to use Execute SQL script step to get data from any table but there's no way to pass data from script step to variable or field. It seems that this step is not designed to return data, but only to send SQL query to driver. When I used SELECT there was no result, even Get (LastODBCError) didn't return anything (which I think is normal).

                   ExecuteSQL function can be used to query outside FileMaker, I tried with MySQL drivers and it worked.

                   I'm not searching for solution here, but trying to understand this limitation. Or I got something very wrong :)

              • 4. Re: MS Access through ODBC - you can write but you cannot read
                ranja

                     Unfortunately, MS Access is not one of the database systems that FileMaker supports as an External Data Source.
                     And, you are right.  Execute SQL script step is not designed to retrieve data via SELECT SQL statement.
                     You need to use Import Records script step to get data from an MS Access database.