4 Replies Latest reply on Jun 4, 2010 8:11 AM by UncleGeo

    System or user DSN not showing up in FMP 11 and Windows 7 64 bit



      System or user DSN not showing up in FMP 11 and Windows 7 64 bit


      My setup:

      1. FM Pro 11.0v1. In the properties for FMP11 under "compatibility" I do NOT have "Run this program in compatibility mode" checked and no operating system is chosen.I also do NOT have 'run this program as admninistrator" chosen.
      2. Win 7 64bit (I now know it's not certified)
      3. I am not sharing the database across a LAN or Web (except for a MySQL connection with ESS) and I am not using IWP or CWP.
      4. I have MySQL Connector ODBC installed
      5. I have a static IP on cable modem from The Evil Comcast.

      About Me:

      Though I've been using FM since it was black and white on an Mac Classic, I'm more a power user than developer. I'm a newbie to MySQL but I've made a database and PHP'd it and my site works fine. I'm not afraid to blow around in stuff  to make it dance.


      The Good News:

      I have an FM database of product listings connected to a MySQL database at a server that feeds my website (in php) so as I update my FM, the MySQL is instantly updated -and I think that's all just fargin magical! This was all set up on this Win7/64 machine with this install of FMP11.0v1 shortly after setting up Win7/64 on a new system hard drive.


      My Problem:

      Flush with success and feeling cocky, I needed an almost identical setup for a new biz project so I cloned the working FM database and gave it a new name and closed the window for the old db. Then:


      • I created a new MySQL database at the server (i.e. totally new and separate FM db and MySQL db -not a separate table off the old MySQL).
      • In Windows Data Sources ODBC, MySQL Connector/ODBC I made a new connection to the new MySQL -test says "Connection Successful!"
      • In FM  "Manage External Data Sources" I deleted the old ODBC connection for the original FMdb that came over in the clone.
      • I then attempted to create a new connection in Manage External Data Sources/Edit Data Source to connect to the new MySQL. I chose ODBC, then,
      • Under DSN I clicked Specify and I believe I should see both the old and the new ODBC connections I had set up in Windows, but I see no choices at all. Nada. Bupkis.
      • I did also enter a user name and password to the MySQLdb under Authentication. (That did not help).
      • I called my web hosting folks at Blue Host to be sure there were no permissions to enable for the new user/pass for the new MySQL db. There weren't and they report they can see a connection when I test the MySQL Connector ODBC data source config.

      So, I have no connection on the new FMdb to the new MySQL.

      Now I mentioned the original database -that sucker still works fine. It was set up on this same machine with this same install of FM Pro11 and I was able to choose system and user DSN when I first set it up -but now if I attempt to go back in, hit edit and "Specify" the DSN for that old db, I don't see any choices nopw. I quick hit cancel so as not to screw up a good thing and it remains working to this day.


      So, here I sit, flummoxed.



        • 1. Re: System or user DSN not showing up in FMP 11 and Windows 7 64 bit

          Make sure that you are using the 32 bit MySQL driver.

          • 2. Re: System or user DSN not showing up in FMP 11 and Windows 7 64 bit

            Thanks mr. vodka.


            I uninstalled the 64 bit version of MySQL Connector/ODBC and used a 3.51.27-win32 installer I had from when it worked on my old XP machine. Then, setting up a system DSN in Windows "ODBC Data Source Administrator", I clicked "Add" but no MySQL ODBC connector showed up (just a SQL server from MS). I uninstalled and reinstalled this a few times -no go.


            I uninstalled the 32 bit and tried a newer version -MySQL Connector/ODBC 5.1.6 -win 32 that I got from the MySQL site; Same problem, no choice for MySQL driver. Uninstalled, reinstalled a couple of times -no go.


            Uninstalled that and downloaded the newer MySQL Connector/ODBC 5.1.6-win 64 to go back to the 64 bit version (which did work before on one database anyway) and now I could see a choice for the "MySQL ODBC 5.1 Driver". Configed this with user, pass, IP and port. Test connect successful!


            But, alas, in FM it would not connect upon startup; entering user and pass did not help. Under Manage/External Data Sources I deleted the old connection and chose a new Edit Data Source.  Clicked  the ODBC radio button. I understand FM should have found the Connector I set up earlier in Windows and  automatically stuck it in the DSN field but DSN shows nothing. I click Specify and there is no connection to choose. I specified user and password anyway but that did not help.Restarted for the hell of it -no go.


            Maybe FM is looking somewhere for the connector but the connector is somewhere else?Maybe when I installed the 32 bit version it went into the wrong place? (I did notice it did not give me any option as to where to install it.)


            Still stumped.



            • 3. Re: System or user DSN not showing up in FMP 11 and Windows 7 64 bit

              Got a little farther -and this may be important for Win7 64 bit users. Found this on a message board:


              "In a 64 bit windows server operating system, there are TWO odbc managers. When you pull up the usual menu for the odbc / dsn system, it is for the 64 bit odbc manager, and 32 bit applications (vb 6.0) will not work using these dsn's.

              This is where the 32 bit odbc manager is:



              It suggested launching this second one and configuring everything again. It was tricky because the settings were in there already. I looked at the config and just saved it again but it told me I did not have admin privleges and erased the config. I chose to add a new one and it let me do it and I saved successfully!


              Now I can see the MySQL choice in FM's Manage External Data sources. I choose it but when I save my fields are all still showing "<File Missing>".


              But I feel like I'm closer!

              Any ideas?

              • 4. Re: System or user DSN not showing up in FMP 11 and Windows 7 64 bit

                OK, tried a couple of things, not sure which one did the trick. I think mr. vodka was on to something (how could it be otherwise with a name like mr. vodka!). In a nutshell, here is what I think is going on: It needed the 32 bit driver, as our inebriated friend suggested, and also the insight that there are two places (SysWOW64 and System32 folders in the Windows folder) to go configuring this all. Finally making sure to re-connect the table instance in the relationship graph to the remote table finished things off. Here's what I did:

                I knew I needed the 32 bit install so I dredged up the 32 bit driver install and tried to install it without uninstalling the 64 bit. It told me I could not do this. Tried the manual install download of Connector ODBC but found the instructions impenetrable and bailed. Then:

                I went into the Control Panel/Programs and Features, right clicked the MySQL Connector ODBC and chose to "Change" it (rather than uninstall or repair). I chose "modify" in the installer/uninstaller dialog and in that window I found choices for both 32 bit and 64 bit drivers (and also the documentation). How that happened I have no fargan idea. Clicking the caret next to the 32 bit driver I chose "this feature and all subfeatures will be installed on this hard drive". The icon did not seem to change but it did take that choice. I clicked "Next" and it installed. (My guess is that I may have installed the 32 bit somehow or it was left over from some previous install -or it comes with the 64 bit version of the installer -who knows!?)

                Next, in Filemaker now, figuring I needed to connect the table in FM with the remote MySQL, I chose Manage Database and highlighted the remote table that had been working so long ago in my old XP (and even in Win 7 for a while with just the 64 bit driver installed - go figure). I clicked the pencil icon (BTW, these icons really need to self identify when you hover) and in the resulting dialog it let me choose the remote Data Source and the Table name. Dismissed dialog, clicked OK in Manage Database.

                ...and it works! Yipee!

                Being a power user and not a programmer I can't tell you which of these did the trick or what part of what I did in the above posts set this up to work, but hopefully this will help someone. Maybe someone smarter than me can parse this and explain.

                BTW, I've been using FM since before it was relational on an old classic upright B&W Mac with the 7" screen.  Just freakin love it!