MS Access to mySql connection via odbc slow

Question

i wish if anyone could help me out with this, i have split my MS access database to mysql server, and connected via odbc driver, and directly connected my ms access table. when i'm opening my ms access front end on local network its speed is fine, however when i connect through internet the speed is too slow, it take 2-3 sec to open and show queries. speed of my internet is very good, i'm surprised that i can open remote desktop and work from it opening ms access at faster rate. tracing at odbc is off please let me know is there and coding required to be written at vba instead to dirrect connection. pls guide i'm not expert. Thank you in advance.

Answer 1

Well, what is the speed of the internet connection compared to your local office LAN?

Even the most el-cheap-o office network these days is 1000T. They used to be 100T, and before that they were base 10T.

So, this is really simple grade 3 math.

Say your internet is 15mbits. Heck lets say it is 50mibts. But then again, that is your download speed!!! When you hit the work server, then THAT END is limited by its up-load speed. And even with a good plan, that up-load speed is limited.

So, lets do some grade 3 math. Lets assume that your iternet connection is 50mbits (it might be one way - but I doublt VERY much your up-load speed is rated that good.

So, 1000/ 50 = 20!

Wow!!! A whopping 20 times slower!

Ok, so now we are asking: How come a 20 times slower connection speed is 20 times slower? As Homer simpsion would say? Duh!!!

So, a 4 second delay now is 80 seconds!!! But then again, I REALLY doubt you have any thing close to a 50 mibts connecton - and when you hit the server, you are LIMITED by not the download speed, since you pulling from that server. So now you limited to up-load speed. Say, 15 mibts. Ok, lets do some more grade 3 math:

1000 / 15 = 66!

Ok, so now we are 66 times slower. A 5 second delay now will be 5 x 66 = 333 seconds (or FIVE AND ONE HALF MINUTES!).

So: No, your internet connection might be fast - but WHAT are you comparing that speed to? Since if you comparing it to your el-cheap office network with a 1000T rating? Well, then you not even close, and by all standards your internet connection IS NOT FAST at all!

I mean you can walk fast, but driving a car is a VERY different process.

Of course, one thing you can do is ensure that the access forms do NOT pull a lot of data.

So, if you link a form to a table with 500,000 rows?

Well, if you JUST launch the form? Then it will start pulling the data. After all, it ONLY doing what you telling Access to do.

However, if you do this:

dim strInvoiceNum = InputBox("What invoice number")

docmd.OpenForm "frmInvoices",,,"InvoiceNum = " & strInvoiceNum

Now, that same form bound to the linked table of 500,000 rows? Only ONE row will come down the network pipe. So, good performance is MOSTLY a question of good designs. If your desisgs are telling Access to pull lots of data into a form? Then you VERY much notice a slow down. But you NEVER noticed it before because you did NOT have to worry about a VERY LIMITED internet connection compared to a 50 or more times conneciton speed you had with the office LAN.

So, because you had such good speed before, then you never had to spend time worring about how much data your pulling into forms, and thus you did not care.

Now, in above, I used inputbox, but in a typical access applicaton you will build NICE UI and nice forms that prompts the user for what to work on.

This is not really different then how google works. You don't download the WHOLE iternet into the browser and then hit ctrl-f to search a HUGE page with tons of data. What you do is prompt - search, display some results, and then let the user select the results.

You then launch the form with a "where" clause. So, even for a form 100% directly bound to a large table? If you open it with a where clause? It only pulls the 1 record, or the several records down the network pipe.

So, you could say build a search form like this:

I typed in smi. So, in the after update event of the text box, I go:

me.RecordSource = "select * from tblCustomers where LastName like '" & ttextbox & "*'"

And when the user clicks on a result? I open the form to ONE reocrd with the where clause:

eg:

Docmd.OpenForm "frmBooking",,,"ID = " & me!id

So, even if the table I am searching has 100,000 or even a million rows? Well, pulling 30 records, or clicking on a row? Well, the above opens the form to the one row (and this is EVEN if the form is bound directly to the huge table - only the ONE row is pulled).

In fact, be it a accounting package, google, or ANY software you ever used? The forms don't pull down a whole table and THEN have you use ctrl-f to seach a HUGE data set that you pulled down for no good reason.

Even with Access as a back end file share? 5 users on a typical office network should have produced BLISTERING performance. However, once you adopt a VPN or a internet connection between Access and the back end? Well, then the back end can't be a file accDB (access one). the reason is that a file share network tends to be too slow for internet speeds.

However, if you adopt the above performance tips for Access and a server based back end? Then yes, you can get rather decent performance - but only if you the developer spends a wee bit of time restricting the data you pull into such forms.

Regardless? Based on simple grade 3 math? You do not have fast connection at all compared to what you had before introduction of the internet between access and the back end database.