Results 1 to 5 of 5
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Recordsets and MYSQL (2000)

    Having an issue with Access linked to MYSQL.
    Done all usual Yes/No to TinyInt(1) field stuff, but have dreaded Another User has changed this record message when trying to use the .Update method of a recordset.
    Anythinh I ought to do to overcome this?
    If I create an Update query to do the changes and run that it works ok.
    If I call an Update PROC on MYSQL it works ok, but using a recordset (either DAO or ADODB) comes up with the same message.
    Any hints useful, if anyone has tried using MYSQL with Access.
    Other than that it is working much better than using Access data file.
    Andrew

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recordsets and MYSQL (2000)

    Try setting Option=3 in the connection string of the DSN.

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Recordsets and MYSQL (2000)

    That is genius!

    Just Relinked all the tdf's in a loop to a new connect string with Option=3 and BINGO.
    Strangely it also works (sometime) with 40 (the one that Access PUT on itself when setting up the links)

    Magic Hans.

    Now, just from an academic point of view, what does OPTION=3 tell it to do?
    The default option for a standard Link is Option=40

    The Blurb on MYSQL site says

    Option - An integer for which we can assign a value to tell MyODBC how it should attempt certain tasks

    However it does not seem to have an easy resource to explain what the options are
    Do you have, or can you point me to anywhere that this might be found?.

    I am happy to live with OPTION=3 works, but interested in what I might be missing out on by not understanding the implications of OPTION parameter.

    I read on one site that 0 ought to work, and on another that 3 was usually not enough (but neither said why)

    I tried it with 0 and 16387 and they work too.

    SO, FINAL QUESTION (at the moment) which is kind of related to this

    MYSQL seems to have a real problem if a field has it's own value written back onto itself.
    It is something to do with the way it triggers a change I guess.

    OK, you can sort that with a query or suitable filter Criteria, but is there any other way round it.

    Somtimes it is easier when updating data from another source to just overwrite values with existing ones whether they are the same or not. This behavior applies to Recordsets and Update queries alike regardles of where they are run from.
    I tried various OPTION values but it makes no difference.

    I suspect it is a feature of either MYSQL or the ODBC connector, but it seems to be part of all Versions of the Connector from 2.5x through to 5.xx

    I can resolve the issue by the way I construct the query, but it makes some of the queries a bit of a pain and I wondered if there was a simple parameter setting that did it.

    Any thoughts appreciated.
    Andrew

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recordsets and MYSQL (2000)

    ODBC Connection Parameters gives an overview of the values you can use for Option= (scroll down to see them). They are additive, i.e. to combine the effect of Option=1 and Option=8 you'd specify Option=9, etc.

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Recordsets and MYSQL (2000)

    Thanks Hans.
    Now I see it, it is the same as the list of Options that you can set as part of the ODBC Connector spec for the DSN.
    However, they don't show the values, just the Options to Tick.
    But it helps to see which ones are needed.
    Odd that 0 seems to work as well.

    Oh well. MYSQL is a bit of a learning curve at the moment, but I'll get there.
    Andrew

Posting Permissions

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