Converting an Access app from Jet database to MySql

[Bob Alston]

Anyone have experience with converting an access app from Jet database
to Mysql? I am specifically looking for any changes I would have to
make to my access forms, queries, modules, vba code, etc. I understand
there are some data format differences in text field types and date
types that need to be addressed if converting from Jet to Mysql. But
other than things that have to be addressed when converting, what other
changes must I make?

Ex. will autonumber fields still work?

Are there more changes to use MySQL than Microsoft's SQL Server?

Bob

[Bookreader]

On Fri, 02 Jun 2006 17:10:31 -0500, Bob Alston <bo********@yahoo.com>
wrote:

Anyone have experience with converting an access app from Jet database
to Mysql? I am specifically looking for any changes I would have to
make to my access forms, queries, modules, vba code, etc. I understand
there are some data format differences in text field types and date
types that need to be addressed if converting from Jet to Mysql. But
other than things that have to be addressed when converting, what other
changes must I make?

Ex. will autonumber fields still work?

Are there more changes to use MySQL than Microsoft's SQL Server?

Bob

I am not trying to start a controversy but why are you switching to
mySQL?

What are the advantages?

I am all for anything that breaks up the Microsoft monopoly a little
bit so I am asking out of interest.

Thanks.

[Bob Alston]

Bookreader wrote:

On Fri, 02 Jun 2006 17:10:31 -0500, Bob Alston <bo********@yahoo.com>
wrote:

Anyone have experience with converting an access app from Jet database
to Mysql? I am specifically looking for any changes I would have to
make to my access forms, queries, modules, vba code, etc. I understand
there are some data format differences in text field types and date
types that need to be addressed if converting from Jet to Mysql. But
other than things that have to be addressed when converting, what other
changes must I make?

Ex. will autonumber fields still work?

Are there more changes to use MySQL than Microsoft's SQL Server?

Bob

I am not trying to start a controversy but why are you switching to
mySQL?

What are the advantages?

I am all for anything that breaks up the Microsoft monopoly a little
bit so I am asking out of interest.

Thanks.

I have gotten interested in MySQL for two reasons
1) for an upsizing database, when Access' Jet is not sufficient
2) Because it is the database used by some software I am testing that
converts Access Forms to web based.
If I can get the access converted to result in web forms, converted from
Access forms, and I can still use Access to access the Mysql data to run
reports, it might be a nice combination.

Bob

[Albert D.Kallal]

I played with MySql + ms-access.

For the most part, things will work quite well. Not a lot of difference then
moving the data to sql server (and, we have several free versions of sql
server now anyway).

A few things:

Code that picks up the autonumber field (identity field) needs changed. You
have to *force* the update, and THEN grab the id

dim rstRec as DAO.RecordSet
dim lngNewID as long
set rstRec = currentdb.OpenRecordSet("yourtable")

rstRec.AddNew
rstRec!City = "Edmonton"
lngNewID = rstRec!ID
rstRec.Update
rstRec.Close
set RstRec = Nothing

At this point, lngNewID is set to the last id created.

When using sql server (or just about *any* server), you have to force the
update, so, all my code (which
works both for JET, and sql server) is now:

rstRec.AddNew
rstRec!City = "Edmonton"
rstRec.Update
rstRec.BookMark = rstRec.LastUpdated
lngNewID = rstRec!ID

Note how we move the record pointer back to the last record. And, if you use
ADO code, then you can execute the update, and not have to move the record
pointer back (so, if you're exiting code is ado, then you likely will not
have the above problem/issue).

So, some code needs to be changed.

further, make sure ALL tables have a primary key. And, *always* expose the
timestamp field to your forms (ms-access uses the timestamp to figure out
what fields to update..and without this, it has to work hard to figuring
things out. In fact, I seem many sub-forms go messy if you don't have the
timestamp.

So, for the most part, I would bet that 90%, or even higher of your existing
code will work. There is a just a few issues like above....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[Bob Alston]

Albert D.Kallal wrote:

I played with MySql + ms-access.

For the most part, things will work quite well. Not a lot of difference then
moving the data to sql server (and, we have several free versions of sql
server now anyway).

A few things:

Code that picks up the autonumber field (identity field) needs changed. You
have to *force* the update, and THEN grab the id

dim rstRec as DAO.RecordSet
dim lngNewID as long
set rstRec = currentdb.OpenRecordSet("yourtable")

rstRec.AddNew
rstRec!City = "Edmonton"
lngNewID = rstRec!ID
rstRec.Update
rstRec.Close
set RstRec = Nothing

At this point, lngNewID is set to the last id created.

When using sql server (or just about *any* server), you have to force the
update, so, all my code (which
works both for JET, and sql server) is now:

rstRec.AddNew
rstRec!City = "Edmonton"
rstRec.Update
rstRec.BookMark = rstRec.LastUpdated
lngNewID = rstRec!ID

Note how we move the record pointer back to the last record. And, if you use
ADO code, then you can execute the update, and not have to move the record
pointer back (so, if you're exiting code is ado, then you likely will not
have the above problem/issue).

So, some code needs to be changed.

further, make sure ALL tables have a primary key. And, *always* expose the
timestamp field to your forms (ms-access uses the timestamp to figure out
what fields to update..and without this, it has to work hard to figuring
things out. In fact, I seem many sub-forms go messy if you don't have the
timestamp.

So, for the most part, I would bet that 90%, or even higher of your existing
code will work. There is a just a few issues like above....

Thanks very much. I knew about the auto number issue with SQLserver but
not aware that it applied to MySQL. YOur code to handle it is much
appreciated.

Bob