如何使用adox将外键添加到访问表 [英] how to add foreign key to access table using adox
问题描述
我正在尝试创建一个包含两个表的数据库.我想在其中之一中添加外键. 但是以下代码无法正常工作,我对其进行了调试,发现唯一的问题是添加了外键.
I am trying to creat a database with two tables. i want to add in a foreign key in one of them. but the following code is not working, i debug it and i found that the only problem is in adding the foreign key.
private static bool creatDatabase()
{
bool result = false;
Catalog cat = new Catalog();
Table tableCustomer = new Table();
Table tableAddresses = new Table();
//Create the table Customer and it's fields.
tableCustomer.Name = "Customer";
tableCustomer.Columns.Append("Customer_ID", ADOX.DataTypeEnum.adInteger);
tableCustomer.Keys.Append("PrimaryKEy", KeyTypeEnum.adKeyPrimary, "Customer_ID");
tableCustomer.Columns.Append("Name", ADOX.DataTypeEnum.adVarWChar, 50);
tableCustomer.Columns.Append("Email", ADOX.DataTypeEnum.adVarWChar, 50);
tableCustomer.Columns.Append("TelNumber", ADOX.DataTypeEnum.adVarWChar, 32);
tableCustomer.Columns.Append("Fax", ADOX.DataTypeEnum.adVarWChar, 32);
tableCustomer.Columns.Append("Street", ADOX.DataTypeEnum.adVarWChar, 50);
tableCustomer.Columns.Append("PostalCode", ADOX.DataTypeEnum.adInteger, 10);
tableCustomer.Columns.Append("City", ADOX.DataTypeEnum.adVarWChar, 50);
tableCustomer.Columns.Append("AdressCounter", ADOX.DataTypeEnum.adSmallInt);
tableAddresses.Name = "Addresses";
tableAddresses.Columns.Append("Address_ID", ADOX.DataTypeEnum.adInteger);
tableAddresses.Keys.Append("PrimaryKEy", KeyTypeEnum.adKeyPrimary, "Address_ID");
//tableAddresses.Columns.Append("Customer_ID", ADOX.DataTypeEnum.adInteger);
//tableAddresses.Keys.Append("ForeignKey", KeyTypeEnum.adKeyForeign, "Customer_ID"); ---> here is the Exception
tableAddresses.Columns.Append("Street", ADOX.DataTypeEnum.adVarWChar, 50);
tableAddresses.Columns.Append("PostalCode", ADOX.DataTypeEnum.adInteger, 10);
tableAddresses.Columns.Append("City", ADOX.DataTypeEnum.adVarWChar, 50);
try
{
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Application.StartupPath + "\\Customers.mdb" + "; Jet OLEDB:Engine Type=5");
cat.Tables.Append(tableCustomer);
cat.Tables.Append(tableAddresses);
//Now Close the database
ADODB.Connection con = cat.ActiveConnection as ADODB.Connection;
if (con != null)
con.Close();
result = true;
}
catch (Exception ex)
{
result = false;
}
finally
{
if (!result)
{
ADODB.Connection con = cat.ActiveConnection as ADODB.Connection;
if (con != null)
con.Close();
File.Delete(Application.StartupPath + "\\Customers.accdb");
}
}
cat = null;
return result;
}
如果我尝试以下方法(在上层方法中创建数据库后打开数据库),则该方法也行不通:
if i try antoher approach like the following one(open the database after creating it in the upper methode), it does not work too:
private static bool addForeignKey()
{
bool retValue = true;
ADODB.Connection con = new Connection();
Key kyForeign = new Key();
Catalog cat = new Catalog();
kyForeign.Name = "test";
kyForeign.Type = KeyTypeEnum.adKeyForeign;
kyForeign.RelatedTable = "Customer";
kyForeign.Columns.Append("CustomerID", ADOX.DataTypeEnum.adInteger);
kyForeign.Columns["CustomerID"].RelatedColumn = "Customer_ID";
try
{
con.Open("Provider='Microsoft.JET.OLEDB.4.0';Data source ='"
+ Application.StartupPath + "\\Customers.mdb';");
cat.ActiveConnection = con;
cat.Tables["Addresses"].Keys.Append(kyForeign, KeyTypeEnum.adKeyForeign, ADOX.DataTypeEnum.adInteger); // here comes the Exception
}
catch
{
retValue = false;
}
finally
{
if(retValue)
{
if (con != null)
con.Close();
}
}
return retValue;
}
我没有通过代码示例找到有关adox api的良好文档,这就是为什么我不知道如何解决此问题的原因? 提前thnx
i dont find a good documentation for the adox api with code examples, thats why i dont know how to solve this? thnx in advance
推荐答案
问题是您没有填写RelatedTable
和RelatedColumn
参数.用以下代码替换第一段代码中的注释行:
The problem is that you don't fill the RelatedTable
and RelatedColumn
parameters. Replace the commented lines in your first piece of code with the following:
tableAddresses.Columns.Append("Customer_ID", ADOX.DataTypeEnum.adInteger);
tableAddresses.Keys.Append("ForeignKey", ADOX.KeyTypeEnum.adKeyForeign, "Customer_ID", "Customer", "Customer_ID");
这篇关于如何使用adox将外键添加到访问表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!