Project Detail

Description

I have a serious delay problem in access forms when linking to tables linked to mysql database with 1 million records or even much less records. Once the form is loaded it works fine and it takes a split second to find any record I am searching for. The form should open up at this same speed when it first opens since it only has to open one record. When the form opens up its getting or checking too many records I think.
Below are some testing I did.

1. I double checked to make sure it was not a problem with my form. I removed the data source behind it and it opened up in mil seconds.
2. I made a query which would filter for one specific record and attached this query to my form. The form found the record and opened up in a around one second. I than pasted the sql code behind the form and the form again opened up in under a second which is real good considering my mysql database has a million records. And being accessed 4 states away using a 30kb connection.
3. I also linked to another access table in another access database on my 100Mbt network with the same number of records and my form opened almost instantly. But when I put a mysql on the same server as my access database still using 100MBT connection it was more then 20 times slower. Something is slowing down the ODBC drivers or it’s the ODBC drivers itself not configured right . If I open a query that is filtered or a from that is attached to a filtered quarry it opens instantly so long as I filtered for only a few records at a time.
4.

Below is the user name and password to one of my mysql database and a table called testdata with one million records. I need a way to link to this in access and make a form that opens instantly without having to open the form using filters. I am looking for some simple code that can tell access to get one record on opening or learning how to configured odbc 3.5 driver so it performs better assuming this is whats wrong.

Also appending data is terribly slow even on the same network compared to appending records to another access database on the same network and server as the mysql database only not using the same odbc drivers. Even a mysql database on the same computer is slow.

Updating is even worse. If I were to update a million rolls in a single character column with a single character in this same table it will take hours with access but if I used other programs like SQLYOG it only takes matter of seconds to run the same sql update statement. Also it only takes seconds if I update linked tables to another access database across the network. Since its the same character it should send it to MYSQL and the hosting the mysql database should run the statement.

I need to get my odbc drivers or Microsoft Access configured right so there is not such a delay in working with this table. I don’t need someone to do this for me. I just need to know what I am doing wrong assuming I am doing something wrong. The first person who gives me the answer to fix these issue wins the bid @ $30.
Right now I am not going much higher than $30 so don’t spend much time on it if you don’t aleady know the answer. I rather just go post it on mysql forum and probably get a free answer.


User name = testdata
Password = testdata
Server IP = 209.40.226.99
Database = testdata
Table = testdata



Additional information submitted:

04/11/2008 at 19:00 EDT:
User name = testdata
Password = testdata
Server IP = 209.40.226.99
Database = testdata
Table = cc_ratecard
Note. corrected table name