CODEWITHSUNDEEP
mysqlms-access
posfan12
posfan12

Reputation: 2651

Relationships lost when importing MySQL data into MS Access

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.

Upvotes: 0

Views: 691

Answers (1)

Sergey S.
Sergey S.

Reputation: 6336

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

Upvotes: 2

Related Questions