Developer Forum

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

Depends on the version of MySQL.

Here is a good place to start:

http://www.yqcomputer.com/

"... 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!

That is a huge reference manual for mySQL. it doesn't answer my question as to how to
migrate from Access/Jet to mySQL.


It's generally accepted that when someone mentions "Access database", most people
realize that it is Jet. Duh.


No, not at all - I'm accessing the database from an ASP web application.


How the hell do I know? The application isn't up yet!


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.

http://www.yqcomputer.com/

I'm surprised you didn't stumble on it yourself. That's the third Google
hit searching for [ms jet mysql difference]. It provides a rough
overview of the KINDS of issues you're likely to encounter, enough that
a reasonably clever DBA can work through them, including describing
tools that can assist in the process.

t'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*:

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

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 parses 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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...