4 Replies Latest reply on Jun 5, 2015 2:59 AM by NathanVeitch

    Connecting to an Access database

    NathanVeitch

      Title

      Connecting to an Access database

      Post

      Can anyone help me with a major issue I am having. I need to push and pull data between an access database and FileMaker Pro 14. I have the ODBC setup and I can use the ODBC to import records from the access database but I can not place any of the access table on the FM relationship graph and when I try using the MBS plug-in I can not make a connection to the access database. 

      If anyone knows how to make a connection between access and FileMaker please let me know.

        • 1. Re: Connecting to an Access database
          SteveMartino

          Which MBS plug-in are you using.  Did you try to contact MSB and ask for help?

          http://www.monkeybreadsoftware.de/

          Also do you have/need an ODBC driver? If you do, Windows should have them in (depending on version) Control Panel>System and Security> AdministrativeTools

          What happens when you go to the relationship graph, select the Add a Table button, click the dropdown for Specify Table, and select Add ODBC source?

          • 2. Re: Connecting to an Access database
            NathanVeitch

            Hi Steve,

            Yep, I Christian helped me with the connection. When I tried to add the ODBC to the relationship graph, it kept saying that the driver was not supported. It seems I can make the connection now via the MBS plugin. Now its just the rest of the script that I need to complete. 

            • 3. Re: Connecting to an Access database
              philmodjunk

              ESS (External SQL Source) is only supported for certain data sources. Last I heard, MS Access was not on that list, but haven't checked to see if this list was expanded for v14. Without ESS, you can't add a shadow table to FileMaker in order to get a TO referencing a query of the ODBC data source. Instead, as I understand it, you define a table in FileMaker and import ODBC data into it. I seem to recall that the Execute SQL script step (NOT the function) can then be used to push data from a FileMaker table out to the ODBC data source.

              • 4. Re: Connecting to an Access database
                NathanVeitch

                Well after bashing my head against the wall for a number of hours, it turn out that the ExecuteSQL script step does everything I need it to do :D. I was under the impression that the FM SQL only allowed SELECT, but I found out last night that the ExecuteSQL() script step does it all :D. Now to teach myself SQL in a day so that I can finally get my solution working :D