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
4090
November 16, 2004 02:37PM
2266
November 29, 2004 12:57PM
2210
November 30, 2004 11:15AM
2264
December 31, 2004 09:51AM
2288
February 04, 2005 12:10AM
2237
December 01, 2004 10:30AM
2242
December 07, 2004 09:50PM
2164
December 10, 2004 05:11PM
2150
December 11, 2004 12:49AM
2404
December 13, 2004 02:17PM
2161
December 15, 2004 04:01PM
2295
December 15, 2004 05:17PM
2268
December 15, 2004 08:19PM
2356
December 16, 2004 04:25AM
2179
December 17, 2004 07:43PM
2200
December 28, 2004 11:26AM
2118
January 02, 2005 05:41PM
2108
January 03, 2005 02:51AM
2207
January 27, 2005 03:47AM
2226
February 01, 2005 12:59AM
2604
February 01, 2005 11:35AM
1885
February 03, 2005 12:43AM
2187
February 03, 2005 01:29PM
2322
February 06, 2005 01:21PM
2179
February 14, 2005 10:13AM
2438
A M
March 08, 2005 11:21AM
2264
A M
March 10, 2005 11:42AM
Re: Migration Utility - Free and easy alternative - HOW TO -
2678
March 11, 2005 11:43AM
2239
February 18, 2005 08:42AM
2159
February 24, 2005 06:53AM
2295
March 01, 2005 02:15PM
2167
March 02, 2005 11:37AM
2023
February 26, 2005 01:51PM
2214
March 02, 2005 08:34AM
2348
March 17, 2005 09:32AM
2132
March 20, 2005 10:59PM
2226
March 29, 2005 12:31PM
2471
April 01, 2005 10:58AM
2195
April 01, 2005 01:09PM
2202
June 07, 2005 10:37AM
2277
June 07, 2005 03:51PM
2160
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.