ms-access - 从 SSMS 导出到 MS Access 时保持表关系

Question

我目前正在为一个客户管理两个数据库。合同即将到期，他们要求我们在移交之前将他们的数据导出到 Access。

我注意到 Access 不保留表关系。SSMS 导出向导中似乎没有选项。将数据发送到 SSMS 时似乎也有解决方案，但反之则不然。这个问题还有其他解决方法吗？

标签： ms-accessexportssms

Answer 1

There is not a means to do the reverse.

However, what you can do is create a blank Access database. Link one table to sql server.

Then execute a transferDatabase command (it operates on one table).

the result of the above is that you now have all tables in Access. You WOULD have to re-create the relationships, but all PK and FK keys would be un-changed, and thus you can setup and re-create the relationships in Access. If you have say 25 tables? Then create a new table in that database (say my tables) and from SSMS execute a query to send all current tables in the database to that "table". (remove the MyTable - and perhaps any others you don't want to transfer).

Now, from the Access side, link to that MyTables, and process the rows for each table - executing a transfer Database command for each given table in that MyTale list. So at that point you have all data and tables in Access. As noted, you will have to re-create the relationships.

Another way? Just create a blank Access database. use the linked table wizard or the import wizard - you can import all tables in one shot. - you thus not need the above code. This is certainly the least amount of work. You can use the external data tab, and choose to create "links" or in fact local tables. So you can well with ease import all tables + data. But setting up and creating of the relationships will have to be done by manually (by hand).