0

I have a front end MS Access running through a backend MySQL database via ODBC, I recently moved MySQL database from a local LAN server to a hosted WAN server on Amazon RDS. Now all of my queries are running very slow. By searching online I found the best way is to rewrite the queries in pass through mode so stored procedures are not running locally in Access. I have tried VPN vs ODBC, didn't help.

Problem with this approach is that I have thousands of queries (saved and in code) that run sums, dlookups and other Access procedures, it will take months to rewrite all of them correctly so the queries run on the hosted site.

Is there another quicker fix solution to this problem?

migrated from stackoverflow.com Dec 10 '17 at 19:20

This question came from our site for professional and enthusiast programmers.

  • No - either get a (much) faster network connection, or use Pass-Through. But you should first test a few queries if they run fast enough via Pass-Through. – Andre Dec 10 '17 at 18:44
0

You could run Windows server on AWS EC2 or a Windows Desktop using Amazon Workspaces and try running Access there in AWS's network environment. That may significantly improve performance. Another thing to consider is that network performance can be limited on micro instances.

There might be other advantages of hosting your whole application in the cloud in terms of uptime.

  • Thank you so much, pass through queries would be the obvious solution but would be too labor intensive, I setup a test Amazon EC2 server and put the MySQL database on Amazon's RDS and it works beautifully! thanks, – Alan P Dec 14 '17 at 14:14
  • Glad you liked it. There are numerous advantages of hosting as much of your application as possible in the cloud (uptime, DRP Conciderations, ECT). If you get to a point where you start looking at refactoring these queries as pass through queries, be sure to look at the rest of Amazon's stack. They offer many services that can aid in the creation and maintenance of business workflows (Athena, Lambda, S3, SQS). – Zerodf Dec 14 '17 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.