You are viewing limited content. For full access, please sign in.

Discussion

Discussion

Forms can lookup data (ODBC) from non SQL db's - Mystery Solved!! Here is how we did it.

posted on November 17, 2016

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!  laugh

 

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. yes

 

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

 

MySQL ODBC.png
MSDASQL Properties.PNG
linked server properties.png
query.PNG
Create New Database.png
Create New View.png
View created.png
Data with joins across multiple tables.PNG
Save & Name View.PNG
Create Data Source in Forms Administration.png
Add Forms Process to Data Source.png
Add Lookup Rules to Form.PNG
Final Result.PNG
MySQL ODBC.png (81.93 KB)
query.PNG (65.69 KB)
10 0
replied on November 17, 2016 Show version history

Glad I could help. You were already 99% there on that last thread :-)

I have added the PDF cheat sheet I hacked together to this thread, it basically says the same as above with screenshots from my environment and a few extra lines of info.

I hope others may get some use from this.

 

Kind Regards Gary Kiebat-Kurnof

1 0
You are not allowed to follow up in this post.

Sign in to reply to this post.