If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
anybody use MySQL?
any different than using MS SQL in terms of linking tables etc...
any gotchas? |
#2
|
|||
|
|||
anybody use MySQL?
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? |
#3
|
|||
|
|||
anybody use MySQL?
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? |
#4
|
|||
|
|||
anybody use MySQL?
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? |
#5
|
|||
|
|||
anybody use MySQL?
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? |
Thread Tools | |
Display Modes | |
|
|