Write standalone program to use MYSQL database

AIGS
AIGS used Ask the Experts™
on
I have a MYSQL database hosted on website
I want to create a standalone program to run on windows PC that will use the MYSQL database to display appropriate data
The standalone program must be able to run external programs that are on PC that the database refers to
Any suggestions on the approach, language etc would be welcome.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
You would need to provide more detailed info on what resources on the PC the application shou,d be able to call on?
Do you have visual studio that you will be creating the application? You would need to get the MySQL odbc driver.......
John TsioumprisSoftware & Systems Engineer

Commented:
The easiest and fastest way for such kind of operations is Ms Access...here is a small demo of a project i am working right now (very early stage)
The data come from my VPS server located over the Internet so you are seeing a data retrieval of 1000 rows with 25 columns and then the transfer to Excel Spreadsheet...
AIGSNetwork Administrator

Author

Commented:
John - I was using MS Access 2010, when we had the data in Access stored on our server, worked well.
Since we changed over to MySQL keeping the data on our website host and after setting up ODBC connection for the same access front-end, I can see the tables and run queries, but using treeview to display the data in a form is not working. It seems the recordset that is created loads too slow  and causes form to freeze! - aigs
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Software Engineer
Distinguished Expert 2018
Commented:
The problem with some tools is that they don't only run select remotely but select all and then filter afterwards.
Which will make it slow.

Did you try to use mysqlworkbench to access the remote database?
(If the database is accross the internet, are you using a TLS(ssl) secured link?)
If the database is remote, it might be far better to create an API that runs on top of the database and access that API from your workstations.
This will create an extra layer of security in that the database can only be changed according to the rules set in the API.
John TsioumprisSoftware & Systems Engineer

Commented:
The problem is that lot of people believe that by switching BE to MySQL...is like a magic pill...well it isn't..it requires considerable homework.
Probably it would be better if you create a Gig...
AIGSNetwork Administrator

Author

Commented:
I have a database which I want to use on externally hosted website and I also need to use it on out internal network. At the end of the day it doesn't really matter if it is in MYSQL or MS Access.
The current front-end (forms) to use the database is in MS Access (in the past this has worked fine when data and forms have both been on the internal network).
As the external website is written in php, I thought I could use MYSQL on external website and use MS Access front-end on internal network (which would link to MYSQL database) - it does work but very slow and causes some issues in retrieving the data.
So, would I be better in having the data in MS Access on internal network and connect to it with php from external website using PDO ODBC to be able to view data on website?
I only want to have one master database but be able to access it from external website and also our internal network - I don't want to have to copy data from one to the other when changes occur.
Am I barking up the wrong tree or is this feasible?
John TsioumprisSoftware & Systems Engineer

Commented:
Well probably you didn't saw my demo ...lets take it again....if you use MySQL as Access its going to be slow...dead slow....if you have a lot of records you would probably "see" timeouts ..and the whole process...is slow...
On the other hand if you use MySQL as MySQL then you will have a database solution that works globally speedy and efficiently...a quick and dirty example right now....fetching 1000 rows and 90 columns on my VPS (1 core Intel® Xeon® E5-2650L v4, 1Gb Ram...over Wifi from accross the street router  (not on the same room) over ADSL ...ping around 2-3 ms...or more ..:)   ) ...time less than 10 seconds....(keep in mind i haven't designed the database)
AIGSNetwork Administrator

Author

Commented:
In the end - after more research I found using dbOpenSnapshot & dbReadOnly with dbOpenRecordSet solved the issues for siaplaying data in a TreeView.
John TsioumprisSoftware & Systems Engineer

Commented:
Well....this my 2 cents

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial