5 Replies Latest reply on Oct 8, 2012 10:18 AM by akselsoft

    How To Connect to multiple FM Projects via ODBC

    akselsoft

      Hey all,

       

      I'm new here but have worked with DBs for a while and am trying to see if this is even possible. We have two databases (projects) - both are set up for sharing. If I create a single ODBC connection, can I connect between both databases?

       

      For example, in SQL Server, I would do something like:

      SELECT * FROM database.dbo.customers join database2.dbo.orders on customers.cust_id=orders.cust_id

       

      Is that possible or do I need to create two separate ODBC connections and then join the two solutions together?

       

       

      Thanks in advance.

        • 1. Re: How To Connect to multiple FM Projects via ODBC
          akselsoft

          More on this...

           

          The scenario is that one project has a relationship to a table in another project. When I pull down the list of tables, it shows both tables but I cannot get access to that table.

          Hope that clarifies a little

          • 2. Re: How To Connect to multiple FM Projects via ODBC
            jbante

            In FileMaker, two different database files can access each others' tables more directly, without ODBC. With one of the files open, select File > Manage > External Data Sources. When you add a data source, you have the option for it to be either a FileMaker or ODBC source. Select FileMaker, and add the other file to the file path list. When FileMaker tries to open a connection to the other file, it will attempt to connect to a file at each path in order until it gets a connection that works. Once you have the external data source in place, you can go to the relationships graph and add table occurrences based on tables in the other file. Then you can build layouts, scripts, and calculations using the data from that table occurrence as if the base table was in the same file.

             

            Note that the data-level security settings for data in the external file are still defined in the external file, not the file with the table occurrence reference.

            • 3. Re: How To Connect to multiple FM Projects via ODBC
              akselsoft

              Hi J,

               

              I understand that I can do it within FileMaker - but what if I'm using ODBC to connect to FileMaker from another system. That's where I'm facing the problem.

               

              I'm thinking that I will need to create two separate connections (one for each FileMaker app) and then combine the results.

              • 4. Re: How To Connect to multiple FM Projects via ODBC
                jbante

                In that case, yes. You'll need one ODBC connection for each side you want to make the connection from: one connection for the first file to interact with data in the second, and another connection for the second file to interact with data in the first.