Small question on MS SQL, MySql and MS ACCESS

[H@z]

Don't want to re-invent the wheel, so I thought I'd ask if someone has experience with this...

Scenario: Me + some friends are developing a small java application that works against a relational db. During development, we have used MS SQL Server 2000 (it came in handy, since we could use MS Access for db design and then import it to MS SQL + we could edit the tables from any pc we liked by using Access + odbc). The application is using the latest jdbc drivers from MS, and everything is working just fine... (Well, some more java coding needs to be done, and some of the sql statements are not 100 % yet, but apart from that... :-)

The system will hopefully be put into beta testing in a couple of months, and now we're thinking about migrating the db to a linux server running MySql instead (it can't stay on my test server forever, mainly because the connection is way to slow - could be their intranet or their bridge to internet combined with my server's internet connection - and the ppl buying the system wants to get it as cheap as possible). I'm not expecting any big troubles with the migration (no stored procedures, since we wanted it to be easy to migrate), but has anyone here experience with db migration Ms SQL --> MySql? I'm a bit worried that some of the sql statements will work differently (yes, I've used google, and I've found some pages about how to migrate etc, but I'm just asking if anyone has any personal experience and can advise what problems to expect).

Also, the organization that has ordered the system has a small Access db with some tables our system would want to use. Instead of keeping data in two different db:s, is there a way to use the Access db from the application (thru jdbc or odbc)? I've only used Access as a "local" dbm and as a "link" using odbc, but I've never had the need to use an Access db from a remote application, and I havn't been able to find anything good on the net...

Grateful for any help...

[samsolenn]

Hi there,

The main problems are with mySQLs management of primary and foreign keys, mySQL doesn't use this notion at all. This is the main inconvenience of mySQL.

There are numerous ways to work around this problem, you'll probably find them on google.

I've migrated several databases from ACCESS / SQL Server to mySQL / postgreSQL and have had only minor problems to deal with.

Good luck...