anybody use MySQL?

any different than using MS SQL in terms of linking tables etc...
any gotchas?

I use it for one of my dbs as the back-end. Download the ODBC drivers and
link as you normally would. i have not had any issue thus far (3 yrs and
counting).

Why make the change though? Do you have a need not met by Access?
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"mcnews" wrote:

any different than using MS SQL in terms of linking tables etc...
any gotchas?

I have developed and implemented a project using MySQL as back-end to Access
using bound forms. Quite easy to use.

You, however, do have to download and distribute the ODBC driver to every
client who needs access to it. You can get it for free from MySQL; it's
called MyODBC and version should be 5.1, I believe.

One gotcha I remember is the date/time difference. Access may not be always
able to handle MySQL's date, time and timestamp consistently. For maximum
portability, stick to MySQL's datetime. You still can use timestamp if you
need it for logging updates as long Access does not have to interact with it,
however. I've found Access and MySQL handle datetime quite well, despite the
difference in format (MySQL prefers ISO format yyyy-mm-dd hh:mm:ss while
Access prefers US format mm/dd/yyyy hh:mm:ss)

Like MS SQL, you have to avoid using bigint as Access cannot support this
and will display #Deleted if the column is returned.

I also wouldn't bother with MySQL's extension data type such as set and
enum. They may be cool, but for ODBC, it's best to stick with standard SQL
data type much as possible. (This is true for any RDBMS, in fact).

Also, if queries, especially one written for recordsources or rowsources,
are not written carefully, you can find youself seeing #Deleted as soon as
you change a column. This is because Access may lose reference to the same
row if it does not have a primary key. Thus all queries should include keys,
wehther you use it or not. (Some posts on internet suggests including
timestamps, but I have not had used it and doubt it would help).

IMO, a required reading for you would be Microsoft's whitepaper on Jet/ODBC
Connectivity. While it was for Jet 3.0 is still relevant to 4.0 and will go
long way toward helping you know how to write effective queries that is
processed on the expected side.
http://support.microsoft.com/kb/128385

Also, you can go to MySQL's site; they have an article about how to link up
with Access and describe in details the various pitfalls and what to expects
over there.
http://dev.mysql.com/doc/refman/5.1/...th-access.html

Last note: I also have found that queries in general perform faster when it
has Connection String explicitly specified. I wrote about this at:
http://access-programmers.co.uk/foru...d.php?t=151976

This hasn't been confirmed by others, but this may be worth a try.

I hope that helps some. You also can go to MySQL's site; they have a forum
as well and discuss about various issues with using Access as front-end.

Best of luck!



"mcnews" wrote:

any different than using MS SQL in terms of linking tables etc...
any gotchas?

On Dec 22, 2:45*pm, Banana wrote:
I have developed and implemented a project using MySQL as back-end to Access
using bound forms. Quite easy to use.

You, however, do have to download and distribute the ODBC driver to every
client who needs access to it. You can get it for free from MySQL; it's
called MyODBC and version should be 5.1, I believe.

One gotcha I remember is the date/time difference. Access may not be always
able to handle MySQL's date, time and timestamp consistently. For maximum
portability, stick to MySQL's datetime. You still can use timestamp if you
need it for logging updates as long Access does not have to interact with it,
however. I've found Access and MySQL handle datetime quite well, despite the
difference in format (MySQL prefers ISO format yyyy-mm-dd hh:mm:ss while
Access prefers US format mm/dd/yyyy hh:mm:ss)

Like MS SQL, you have to avoid using bigint as Access cannot support this
and will display #Deleted if the column is returned.

I also wouldn't bother with MySQL's extension data type such as set and
enum. They may be cool, but for ODBC, it's best to stick with standard SQL
data type much as possible. (This is true for any RDBMS, in fact).

Also, if queries, especially one written for recordsources or rowsources,
are not written carefully, you can find youself seeing #Deleted as soon as
you change a column. This is because Access may lose reference to the same
row if it does not have a primary key. Thus all queries should include keys,
wehther you use it or not. (Some posts on internet suggests including
timestamps, but I have not had used it and doubt it would help).

IMO, a required reading for you would be Microsoft's whitepaper on Jet/ODBC
Connectivity. While it was for Jet 3.0 is still relevant to 4.0 and will go
long way toward helping you know how to write effective queries that is
processed on the expected side.http://support.microsoft.com/kb/128385

Also, you can go to MySQL's site; they have an article about how to link up
with Access and describe in details the various pitfalls and what to expects
over there.http://dev.mysql.com/doc/refman/5.1/...xamples-tools-...

Last note: I also have found that queries in general perform faster when it
has Connection String explicitly specified. I wrote about this at:http://access-programmers.co.uk/foru...d.php?t=151976

This hasn't been confirmed by others, but this may be worth a try.

I hope that helps some. You also can go to MySQL's site; they have a forum
as well and discuss about various issues with using Access as front-end.

Best of luck!


great stuff.
thanks!

"mcnews" wrote:
any different than using MS SQL in terms of linking tables etc...
any gotchas?

On Dec 22, 2:38*pm, Daniel Pineault
wrote:
I use it for one of my dbs as the back-end. *Download the ODBC drivers and
link as you normally would. *i have not had any issue thus far (3 yrs and
counting).

Why make the change though? *Do you have a need not met by Access?

erm ... customer is always right?

--
Hope this helps,

Daniel Pineaulthttp://www.cardaconsultants.com/
For Access Tips and Examples:http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

"mcnews" wrote:
any different than using MS SQL in terms of linking tables etc...
any gotchas?