MySQL Forums
Forum List  »  Microsoft Access

Re: Migration Utility - Free and easy alternative - HOW TO -
Posted by: Claudio Domiziani
Date: March 11, 2005 11:43AM

If you have table relationship involving referential integrity, exporting access tables directly via ODBC, they go away and if you have junk characters in table or field names the behaviour when relinking tables from acces is unknown!

Recently I've migrated a BIG access database (over 70 tables) to Mysql, leaving access frontend for form record editing, queries and reports and I've experienced some problems.
These are the ways I solved them :

If you have/plan-to-use referential integrity, use innodb tables that support foreign keys. This is possible from some latest versions of Mysql 3.23.x, but they have to be explicit configured. Using Mysql 4 there are no problems since InnoDB engine is active by default.

Using v. 4.0.x can lead to some problems with MyODBC and MS access.
So use, if possible, Mysql >= 4.1.10 and MyODBC >=3.51.11-2.

Use dbtools pro freeware edition to migrate the entire database in one shot.
It ca migrate also relationships and indexes and automatically normalize field/table names to a standard form (removes spaces, junk chars, etc.). This is the best tool i've tried to migrate access db.

Take a look at fields type for those that were currency in access. These are sometime converted as double, but this can cause an error when updating fields from an access form (record has been locked by another user....). Change them to decimal(20,4) or similar precision.

Be sure that a primary key is present in each table before linking tables from access. If not, add an integer autoincrement field (best in first position) and make it primary key.

Add a Timestamp field defaulting to now() to each table. This can solve the #deleted# field problem when inserting new records from a form.

Be sure that the fields used in queries are indexed. e.g. If a query uses an ORDER BY F1, F2, F3, make a single index for all three fields. If there's a filter, index the filtering field/s This can seem obvious to most people, but I read much about slow access queries in newsgroups!

For details about SQL commands in Mysql, there's the great mysql documentation (eg. RTFM).

How to find :
DBTOOLS -> http://www.dbtools.com.br/
MYODBC 3.51.11-2 -> http://www.peterharvey.org/Downloads/MySQL/MyODBC/

Regards,
Claudio

Options: ReplyQuote


Subject
Views
Written By
Posted
4094
November 16, 2004 02:37PM
2269
November 29, 2004 12:57PM
2214
November 30, 2004 11:15AM
2265
December 31, 2004 09:51AM
2290
February 04, 2005 12:10AM
2240
December 01, 2004 10:30AM
2244
December 07, 2004 09:50PM
2179
December 10, 2004 05:11PM
2154
December 11, 2004 12:49AM
2405
December 13, 2004 02:17PM
2162
December 15, 2004 04:01PM
2296
December 15, 2004 05:17PM
2271
December 15, 2004 08:19PM
2357
December 16, 2004 04:25AM
2180
December 17, 2004 07:43PM
2202
December 28, 2004 11:26AM
2119
January 02, 2005 05:41PM
2109
January 03, 2005 02:51AM
2209
January 27, 2005 03:47AM
2227
February 01, 2005 12:59AM
2607
February 01, 2005 11:35AM
1886
February 03, 2005 12:43AM
2188
February 03, 2005 01:29PM
2324
February 06, 2005 01:21PM
2182
February 14, 2005 10:13AM
2440
A M
March 08, 2005 11:21AM
2268
A M
March 10, 2005 11:42AM
Re: Migration Utility - Free and easy alternative - HOW TO -
2683
March 11, 2005 11:43AM
2240
February 18, 2005 08:42AM
2161
February 24, 2005 06:53AM
2305
March 01, 2005 02:15PM
2168
March 02, 2005 11:37AM
2025
February 26, 2005 01:51PM
2215
March 02, 2005 08:34AM
2350
March 17, 2005 09:32AM
2134
March 20, 2005 10:59PM
2245
March 29, 2005 12:31PM
2473
April 01, 2005 10:58AM
2197
April 01, 2005 01:09PM
2205
June 07, 2005 10:37AM
2279
June 07, 2005 03:51PM
2162
July 18, 2005 09:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.