0

I am trying to import a database from MySQL to MS Access. I have been following these instructions:

Converting MySQL to MS Access

The data imports okay, but the relationships and keys are lost. Any tips on how I can import these as well? Thanks.

Community
  • 1
  • 1
posfan12
  • 2,541
  • 8
  • 35
  • 57
  • 3
    Primary keys are retained when *linking* tables, but not when importing. Relationships cannot be imported at all from outside Access. You'll have to recreate both in the Access tables - either with DDL SQL or with VBA (DAO). – Andre Nov 15 '16 at 11:29

1 Answers1

2

In order to convert table structure from MySQL or any other database to MS Acces first of all dump database structure as SQL. You can use, for instance, answers from this topic, then edit received DDL SQL, make it compatible with MS Access. Basic DDL of MySQL and Access qre quite similar. In fact primary key and relationships are constrains, plus indexes. For instance, here is DDL for creating table with primary key, one additional index and two relationships with tables Countries and Customers:

create table CustomersAddresses
(
    ID_CustomerAddress   COUNTER         not null,
    ID_Customer          INTEGER,
    AddressLIne1         Text(255),
    AddressLIne2         Text(255),
    ID_Country           INTEGER,
constraint PK_CUSTOMERSADDRESSES primary key (ID_CustomerAddress)
);

create  index ID_Customer on CustomersAddresses
(
   ID_Customer          ASC
);

alter table CustomersAddresses
   add constraint FK_CustomersAddresses_REF_Countries foreign key (ID_Country)
      references Countries (ID_Country);

alter table CustomersAddresses
   add constraint FK_CustomersAddresses_REF_Customers foreign key (ID_Customer)
      references Customers (Id_Customer)  on delete cascade on update cascade;

You should receive similar SQL from MySQL table structure dump tool and after editing run DDL commands in Access manually or using VBA. Please note, that DAO and built-in Access query builder doesn't support on delete cascade on update cascade options in DDL SQL, use ADO for this, it supports.

The easiest way for database structure conversion is using CASE tools like ERWin or SAP PowerDesigner: they can reverce database structure of almost any database, convert it to any destination database and even generate new databese in few button clicks

Community
  • 1
  • 1
Sergey S.
  • 6,296
  • 1
  • 14
  • 29
  • I tied running the MySQL dump in Access, but it said the file was too big to fit into a query window. The dump is ~20k lines long. There are other issues like CREATE TABLE IF NOT EXISTS not working. – posfan12 Nov 15 '16 at 13:28
  • 1
    Of course you cannot get the same features in Access because MySQL and Access are different type of databases. Frankly I don't understand why you are trying to move such big database to Access, it's less reliable and it has much more limitations by comparing MySQL, for instance Access has 2GB limitation per data file and doesn't work properly already after 1.5GB. You'd better to link tables in Access frontend to MySQL backend. As frontend Access is perfect. – Sergey S. Nov 16 '16 at 02:15
  • I tried linking and it showed the primary keys but not the foreign key relationships. I am trying to export a diagram of these relationships, and Access usually has a nice one. – posfan12 Nov 16 '16 at 05:46