6 Replies Latest reply on Jan 20, 2017 4:31 AM by simonrh

    New user for FM15 Pro - ODBC datasource issues

    simonrh

      Hi Community,

       

      You have done me well so far in answering every FM question I have come across by googling and getting community answers so far but I am stuck with an issue.

       

      We have been trialling FM so far using fresh native tables created within FM itself. Now we have found our feet with FM we want to integrate it with a lot of existing, legacy DB information. We need live access that is up to date at point of use

       

      We currently have a large CRM database in "Adaptive server anywhere" (sybase SAP system).

       

      We currently have a large Visual fox pro database for our business system.

       

      Both of these are set up as 32 bit ODBC datasources on our PCs (windows 10, 64 bit).

       

      We uninstalled the 64 bit FM15 installation we have been trying and installed 32 bit to match all our datasources (cant seem to find 64 bit drivers).

       

      These 32 bit datasources can be accessed by other software successfully (Excel, crystal report, MS-Access) but when we try to add them to the relationships graph in FM, i get a "The ODBC data source you have selected is not supported".

       

      I created an MS-Access database that simply opens one table from the sybase CRM database and saved it as a an Access DB. I then  created an ODBC system DSN connection to the MS- access database and tried to open it in FM but again "not supported".

       

      I thought ODBC was pretty generic and worked system wide, are these real faults or am i doing something wrong. This was going to be our "killer" use to bring all the legacy data together.

       

      Thanks,


      Simon

        • 1. Re: New user for FM15 Pro - ODBC datasource issues
          fmpdude

          MS dumped ODBC support, as I read it, for VFP a long time ago. VFP, sadly, is a dead product as of version 9 many, many years ago. I also have a client who is actively using VFP and that client is very nervous that MS might just decide to remove all support for VFP in some service patch. Then, with MS pulling support for ODBC, that wasn't helpful, either.

           

          But, to the rescue ...when I need to access VFP, there is a JDBC driver out there (not from MS) that works well. The JDBC approach works across all platforms also.

           

          Yet, unlike virtually every other JDBC driver on the planet, this VFP driver I use is not free, but it works well with contained (DBC) tables, free tables, MEMO fields, etc.  I guess the company who sells it knows that if you need a JDBC driver for VFP, you must be willing to pay - since options are few. More broadly, I guess, since there might not be much demand, the company needs to recoup R&D. Who knows?

           

          I can give you more information about that driver if you're interested.

           

          HOPE THIS HELPS.

          • 2. Re: New user for FM15 Pro - ODBC datasource issues
            user19752

            "in relationship graph" only limited databases are supported, MS SQL,MySQL,ORACLE (pgSQL, IBM using adapter).

            • 3. Re: New user for FM15 Pro - ODBC datasource issues
              simonrh

              Thanks for that, I think I have 2 issues from reading these posts:

              • Driver availability long term (we have a set of functioning drivers for now)
              • Trying to use them in the relational database with relations, not just as a one-time access / import.

              One of the table sets (the Sybase one) will become static and so we can do a one-time import but the VFP stuff wil be ongoing. The developer for the tool are moving to SQl in the mid-term (just not yet!)

              • 4. Re: New user for FM15 Pro - ODBC datasource issues
                fmpdude

                I can also use the JDBC driver interactively using a database console (or data tab in an IDE).

                 

                The JDBC driver for VFP is actively maintained. I realize JDBC may not be an optimal solution for you, but it works great.

                 

                VFP is an incredible DB tool. From the legendary command window to its extremely low cost of deployment (basically zero) to the rich tool-set it has. Totally amazing.

                 

                Let me know if I can help further.

                • 5. Re: New user for FM15 Pro - ODBC datasource issues
                  simonrh

                  I have gotten so close now to getting something that will work.

                   

                  If i use the "import records" menu I can set it up so that it will update any existing records that have been previously imported to an FM table, add any new ones and keep an FM table up to date by running the import script each time i open the relevant layout. This works just fine now

                   

                  The only issue: The ODBC driver doesn't allow the saving of the VFP DB's username and password. FM has a tick box to remember the username and password but it doesn't stay ticked when run from a script.

                   

                  I will need to have a look because I don't know how to setup a JDBC datasource to see if it would be any better.

                  • 6. Re: New user for FM15 Pro - ODBC datasource issues
                    simonrh

                    Further to my previous comment i can now save UN and PW details. I ahve a working solution, thanks for everyone's help. Fingers crossed.