Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2007
    Posts
    4

    Unanswered: MySQL and MS Access integration and performance

    Hi,

    Perhaps I trying to shoot a dead horse, but I would like to see if there is any advice on getting better performance from my application.

    At the moment, the data resides in an MySQL 5.0 database and I use MS Access to provide a User interface (complex VBA etc). However, the performance is far worse then if the data resides within the MS Access application.

    Can anyone suggest techniques of improving the performance between the two databases?

    I simply use an ODBC driver - via Access to connect the two databases using linked tables.

    Also I get an error "[MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt] you have an error in your SQL syntax......" when trying to run a query. The query using specific MS Access SQL - "where exists in (Select....... ) = false" is this the problem?

    thanks for the help

    Paul

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by paulspratley
    The query using specific MS Access SQL - "where exists in (Select....... ) = false" is this the problem?
    yes it is

    that syntax is invalid in both databases

    the correct syntax is
    Code:
    where not exists (Select....... )
    performance depends on indexes -- have you defined the proper indexes in mysql?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2007
    Posts
    4

    Thanks

    Doh! - yes it would help if I set indexes on the MySQL!!!

    Cheers

    Paul

Posting Permissions

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