Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    5

    Angry Unanswered: MS Access slow with MySQL

    Hi folks,

    I've just converted a relatively substantial database to use a MySQL 4.0.20a-nt back end with Access 2002 front end. All my indexes came over nicely and I've put the timestamps in as recommended for use with MyODBC 3.51.

    Unfortunately when I bring up my forms they now take an age to show. What's strange is that the processor occupancy and disk utilization are at 0%, so it seems like Access is just suspended waiting on something.

    Has anyone encountered this before and if so, what did you do to overcome it? It's really frustrating because I can see that there will be real advantages to using it once I get this speed issue resolved.

    Thanks in advance.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    How are you accessing the tables? DAO? ADO? Direct link?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jul 2004
    Posts
    5

    Table access method

    Thanks for the quick reply. I'm accessing the tables via linked tables connected to the MyODBC 3.51 driver.

    It's a front end that was developed using Access's split tool that created an Access back end and front end. The front end's forms are linked to the tables as is the default with the form creation wizard. Most of the forms have some linked subforms, too. I'm concerned that I may have to go through and change the way all this works so that the forms don't link directly and works a bit more like a proper database!

    Does that answer your question?
    Last edited by eepgmik; 07-27-04 at 09:38.

  4. #4
    Join Date
    Jul 2004
    Posts
    64
    How many records are you pulling into your forms? The number of form/subform records being shown can greatly decrease your performance. You might also try linking to stored procedures and using pass-through queries for any forms where data does not need to be entered. You might also try looking at linking via an access project file, although I am not sure if those will only work with SQL server or if they work with MySQL as well.

  5. #5
    Join Date
    Jul 2004
    Posts
    5
    I didn't really know what pass-through queries were before today; I'll have a look at whether that could work. The forms are currently used for update as well, which is a potential problem.

    The table that is being referred to holds about 32k records, but I'm not convinced that it's reading them all in at the time.

    The strange thing is that both the MSAccess.exe and mysqld-nt.exe processes are idle when the form is being displayed and Access becomes unresponsive. When Access is normally doing a lot of work with the table data it runs to 100% occupancy. I'm quite confused as to what it's actually doing!

  6. #6
    Join Date
    Jul 2004
    Posts
    64
    Pass-Through queries will not work for an updateable form, as the recordset they get is read-only. You still might try them though, to limit the number of records being gotten to see if that will help speed things up. I currently use SQL server (well, MSDE) here to hold data, and access seems to have a hard time pulling in more than 20k records with an access project... I am actually looking into converting over to MySQL here

  7. #7
    Join Date
    Jul 2004
    Posts
    5
    Ok, thanks. I'll give it a go and see if that makes a difference. I'm learning more about how Access works with ODBC and get more worried as I go! I might have to do some fairly modifications to make this work... I'll post my progress with it.
    Last edited by eepgmik; 07-27-04 at 09:47. Reason: Wanted to add more info

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •