Professional Web Applications Themes

Any SQL Query Differences between Access and mySQL? - MySQL

I've got an application that uses the Microsoft Access Jet database and I'd like to rewrite it to use mySQL because it's going to get too big for Access. Can someone recommend a good reference on the SQL language differences between Access/Jet and mySQL? Ed...

  1. #1

    Default Any SQL Query Differences between Access and mySQL?

    I've got an application that uses the Microsoft Access Jet database and I'd like to
    rewrite it to use mySQL because it's going to get too big for Access.

    Can someone recommend a good reference on the SQL language differences between
    Access/Jet and mySQL?

    Ed


    Edwin Guest

  2. #2

    Default Re: Any SQL Query Differences between Access and mySQL?

    Depends on the version of MySQL.

    Here is a good place to start:

    [url]http://dev.mysql.com/doc/refman/5.0/en/index.html[/url]

    "... too big for Access..."
    I'll assume you meant "too big for MS Jet", as you still intend to use MS
    Access as the Front End.

    How many users?
    What are the logistical considerations?

    Anyway, if you migrate the back end:

    As long as all table and field names remain unchanged, and the data types
    are "basically" similar to what you already have, you should be fine with
    your existing interface. Get your tables moved over, backup, delete all
    tables, link all back in from the new source. Everything should still
    operate, with only a few issues, a couple of which are:

    1) If you have autonumbering or backend-driven field auto-population, the
    entire record shows "deleted" in all fields just after you leave it, but it
    is not actually gone; you just need to requery. However, this in turn
    forces the cursor back to the beginning of the record set, which makes it a
    bit frustrating. You may consider adding coding auto-requery and to move to
    the latest entry or new record row.

    2) If you begin to edit a record, but end up making no changes, and forget
    to ESCape yourself out of edit mode, you will get a database write conflict
    error, but it doesn't mean anything, and nothing is wrong; it just means the
    update failed because there were no changes. Problem is, depending on your
    design, it may lock up your MS Access interface, especially if you have any
    code-based updates with little or no specific error handling. In
    straight-forward RAD forms, and direct table/query edits, it won't lock up,
    but you will still see the message. Just cancel the update on the dialog.

    3) If your logistics are local (no-one off site), and your concurrent user
    count is low, you may consider getting MS SQL Desktop Engine (a throttled
    lesser version of MS SQL Server), which can be directly accessed similar to
    native MS Jet tables, and you lose the previously mentioned issues (and
    other issues).

    There are more issues and considerations, but you learn as you go. HTH.

    Cheers!

    "Edwin" <ededdie.com> wrote in message
    news:osCdnQg0ff1-EkvZnZ2dnUVZ_vednZ2dcomcast.com...
    > I've got an application that uses the Microsoft Access Jet database and
    > I'd like to
    > rewrite it to use mySQL because it's going to get too big for Access.
    >
    > Can someone recommend a good reference on the SQL language differences
    > between
    > Access/Jet and mySQL?
    >
    > Ed
    >
    >

    Duane Phillips Guest

  3. #3

    Default Re: Any SQL Query Differences between Access and mySQL?


    "Duane Phillips" <duane.phillipsaskme.askme> wrote in message
    news:FbidnVA0DcIf3kTZnZ2dnUVZ_qmdnZ2dgiganews.com ...
    > Depends on the version of MySQL.
    >
    > Here is a good place to start:
    >
    > [url]http://dev.mysql.com/doc/refman/5.0/en/index.html[/url]
    That is a huge reference manual for mySQL. it doesn't answer my question as to how to
    migrate from Access/Jet to mySQL.
    >
    > "... too big for Access..."
    > I'll assume you meant "too big for MS Jet",
    It's generally accepted that when someone mentions "Access database", most people
    realize that it is Jet. Duh.
    > as you still intend to use MS Access as the Front End.
    No, not at all - I'm accessing the database from an ASP web application.
    >
    > How many users?
    How the hell do I know? The application isn't up yet!
    > What are the logistical considerations?
    How many ways do you want me to answer that question?

    Actually, I migrated it over to MS SQL and there was no difference in the query
    language.

    In all honesty - and please, I'm really not trying to start an arugment here - the major
    difference that I have learned over the past few days between mySQL and Microsoft SQL is
    the support. Microsoft newsgroup people seem to know exactly what I am looking for and
    answer it with relevant examples.

    mySQL people want to argue terminology and take a stance of "I'm smarter than you are".
    Maybe I'm a beginner with databases, but that's no reason to give me a hard time.




    Edwin Guest

  4. #4

    Default Re: Any SQL Query Differences between Access and mySQL?

    It's cool! I'm not giving you a hard time...

    Just trying to be helpful, and asking questions; I don't quite frankly care
    who is smarter.
    My apologies for assuming your front end was Access. However, I did say I
    was assuming. That should have been my first question. So it's a web app.

    As far as the "friendlies" go, I've been developing MS Access and MS SQL
    Server, for years and now currently upgrade and develop for a company on an
    internal project based on a begrudgingly MySQL backend, so, I would not
    class myself in the "MySQL" groupie world... nor in any way shape or form am
    I trying to hang out how smart myself or anyone else is.

    The link I gave you *is* the starting source for migration from anything
    else to MySQL, for the latest version.

    Peter H. Coffin also responded with a more specific link to your question
    *from the same site*:

    [url]http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html[/url]

    There are major differences in the SQL language supported by each version of
    MySQL. For instance, anything below 4.1 does not support SQL sub-selects
    (one of my current major pains as my current employ is on version 4.0...).
    Not only that, but the actual database backend you select to use with MySQL
    has different capabilities.

    MySQL itself is NOT a database. It is a pluggable architecture that can use
    *many* different backend database engines. It ships with several, but there
    are others you can download and plug in. Think of MySQL as the server
    engine that processes the SQL requests, but ps and passes the bits of
    "work" onto the DB table-type of your choosing, then receives and funnels
    the response back to the requester. The most popular two DB Engines used
    with MySQL are InnoDB and MyISAM. A single instance of MySQL can support
    and use multiple types at once, so you are not locked into just choosing
    one.

    MyISAM is fast on inserts, but has no transactions, no foreign constraints,
    and does not have record level locking.

    InnoDB is slower on inserts, but has most of the bells and whistles of a
    transaction safe DB, and is ACID compliant.

    I truly hope this has been of help. Please look especially at the section
    on Peter's link titled, "Why You Should Not Migrate to MySQL", and those
    just below it.

    Cheers!

    ~ Duane Phillips.

    "Edwin" <ededdie.com> wrote in message
    news:xcOdnYJL_ro820fZnZ2dnUVZ_sidnZ2dcomcast.com. ..
    >
    > "Duane Phillips" <duane.phillipsaskme.askme> wrote in message
    > news:FbidnVA0DcIf3kTZnZ2dnUVZ_qmdnZ2dgiganews.com ...
    >> Depends on the version of MySQL.
    >>
    >> Here is a good place to start:
    >>
    >> [url]http://dev.mysql.com/doc/refman/5.0/en/index.html[/url]
    >
    > That is a huge reference manual for mySQL. it doesn't answer my question
    > as to how to
    > migrate from Access/Jet to mySQL.
    >
    >>
    >> "... too big for Access..."
    >> I'll assume you meant "too big for MS Jet",
    >
    > It's generally accepted that when someone mentions "Access database", most
    > people
    > realize that it is Jet. Duh.
    >
    >> as you still intend to use MS Access as the Front End.
    >
    > No, not at all - I'm accessing the database from an ASP web application.
    >
    >>
    >> How many users?
    >
    > How the hell do I know? The application isn't up yet!
    >
    >> What are the logistical considerations?
    >
    > How many ways do you want me to answer that question?
    >
    > Actually, I migrated it over to MS SQL and there was no difference in the
    > query
    > language.
    >
    > In all honesty - and please, I'm really not trying to start an arugment
    > here - the major
    > difference that I have learned over the past few days between mySQL and
    > Microsoft SQL is
    > the support. Microsoft newsgroup people seem to know exactly what I am
    > looking for and
    > answer it with relevant examples.
    >
    > mySQL people want to argue terminology and take a stance of "I'm smarter
    > than you are".
    > Maybe I'm a beginner with databases, but that's no reason to give me a
    > hard time.
    >
    >
    >
    >

    Duane Phillips Guest

Similar Threads

  1. Replies: 2
    Last Post: March 7th, 07:18 PM
  2. Replies: 0
    Last Post: November 4th, 05:38 PM
  3. Differences between MySQL 5 and MaxDB?
    By Duane Phillips in forum MySQL
    Replies: 5
    Last Post: November 3rd, 01:48 AM
  4. [PHP] PHP - MySQL Query...
    By Jay Blanchard in forum PHP Development
    Replies: 3
    Last Post: August 14th, 11:43 AM
  5. Query / mySQL / Access
    By John Smith in forum ASP
    Replies: 1
    Last Post: July 18th, 03:38 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139