Solved

Cannot edit tables in MS SQL Server 2005 database

Posted on 2009-04-04
7
480 Views
Last Modified: 2012-05-06
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_BusinessSupportersCategories
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_Cat, column BusinessID. The statement has been terminated. (#3621)"

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 ASC

Open in new window

three-tables.gif
0
Comment
Question by:Eric Bourland
  • 3
  • 2
  • 2
7 Comments
 
LVL 77

Assisted Solution

by:arnold
arnold earned 100 total points
ID: 24069959
I suspect the fact that the SQL table relies on the linked Access DB, neither can be altered. There is no mechanism that will be able to interpret your addition of a column or deletion of one in terms of the link on the SQL server.
One option is unlink the database on SQL 2005.  Make the changes and relink.
The other option import the Access DB into the SQL as an object rather than as a linked object.  Then when you need to alter the database, you can do it through SSMS on the SQL server.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 24069978
arnold, thank you for the response. I was thinking along those same lines -- I cannot alter the database b/c the MS SQL table is linked to my MS Access application.

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
0
 
LVL 77

Expert Comment

by:arnold
ID: 24070022
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.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 11

Accepted Solution

by:
mildurait earned 400 total points
ID: 24070440
The updates are failing because tbl_BusinessSupportersCategories and tbl_Biz_Support_Has_Cat do not have Identity columns.
Try the columns in SQL Server (Data Type Int, Identity Yes) and then relink the tables to access.


0
 
LVL 11

Expert Comment

by:mildurait
ID: 24070444
..Try adding the columns in SQL Server...
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 24070932
mildurait, thank for your note. I made this observation too: tbl_BusinessSupportersCategories and tbl_Biz_Support_Has_Cat do not have Identity columns. But I tried to add identity columns in SSMS ... I was not allowed to do so.

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
0
 
LVL 3

Author Closing Comment

by:Eric Bourland
ID: 31566688
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
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Apache error.log 19 43
Allow user to edit an outgoing email 2 25
Reference Controls on subforms 7 28
Linking to an Excel file and determining the field types 3 15
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question