|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Not Able to Edit SQL Data in AccessSP-3 for the front-end, and MDAC 2.8 RTM. I cannot edit any of the data in the SQL tables from Access. I can enter new data, but once it is in there it is untouchable from Access. I can edit it on the SQL Server side, but that's just the current work-around. The data came from several Access databases that I wanted to 1) merge together and 2) have the data reside in SQL. I created the SQL tables first, then linked them to Access, appended all data into the appropriate tables, then made certain columns the identity columns (on the SQL side), then I made that identity column the primary key, too. Every user is a member of the 'public' group, the 'public' group can select, insert, delete and execute, and every user is also a db_owner. Some of these settings happened at different intervals, but every time I made a change on the SQL side I deleted the linked table in Access, linked them all again, and in my understanding that is relinking the SQL tables. Before I made the primary key distinction in SQL I did do the Access side of it. I don't have any views, and I have the SQL table created in the System DSN. Here's the thing that really confuses me. I had all the Access data from the assorted tables merged into one Access database and then I appended that data to the SQL table it was to reside in. I did that on 11/14/05, so I can edit any data that was appended just fine, but if I try to edit data that was entered into the database on, say, 11/21/05, I get the window that gives me the options of "dropping changes" or "copy to clipboard" due to the data being in use by another user. I am the only one using the MDB, and I know no one is using the same record as I am (they all use MDEs). So basically any new data entered into these SQL tables cannot be edited in Access; they can be changed in SQL. I have seen several threads on this topic, but I must have something different in the mix because I am not getting the same results. Thank you in advance for your assistance! Try creating a Profiler trace on the app and see what turns up. Not
creating a primary key or unique index is usally the cause of problems like this (you don't say how you appended the data). Also, you're better off ditching the DSN and writing code to dynamically link the tables at startup and delete the links when the app closes down. That way you're sure to catch any schema changes. --Mary On 2 Dec 2005 14:50:13 -0800, wenzl***@yahoo.com wrote: Show quote >Greetings. I am using SQL Server 2000 to store my data, Access XP 2002 >SP-3 for the front-end, and MDAC 2.8 RTM. I cannot edit any of the >data in the SQL tables from Access. I can enter new data, but once it >is in there it is untouchable from Access. I can edit it on the SQL >Server side, but that's just the current work-around. The data came >from several Access databases that I wanted to 1) merge together and 2) >have the data reside in SQL. I created the SQL tables first, then >linked them to Access, appended all data into the appropriate tables, >then made certain columns the identity columns (on the SQL side), then >I made that identity column the primary key, too. Every user is a >member of the 'public' group, the 'public' group can select, insert, >delete and execute, and every user is also a db_owner. Some of these >settings happened at different intervals, but every time I made a >change on the SQL side I deleted the linked table in Access, linked >them all again, and in my understanding that is relinking the SQL >tables. Before I made the primary key distinction in SQL I did do the >Access side of it. I don't have any views, and I have the SQL table >created in the System DSN. > >Here's the thing that really confuses me. I had all the Access data >from the assorted tables merged into one Access database and then I >appended that data to the SQL table it was to reside in. I did that on >11/14/05, so I can edit any data that was appended just fine, but if I >try to edit data that was entered into the database on, say, 11/21/05, >I get the window that gives me the options of "dropping changes" or >"copy to clipboard" due to the data being in use by another user. I am >the only one using the MDB, and I know no one is using the same record >as I am (they all use MDEs). So basically any new data entered into >these SQL tables cannot be edited in Access; they can be changed in >SQL. > >I have seen several threads on this topic, but I must have something >different in the mix because I am not getting the same results. Thank >you in advance for your assistance! If it helps, I appended the data by running an append query in Access.
I linked the SQL table to the Access database, started a new query, made it an append query to that SQL table and brought every row into the SQL table. wenzl***@yahoo.com wrote on 2 Dec 2005 14:50:13 -0800:
Show quote > Greetings. I am using SQL Server 2000 to store my data, Access XP 2002 Check your linked tables have primary keys defined. However, if they didn't > SP-3 for the front-end, and MDAC 2.8 RTM. I cannot edit any of the > data in the SQL tables from Access. I can enter new data, but once it > is in there it is untouchable from Access. I can edit it on the SQL > Server side, but that's just the current work-around. The data came > from several Access databases that I wanted to 1) merge together and 2) > have the data reside in SQL. I created the SQL tables first, then > linked them to Access, appended all data into the appropriate tables, > then made certain columns the identity columns (on the SQL side), then > I made that identity column the primary key, too. Every user is a > member of the 'public' group, the 'public' group can select, insert, > delete and execute, and every user is also a db_owner. Some of these > settings happened at different intervals, but every time I made a > change on the SQL side I deleted the linked table in Access, linked > them all again, and in my understanding that is relinking the SQL > tables. Before I made the primary key distinction in SQL I did do the > Access side of it. I don't have any views, and I have the SQL table > created in the System DSN. > > Here's the thing that really confuses me. I had all the Access data > from the assorted tables merged into one Access database and then I > appended that data to the SQL table it was to reside in. I did that on > 11/14/05, so I can edit any data that was appended just fine, but if I > try to edit data that was entered into the database on, say, 11/21/05, > I get the window that gives me the options of "dropping changes" or > "copy to clipboard" due to the data being in use by another user. I am > the only one using the MDB, and I know no one is using the same record > as I am (they all use MDEs). So basically any new data entered into > these SQL tables cannot be edited in Access; they can be changed in > SQL. > > I have seen several threads on this topic, but I must have something > different in the mix because I am not getting the same results. Thank > you in advance for your assistance! then you'd get errors about the data being read only, not the error you're seeing. I get that error too occassionally - whenever I open the table to edit, or include all fields in the row. In my case it's due to triggers updating values in the same row - when you try to save the data, Access spots that data is being changed at the server at the same due to the trigger, and assumes another user is updating the record and so drops the changes. If you need to edit data directly in Access then use a query to retrieve just the fields that you require in your application, and make sure any columns that get updated by triggers are not included. Dan I don't have any triggers applied, which may be part of my problem.
Are you suggesting that I use update queries to edit data? <wenzl***@yahoo.com> wrote in message
news:1133800372.146702.278720@g47g2000cwa.googlegroups.com... Not having triggers shouldn't be a problem.>I don't have any triggers applied, which may be part of my problem. > Are you suggesting that I use update queries to edit data? Yes. Access is seeing something change in the data that's it not expecting and so assumes that another user changed the data. Personally, I'd try and avoid Access altogether in conjuction with SQL Server, I've migrated all my own apps to a VB client with ADO. Dan  |
|||||||||||||||||||||||
Other interesting topics