Cannot edit tables in MS SQL Server 2005 database
Problem: I have created three database tables that will not let me edit them.
Details:
Here's what I did:
1. In MS Access 2003, I created a database with three tables:
tbl_BusinessSupporters
tbl_BusinessSupportersCate
tbl_Biz_Support_Has_Cat
The three tables are related, as shown in the diagram below.
2. Next, I used the "Microsoft SQL Server Migration Assistant for Access" to export the database from MS Access 2003 (on my local computer) to MS SQL Server 2005 (on my database server). That went well.
3. The "Microsoft SQL Server Migration Assistant for Access" application prompted me to "Link" the MS Access tables to the tables in MS SQL Server 2005. This means that when I update the tables in MS Access on my local computer, then the tables in MS SQL Server get updated too -- because the tables displayed in the local MS Access application are, in fact, the actual tables on the MS SQL Server. (This is my understanding, anyway. Correct me if I am mistaken?)
The problem is, I cannot edit any of the three tables in either the local MS Access application, or in the MS SQL Server.
* I cannot add any more records to table "tbl_Biz_Support_Has_Cat" -- in MS Access the AutoNumber is deactivated.
* Or, in MS Access, if I go into tbl_BusinessSupporters and attempt to delete a record, I get this error: "ODBC Call Failed. The DELETE statement conflicted with the Reference Constraint. The conflict occurred in database ascassociation, table dbo.tbl_Biz_Support_Has_Ca
I would like to be able to edit these database tables in MS Access on my local computer, save the edits, and (since the tables are linked to the MS SQL Server) have the edits appear in the MS SQL Server (hence on the live web site).
What am I doing wrong?
Thank you for any advice!
Eric
Details:
Here's what I did:
1. In MS Access 2003, I created a database with three tables:
tbl_BusinessSupporters
tbl_BusinessSupportersCate
tbl_Biz_Support_Has_Cat
The three tables are related, as shown in the diagram below.
2. Next, I used the "Microsoft SQL Server Migration Assistant for Access" to export the database from MS Access 2003 (on my local computer) to MS SQL Server 2005 (on my database server). That went well.
3. The "Microsoft SQL Server Migration Assistant for Access" application prompted me to "Link" the MS Access tables to the tables in MS SQL Server 2005. This means that when I update the tables in MS Access on my local computer, then the tables in MS SQL Server get updated too -- because the tables displayed in the local MS Access application are, in fact, the actual tables on the MS SQL Server. (This is my understanding, anyway. Correct me if I am mistaken?)
The problem is, I cannot edit any of the three tables in either the local MS Access application, or in the MS SQL Server.
* I cannot add any more records to table "tbl_Biz_Support_Has_Cat" -- in MS Access the AutoNumber is deactivated.
* Or, in MS Access, if I go into tbl_BusinessSupporters and attempt to delete a record, I get this error: "ODBC Call Failed. The DELETE statement conflicted with the Reference Constraint. The conflict occurred in database ascassociation, table dbo.tbl_Biz_Support_Has_Ca
I would like to be able to edit these database tables in MS Access on my local computer, save the edits, and (since the tables are linked to the MS SQL Server) have the edits appear in the MS SQL Server (hence on the live web site).
What am I doing wrong?
Thank you for any advice!
Eric
The SELECT statement that I use in my ColdFusion application:
SELECT *
FROM tbl_BusinessSupporters bs, tbl_BusinessSupportersCategories bsc, tbl_Biz_Support_Has_Cat bshc
WHERE bs.BusinessID = bshc.BusinessID
AND bsc.BusinessCategoryID = bshc.BusinessCategoryID
AND bsc.BusinessCategoryID = 2
ORDER BY BusinessTitle ASCSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SQL is a client/server platform. Access DB is not by default. The access developer needs to build the client/server functionality into the access front end.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
..Try adding the columns in SQL Server...
ASKER
mildurait, thank for your note. I made this observation too: tbl_BusinessSupportersCate
I think I need to unlink the tables, then try to add identity columns. I will try this and get back to you tomorrow. Thanks for your help.
Eric
I think I need to unlink the tables, then try to add identity columns. I will try this and get back to you tomorrow. Thanks for your help.
Eric
ASKER
Thanks for your help. Here is how I fixed it: In MS Access I unlinked the tables. I also removed all of the relationships between the tables (again, in MS Access). Then I added the relationships again -- but did not enforce referential integrity. Also, I deleted the tables from the MS SQL Server, and ran the "Microsoft SQL Server Migration Assistant for Access" application again. At first I did not link the tables, to see if I could edit the tables in MS SQL Server. I could. Then I linked the tables -- and now I can still edit them. Yahoo.
Thanks again, friends. Enjoy the rest of the weeked.
Eric
Thanks again, friends. Enjoy the rest of the weeked.
Eric
ASKER
But, if that is the case, then what is the purpose of linking? It seems like the only purpose of linking tables is to make them uneditable -- which I will guess is not intended to be the case.
I'm continuing to investigate. I'll get back to you soon.
Eric