Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    173

    Unanswered: Access and MySQL

    I am currently contemplating moving a back end Access database to MySQL and I have a few questions. I want to do it specifically to improve speed issues. The current front end has about 20 bound forms in it and only about 5 users.

    1. Is this advisable?

    2. Is this usually done with linked tables? If so are bound forms advisable and if not am I not better off dumping Access entirely and using VB?

    Any replies appreciated. Thanks in advance.

  2. #2
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    I actually know nothing about MySQL but I have done a lot of research about migrating an Access database to SQL. My understanding is that with 5 users you will probably not see a performance difference. The number of forms is not really relevant, it is mainly the number of users.
    w

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    nobody else wants to jump in, so you "benefit" from my biased (and possibly eccentric) opinion:

    i use mySQL for multiuser, not for speed.
    it is true that mySQL seems to win in the SELECT TOP 100000000 tests but this capability never features in my applications - it is rare that i select a complete record into a form, let alone many thousands of records.
    (reports are a different story of course, but users seem to get less upset if a big report takes 5 seconds to preview whereas 5 seconds for a form is seen as seriously bad performance)

    i use only unbound forms

    i no longer link-table to SQL-aware machines - i did try for a while, but i got frustrated with occasional "semi-freeze" situations where nothing seemed to happen for a few seconds (and i'm talking of my development environment with the mother of all servers and just little me as the only user and the only LAN traffic). i'm not a guru on the inner workings of what happens when you link, but i suspect that it is not the optimum use of a very fast SQL engine. my goal is to make the server do the work and then minimise the stuff pumped down from the server: i'm not convinced that linking helps that goal.
    if i need to bind something (e.g. a combo) then i do it against a local copy of the lookup table and impose a check-and-update-if-necessary regime for the local tables - an occasional one second delay with on-screen apology seems less frustrating for the user than the click-nothing-happens experience.

    sorry if this is not particularly helpful - it looks like i'm headed down a different direction from the one you propose.

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Apr 2004
    Posts
    173
    So...... are your forms building recordsets as necessary and then populating the form when a user wants to find and then edit a record and then writing a record in an INSERT statement? It might seem that alone may cure some of my problems. (of course brand new PCs and a faster LAN would help too) Secondly, if you're not linking tables and using the Access's handy dandy forms and controls why not just code the ap in VB and skip Access entirely? Any reply appreciated eccentric or not.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ummmm, yesno

    no part:

    you can't take the SQL-serving machine out of the equation and do the same with a backend .MDB

    ...a backend .MDB is just a dumb file - anything that happens, happens on the client. if you go for a
    SELECT whateverShouldReturnOneRecord;
    the client gets the whole lot anyway and throws 99.99% of the LAN traffic in the bin ----- that's your speed issue .

    yes part:

    i do indeed make a rst (often only part of one record)
    ...and populate my form from it
    ...and run an optimistic update on the server table after an edit
    (keep rst in memory --- and build SQL whenever currentValue <> rstValue
    UPDATE tblStuff SET fldThis = currentValue WHERE fldThis = rstValue
    (with unique ID in the WHERE as well of course))
    ...and check .recordsaffected
    if it's 1 - life is cool
    else - either your db went banana-shaped or someone else wrote to your record after you read the rst
    (take care here: DAO.database does NOT update .recordsaffected, so you need to use .connection)

    does it work: yessssssssss


    izy


    LATER EDIT:
    .recordsaffected works fine with most ways of addressing JET but the moment you play ODBC/OLLEDB passthru games to a server, you MUST use .connection to have a meaningfull return in .recordsaffected
    Last edited by izyrider; 01-28-05 at 14:37. Reason: TRYING TO BE CLEAR!
    currently using SS 2008R2

  6. #6
    Join Date
    Apr 2004
    Posts
    173
    I think an arrangement like your yes part would probably serve my purpose. My problem seems to come in when I use a certain subset of forms especially the ones with large recordsets. I've attempted the first part and that seemed to help some of the time especially when I removed the form's recordset at close and then rebuilt it at open with only a small set of records. Problems come in though when the user wants to find a whole bunch of records to look at and they run the open part wide open and attempr to return the whole table. So I take it that in terms of user functionality your nav buttons navigate your DAO recordset, any record searches rebuild the DAO recordset, any filters filter on your DAO recordset, and all of this is reflected in your form by a procedure to populate the forms controls. Sounds like this could fix my problem. If I've got the gist. Thanks again izy.

Posting Permissions

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