Results 1 to 15 of 15
  1. #1
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151

    error when moved access database to SQL server

    I had an access database where I moved the Tables to a Database I created under my SQL server and then moved the Tables to this Database. I linked them back into My access and all the records, queries and forms work. However i am not able to modify any records. I get a conflict error about changes from another user even though I am the only user. I googled this error and many posts mention that this is an issue with having a Bit type in the table with no default. I checked and found several fields with Bit type so I added the default as 0 but when I try to save the Table I get a message as follows:



    Saving the changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be recreated or enabled the option Prevent saving changes that require the table to be re-created.

    I am not familiar with SQL too much so any help deciphering this error would be greatly appreciated.

    Thanks,
    Sam

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    can you change the sql table field from BIT to SMALL INT?

    access dont like BIT fields.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    In SQL Server, change the settings which will allow you to make table changes without dropping the table first.
    In SSMS, go to Tools...Options...Designers,,,Table & Datasheet Designers. Untick ...Prevent saving changes that require table re-creation

    Click image for larger version. 

Name:	Capture.PNG 
Views:	28 
Size:	24.1 KB 
ID:	43864
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    It was a Yes/No filed in access which was converted to bit when i exported the table to SQL

  5. #5
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    So changing bit default didn't resolve my issue. At first I thought it might be a security issue but just noticed something which may help someone diagnose this for me. I am able to create new records and I can modify any records which were originally created in Access before the move to SQL. Only new records added after the move cannot be edited after "save" the record by moving off this record and then trying to come back to it.

  6. #6
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you add a new record in SQL server (SSMS)? Are you getting the same error (another user....)?

    Could you upload a copy of your Access table here so we could have a look (with no data or just a few dummy records)?

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    There is nothing wrong with using bit fields in a SQL Server BE providing you set a default value and ensure there are no null values from before you set that.
    I gave you the solution in post #3 but you seem to have overlooked that.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    I haven't overlooked that. I followed your post the change worked but didn't solve my issue of editing records

  9. #9
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    But did you updated your original data to replace the existing Nulls with zeros (setting the default only affects the new records)? Have you tried to add new records in SSMS?
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    I am able to update a file using the Query execution on the SQL Server management studio

  11. #11
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    The thing is that the existing records were NOT null. Even before I made the change they were appearing as 0

  12. #12
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    They were appearing as 0 in Access or SQL? In Access they would because it is either 0 or -1, but SQL BIT allows a third value of Null. What error are you getting in Access when attempting to add a record?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by swenger View Post
    I haven't overlooked that. I followed your post the change worked but didn't solve my issue of editing records
    Well, it would have helped if you had mentioned that without being prompted
    Gicu's comments in post #12 are almost certainly the explanation. If there are still null BIT fields records in SS you will get write conflict errors in Access
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #14
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    It is true. Previous records appear as NULL. I would like to change them to zero through a query but not so familiar with syntax of SQL.

    I would like to use something like this but it doesn't like any of my where syntax I tried

    UPDATE [details]
    Set [Lifetime]=0
    Where isnull([Lifetime]);

    Where [Lifetime] = NULL;
    Where [Lifetime] = "NULL";

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    ... Is null
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Moved access database to new server
    By AliceCampbell in forum Access
    Replies: 7
    Last Post: 10-09-2017, 10:44 AM
  2. Error when migrating Access database to SQL Server
    By AnneForumer in forum Import/Export Data
    Replies: 1
    Last Post: 11-18-2016, 10:24 PM
  3. PHP ADO to ACCESS Database on server FATAL Error
    By jimneely in forum Programming
    Replies: 4
    Last Post: 05-08-2014, 02:14 AM
  4. Replies: 14
    Last Post: 07-29-2013, 02:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums