如何使用SQL使用关系重命名MS Access表? [英] How to rename MS Access table with relationships using SQL?

问题描述

我正在从外部.Net应用程序连接到MS Access数据库.我需要使用SQL来升级数据库的架构.升级的一部分要求重命名属于一对多关系的表.

I'm connecting to an MS Access database from an external .Net application. I need to use SQL in order to upgrade the schema of the database. Part of the upgrade requires renaming a table that is part of a 1-to-many relationship.

我了解实际上无法使用SQL在MS Access中重命名表.我的研究使我找到了以下解决方案.

I understand that it is not possible to actually rename a table in MS Access using SQL. My research has led me to the following solution.

SELECT * INTO OldTableName FROM NewTableName
DROP TABLE OldTableName

对于不属于关系的表，这似乎可以正常工作.但是，如果表属于某个关系，则在运行DROP TABLE SQL时会收到以下异常.

This appears to work correctly for tables that are not part of a relationship. However, if the table belongs to a relationship, then I receive the following exception when running the DROP TABLE SQL.

Cannot delete this index or table.  It is either the current index or is used in a relationship.

通过SQL是否可以更新MS Access中的关系以指向已创建的新表，以便可以删除旧表?

Is there a way via SQL that I can update the relationship in MS Access to point to the new table that has been created so the old table can be dropped?

推荐答案

您可以通过使用Access DAO重命名表来为自己省些麻烦:

You can probably save yourself some grief by simply using Access DAO to rename the table:

using Microsoft.Office.Interop.Access.Dao;

namespace AccessDaoConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            var dbe = new DBEngine();
            Database db = dbe.OpenDatabase(@"C:\Users\Public\Database1.accdb");
            TableDef tbd = db.TableDefs["OldTableName"];
            tbd.Name = "NewTableName";
            db.Close();
        }
    }
}

.NET项目将需要Access DAO的COM参考.我用的是

The .NET project will require a COM reference for Access DAO. The one I used was

Microsoft Office 14.0 Access数据库引擎对象库

Microsoft Office 14.0 Access Database Engine Object Library

这篇关于如何使用SQL使用关系重命名MS Access表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！