If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case.
Described below is what I would suspect to be a Microsoft Table Wizard
issue. It occurs with any table creation and setting foreign key relationships. Has anyone noticed or seen this issue addressed by Microsoft? Please excuse the lengthiness, but I wanted the issue to be clear to all. HDW ACC2003: Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. SYMPTOMS When you use the new table wizard to create a table and the foreign key column name does not match the primary key in a foreign table exactly in case, the table relationships are not set even though the table wizard declares that the relationship was set. MORE INFORMATION Steps to Reproduce Problem ----------------------------------- Create Categories Table in Design View 1. Create a new table in Design View. 2. Add the following fields to the table: Table: ----------- Field Name: Categoryid (be sure to type the characters "id" in lowercase). Data Type: AutoNumber FieldName: CategoryName Data Type: Text 3. Close and save the table as Categories. CREATE PRODUCTS TABLE WITH TABLE WIZARD 1. Click on Tables in the Objects window. 2. Click on New in the database toolbar. 3. Select Table Wizard in the New Table form and click the OK button. 4. Click on Products in the Sample Tables list. 5. Press the button to add all sample fields to the field in my new table list. 6. Press the Next button twice. 7. Select the text "not related to Categories" in the "My new Products table is" list box. 8. Press the Relationships. button. 9. Select the option button with the text "One record in the 'Categories' table will match many records in the Products table. (Note the statement on this form: "The table wizard will add a 'Categorid' field to the Products table, and then create this relationship." 10. Click the OK button. 11. Note that the list box entitled "My new Products table is." now states 'related to Categories. 12. Press the Next button and then the Finish button. VIEW TABLE RELATIONSHIPS 13. Open the Relationships form by selecting Tools|Relationships from the menu bar. 14. Notice that no relationship connecting line is drawn between the two tables for columns Categories!Categoryid and Products!CategoryID. STEPS TO SUCCESSFULLY CREATE RELATIONSHIPS 15. Delete the existing Products tables created in the instruction steps above. 16. Repeat steps 1 through 5 in Create Products Table with Table Wizard above. 17. Before pressing the Next button and the remainder of steps 6 through 12, select the CategoryID field name in the "Field in My new Table" list. 18. Press the Rename Field. button. 19. Type Categoryid in the Rename Field form and be sure to type "id" in lower case letters. 20. Then press the OK button. 21. Continue with steps 6 through 12 above. 22. Repeat steps 13 and 14 for View Table Relationships and notice the connecting relationship line between these two fields. |
#2
|
|||
|
|||
Presumably you are talking about the Lookup Wizard in table design?
If so, you may have just provided one more reason for not using this horrendous thing. For a list of some others, see: The Evils of Lookup Fields in Tables at: http://www.mvps.org/access/lookupfields.htm -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "HDW" wrote in message ... Described below is what I would suspect to be a Microsoft Table Wizard issue. It occurs with any table creation and setting foreign key relationships. Has anyone noticed or seen this issue addressed by Microsoft? Please excuse the lengthiness, but I wanted the issue to be clear to all. HDW ACC2003: Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. SYMPTOMS When you use the new table wizard to create a table and the foreign key column name does not match the primary key in a foreign table exactly in case, the table relationships are not set even though the table wizard declares that the relationship was set. MORE INFORMATION Steps to Reproduce Problem ----------------------------------- Create Categories Table in Design View 1. Create a new table in Design View. 2. Add the following fields to the table: Table: ----------- Field Name: Categoryid (be sure to type the characters "id" in lowercase). Data Type: AutoNumber FieldName: CategoryName Data Type: Text 3. Close and save the table as Categories. CREATE PRODUCTS TABLE WITH TABLE WIZARD 1. Click on Tables in the Objects window. 2. Click on New in the database toolbar. 3. Select Table Wizard in the New Table form and click the OK button. 4. Click on Products in the Sample Tables list. 5. Press the button to add all sample fields to the field in my new table list. 6. Press the Next button twice. 7. Select the text "not related to Categories" in the "My new Products table is" list box. 8. Press the Relationships. button. 9. Select the option button with the text "One record in the 'Categories' table will match many records in the Products table. (Note the statement on this form: "The table wizard will add a 'Categorid' field to the Products table, and then create this relationship." 10. Click the OK button. 11. Note that the list box entitled "My new Products table is." now states 'related to Categories. 12. Press the Next button and then the Finish button. VIEW TABLE RELATIONSHIPS 13. Open the Relationships form by selecting Tools|Relationships from the menu bar. 14. Notice that no relationship connecting line is drawn between the two tables for columns Categories!Categoryid and Products!CategoryID. STEPS TO SUCCESSFULLY CREATE RELATIONSHIPS 15. Delete the existing Products tables created in the instruction steps above. 16. Repeat steps 1 through 5 in Create Products Table with Table Wizard above. 17. Before pressing the Next button and the remainder of steps 6 through 12, select the CategoryID field name in the "Field in My new Table" list. 18. Press the Rename Field. button. 19. Type Categoryid in the Rename Field form and be sure to type "id" in lower case letters. 20. Then press the OK button. 21. Continue with steps 6 through 12 above. 22. Repeat steps 13 and 14 for View Table Relationships and notice the connecting relationship line between these two fields. |
#3
|
|||
|
|||
Hi Allen,
Nope, my question in on the New Table Wizard. I never use these things myself, but where I work they use them in certification exam questions that they develop. So an Access user would get to this Table Wizard by clicking on the Tables tab on the database container, then press New, and then select Table Wizard. It is during this process that the wizard will set a relationship between two tables, if the field names between the two match exactly in case. If a user renames a field name to match another existing table, but does NOT match that name exactly in case then a relationship will not be established even though the wizard might indicate so. In my own consulting work, I always set referential integrity manually between columns and rows in the Tools|Relationships form. However, while developing code to detect what the wizard had set by looking at the DB properties, I believe, I have discovered this "bug" in this wizard. I was hoping someone out there (maybe even a Microsoft Program Manager or a MVP like yourself)could confirm this. Thanks for your reply though. How are things down under? HDW "Allen Browne" wrote in message ... Presumably you are talking about the Lookup Wizard in table design? If so, you may have just provided one more reason for not using this horrendous thing. For a list of some others, see: The Evils of Lookup Fields in Tables at: http://www.mvps.org/access/lookupfields.htm -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "HDW" wrote in message ... Described below is what I would suspect to be a Microsoft Table Wizard issue. It occurs with any table creation and setting foreign key relationships. Has anyone noticed or seen this issue addressed by Microsoft? Please excuse the lengthiness, but I wanted the issue to be clear to all. HDW ACC2003: Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. SYMPTOMS When you use the new table wizard to create a table and the foreign key column name does not match the primary key in a foreign table exactly in case, the table relationships are not set even though the table wizard declares that the relationship was set. MORE INFORMATION Steps to Reproduce Problem ----------------------------------- Create Categories Table in Design View 1. Create a new table in Design View. 2. Add the following fields to the table: Table: ----------- Field Name: Categoryid (be sure to type the characters "id" in lowercase). Data Type: AutoNumber FieldName: CategoryName Data Type: Text 3. Close and save the table as Categories. CREATE PRODUCTS TABLE WITH TABLE WIZARD 1. Click on Tables in the Objects window. 2. Click on New in the database toolbar. 3. Select Table Wizard in the New Table form and click the OK button. 4. Click on Products in the Sample Tables list. 5. Press the button to add all sample fields to the field in my new table list. 6. Press the Next button twice. 7. Select the text "not related to Categories" in the "My new Products table is" list box. 8. Press the Relationships. button. 9. Select the option button with the text "One record in the 'Categories' table will match many records in the Products table. (Note the statement on this form: "The table wizard will add a 'Categorid' field to the Products table, and then create this relationship." 10. Click the OK button. 11. Note that the list box entitled "My new Products table is." now states 'related to Categories. 12. Press the Next button and then the Finish button. VIEW TABLE RELATIONSHIPS 13. Open the Relationships form by selecting Tools|Relationships from the menu bar. 14. Notice that no relationship connecting line is drawn between the two tables for columns Categories!Categoryid and Products!CategoryID. STEPS TO SUCCESSFULLY CREATE RELATIONSHIPS 15. Delete the existing Products tables created in the instruction steps above. 16. Repeat steps 1 through 5 in Create Products Table with Table Wizard above. 17. Before pressing the Next button and the remainder of steps 6 through 12, select the CategoryID field name in the "Field in My new Table" list. 18. Press the Rename Field. button. 19. Type Categoryid in the Rename Field form and be sure to type "id" in lower case letters. 20. Then press the OK button. 21. Continue with steps 6 through 12 above. 22. Repeat steps 13 and 14 for View Table Relationships and notice the connecting relationship line between these two fields. |
#4
|
|||
|
|||
Good night! That wizard does create some kind of relation, but it does not
make any sense. The relation does not show in the Relationships window, but you can see it with the code below. It is an unenforced relation (utterly useless). It seems to have attributes 3, which is probably dbRelationUnique + dbDRelationDontEnforce. Why it would want to create an unenforced one-to-one relation is beyond me. Gotta rush: maybe I'm missing something here? Function ShowRel() Dim db As DAO.Database Dim rel As DAO.Relation Dim fld As DAO.Field Set db = CurrentDb() For Each rel In db.Relations Debug.Print rel.Name, rel.Table, rel.ForeignTable, rel.Attributes For Each fld In rel.Fields Debug.Print , fld.Name, fld.ForeignName Next Next Set rel = Nothing Set db = Nothing End Function -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "HDW" wrote in message ... Hi Allen, Nope, my question in on the New Table Wizard. I never use these things myself, but where I work they use them in certification exam questions that they develop. So an Access user would get to this Table Wizard by clicking on the Tables tab on the database container, then press New, and then select Table Wizard. It is during this process that the wizard will set a relationship between two tables, if the field names between the two match exactly in case. If a user renames a field name to match another existing table, but does NOT match that name exactly in case then a relationship will not be established even though the wizard might indicate so. In my own consulting work, I always set referential integrity manually between columns and rows in the Tools|Relationships form. However, while developing code to detect what the wizard had set by looking at the DB properties, I believe, I have discovered this "bug" in this wizard. I was hoping someone out there (maybe even a Microsoft Program Manager or a MVP like yourself)could confirm this. Thanks for your reply though. How are things down under? HDW "Allen Browne" wrote in message ... Presumably you are talking about the Lookup Wizard in table design? If so, you may have just provided one more reason for not using this horrendous thing. For a list of some others, see: The Evils of Lookup Fields in Tables at: http://www.mvps.org/access/lookupfields.htm -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "HDW" wrote in message ... Described below is what I would suspect to be a Microsoft Table Wizard issue. It occurs with any table creation and setting foreign key relationships. Has anyone noticed or seen this issue addressed by Microsoft? Please excuse the lengthiness, but I wanted the issue to be clear to all. HDW ACC2003: Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. SYMPTOMS When you use the new table wizard to create a table and the foreign key column name does not match the primary key in a foreign table exactly in case, the table relationships are not set even though the table wizard declares that the relationship was set. MORE INFORMATION Steps to Reproduce Problem ----------------------------------- Create Categories Table in Design View 1. Create a new table in Design View. 2. Add the following fields to the table: Table: ----------- Field Name: Categoryid (be sure to type the characters "id" in lowercase). Data Type: AutoNumber FieldName: CategoryName Data Type: Text 3. Close and save the table as Categories. CREATE PRODUCTS TABLE WITH TABLE WIZARD 1. Click on Tables in the Objects window. 2. Click on New in the database toolbar. 3. Select Table Wizard in the New Table form and click the OK button. 4. Click on Products in the Sample Tables list. 5. Press the button to add all sample fields to the field in my new table list. 6. Press the Next button twice. 7. Select the text "not related to Categories" in the "My new Products table is" list box. 8. Press the Relationships. button. 9. Select the option button with the text "One record in the 'Categories' table will match many records in the Products table. (Note the statement on this form: "The table wizard will add a 'Categorid' field to the Products table, and then create this relationship." 10. Click the OK button. 11. Note that the list box entitled "My new Products table is." now states 'related to Categories. 12. Press the Next button and then the Finish button. VIEW TABLE RELATIONSHIPS 13. Open the Relationships form by selecting Tools|Relationships from the menu bar. 14. Notice that no relationship connecting line is drawn between the two tables for columns Categories!Categoryid and Products!CategoryID. STEPS TO SUCCESSFULLY CREATE RELATIONSHIPS 15. Delete the existing Products tables created in the instruction steps above. 16. Repeat steps 1 through 5 in Create Products Table with Table Wizard above. 17. Before pressing the Next button and the remainder of steps 6 through 12, select the CategoryID field name in the "Field in My new Table" list. 18. Press the Rename Field. button. 19. Type Categoryid in the Rename Field form and be sure to type "id" in lower case letters. 20. Then press the OK button. 21. Continue with steps 6 through 12 above. 22. Repeat steps 13 and 14 for View Table Relationships and notice the connecting relationship line between these two fields. |
Thread Tools | |
Display Modes | |
|
|