After many hours of researching, we finally managed to to be able to lookup data from a non SQL server database using ODBC and linked server in SQL!  

There are dozens of posts on LF Answers where people have been asking this same question for a long time, and I could not find any detailed answer on how to actually do it. So, i thought I'd create this new post (sorry if you prefer this be added onto an existing post) and share my detailed steps we used to crack this tough nut. I am a complete novice with SQL, so I hope this gives some of you some confidence that if I can do it, you should too. 

In our situation, we need to access data from MySQL. This should be able to be done with any ODBC connection to other db's. This is a simple table example of how we did it:

1. Set up a user lfforms (PHPMyAdmin) in MySQL. Install and configure the MySQL ODBC driver that tested successfully (see attached).

2. Modify the Server Objects / Linked Servers / Providers / MSDASQL properties (see attached)

3. In MS SQL Server Management Studio set up a linked server (following various different instructions on the net).

4. Set the new Linked Server properties (attached).

5. I can view the database and all the tables as well as query a simple table (attached).

6. Create a New Database in SQL: Right Click on Databases folder | New Database

Name the Data Base | Click Ok (you can adjust further settings here)

7. Create New View: Within the New Created Data Base | Right Click on Views | New View

Close the Add Table pop up box

8. Refer to screen shot (Create new View) and enter in the SQL syntax by completing the same pattern by that I have used and specify the columns you require. 

Note: you can take this further by creating joins across multiple tables. I have attache da screenshot of an example my colleague used.

9. Right click and select Execute SQL

10. You should have results from this query displayed (see attached)

11. Save and name the View (Ctrl-S)

12. Go back to Forms Administration and on the Data Source tab create a new Data Source and test the connection (make sure you have the correct Database name from Step 6).

13. Add your forms Process(s) to this Data Source

14. Go to your Form process and into the Lookup rules add your new create DB Data source

15. Test and it should work!!!!

I believe there is an alternative way to replace Steps 6- 11 by using Stored Procedures, which I had tried on a previous post in LF Answers, but I was unable to get this working.

Kudos to my colleague Gary for his help in those final pieces to this puzzle!

Enjoy!

cheers

Grant