Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    We have Access 2007 linked to a SQL 2005 database through an ODBC link. We recently removed a couple of old unneeded fields from a table in the database, but it turned out that they were needed somewhere in another application, so we re-added the fields in SQL 2005's Enterprise Manager and refreshed the links in Access 2007's Linked Table Manager, and all our Access queries and the other application continue to work fine except that now when we open this table in Access and attempt to modify *any* field directly, we get the standard record lock message ("This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made....") even when no other user even has Access open. This does not happen in other tables in Access, and did not happen in this table before we deleted and re-added the fields in SQL 2005. We can still modify fields in this table using a query - *except* the fields that we deleted and re-added, which now give the record lock message even if we attempt to modify them using a query. We tried running the Compact and Repair function in Access, and rebooting the system, but the issue persists. How can we fix this?

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Not experienced this myself between Access and SQL
    No idea if this would resolve the problem
    Have you tried deleting the linked tables in Access, and then Re-linking them,
    rather than just doing a refresh.
    Just as a matter of interest what was the data type of the fields removed and replaced.
    I have seen this in MYSQL with BIT fields, but not in SQL Server.
    Andrew

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I have seen bizzare behavior like this when you change tables in SQL Server and then simply do a refresh. The method I've used to correct the issue is to drop the table (delete the link to it) and link to it again, rather than simply refreshing the link. Let us know if that resolves the problem.
    Wendell

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your replies. The types of the deleted/re-added fields were bit x3 and integer x1. The suggestion to delete and recreate the link sounds promising. Could you provide, or direct me to, details of how to do this? I can't see any facility to delete a link in Linked Table Manager in Access; I can see the database listed under External Data > More > ODBC Database > Link > Machine Data Source, but I can't see how to delete/re-create a link. Can a link be backed up in case something goes wrong with recreating it (is the link stored in the .mdb file)?

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    To delete a link, you simply click on the table in the Navigation Pane, and press the delete key. It will ask if you want to delete the link to that table - click OK and it will be gone. Then you click on External Data on the Ribbon, choose More in the Import section, and specify ODBC Database from the drop-down. Choose Link to the data source by creating a linked table, and then you get the familiar "Select Data Source" dialog box that gives you choices to connect from. Select the appropriate ODBC Data Source, and it will then display the SQL Server tables in the database. Find the one(s) you deleted the link for, click the check box, and click OK, and you should have them back.
    Wendell

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. I deleted and recreated the link, but we are still getting the spurious record lock messages when attempting to modify the deleted/re-added fields via a query, and when attempting to modify *any* field directly in the table.

    (Is the link stored in the .mdb file? We have several .mdb files for multiple users, and the issue occurs in all of them since the fields were deleted and re-added; does this suggest that the problem is in the database rather than in Access?)

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by Murgatroyd View Post
    Thanks for your reply. I deleted and recreated the link, but we are still getting the spurious record lock messages when attempting to modify the deleted/re-added fields via a query, and when attempting to modify *any* field directly in the table.

    (Is the link stored in the .mdb file? We have several .mdb files for multiple users, and the issue occurs in all of them since the fields were deleted and re-added; does this suggest that the problem is in the database rather than in Access?)
    Have you introduced new fields in a SL table that has been defined as a bit field? If so, have you defined the default as 0, You will also have to write an update query in SQL server to write 0 to this field.

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. There are several other bit fields in this table. Some have "Allow nulls" = "Yes" and some have "No". Some have defaults and some don't.

    The three bit fields that were recently re-added currently have "Allow nulls" = "Yes", and they have no default. Could this be causing the problem in Access?

    If so, could you provide, or direct me to, details of how to write and run an update query that would fix this in SQL Server 2005 Management Studio?

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I just did a test here on a database linked to SQL 2005 from Access.
    IF any of the fields allowed NULLS, and also had NULL values in
    then the behaviour as above was shown.

    Once I set all the NULL Values in the fields to 0,
    then set the default value to 0 and also the allow nulls to False,
    the problem went away.

    It would appear that ACCESS has issues with this scenario while SQL does not.

    NOTE... I only had to refresh the links to fix this NOT drop the links
    and recreate.

    The basic query in the Enterprise Manager was something like....


    UPDATE [TableNameGoesHere]
    SET [BitField]=0 Where [BitField[ Is NULL

    You will need to run it for Each BIT field BEFORE you Edit the Table
    and set the NO NULLS and Default Value property.




    Andrew

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    That almost surely is the issue - bit fields in SQL Server must have a default set, and allow nulls must not be set. To change that start SQL Server Management Studio and go to the table in question in the database, right-click on the table name and choose Modify. That will open the table in design view. Click on one of the fields in question, and make sure the Allows Nulls check box on that field is empty. Then look down at the Column Properties and in particular at the "Default Value or Binding" and put either a 0 or a 1 as the default. Then save the table. Then you will want to relink the table again. Andrew is also correct in that you will want to make sure all the records have a value for each of the bit fields before you change the table design.
    Wendell

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your replies. I found that in this table, some other bit fields have "Allow nulls" = "Yes" and some have "No", and most of them have defaults set but a couple don't, but none of them actually contains nulls (presumably the application that writes new records also writes defaults to the bit fields that don't have them set in the table), so the critical factor causing this issue seems to be having bit fields that *contain* nulls.

    Can I fix the three fields concerned in one update query; e.g., as follows?
    UPDATE [TableName]
    SET [Field1]=0 Where [Field1] Is NULL
    SET [Field2]=0 Where [Field2] Is NULL
    SET [Field3]=0 Where [Field3] Is NULL

    (Is the "Where" clause needed, as all records have nulls in the three fields concerned?)

    And I need to run the update query to put data in the fields *before* I modify the fields to set the null and default parameters - is that correct?

    Is there an easy way in SQL Server Management Studio to make a backup copy of the table before running the query (and to restore it afterwards), in case something goes wrong?

  12. #12
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    You are safer to run 3 queries one for each bit field as in my earlier example.

    BUT IF you are sure that all fields are all NULL throughout then

    UPDATE [TableName] SET [Field1]=0, [Field2]=0, [Field3]=0 Where [Field1] Is NULL


    Should do it.


    You can Run a Make Table query to generate a backup


    SELECT TableToBackUp.* INTO BackupOfTable FROM TableToBackup


    Andrew

  13. #13
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    1. For the update query: UPDATE [TableName] SET [Field1]=0, [Field2]=0, [Field3]=0 Where [Field1] Is NULL

    Is the "where" clause on the end needed? referring to Field1 only?

    2. And I need to run the update query to put data in the fields *before* I modify the fields to set the null and default parameters - is that correct?

    3. For the make table query for a backup: SELECT TableToBackUp.* INTO BackupOfTable FROM TableToBackup

    Does this backup everything - structure, data, keys, etc? If I something went wrong, how would I replace the table with the backup copy?

  14. #14
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    1. For the update query: UPDATE [TableName] SET [Field1]=0, [Field2]=0, [Field3]=0 Where [Field1] Is NULL

    Is the "where" clause on the end needed? referring to Field1 only?


    IF ALL the Data in ALL the BIT Fields 1,2,3 on ALL Records are NULLS that require setting to 0 then NO you would not need the WHERE.
    But I am a Paranoid Soul, so I would probably Run 1 for each Field.

    2. And I need to run the update query to put data in the fields *before* I modify the fields to set the null and default parameters - is that correct?


    YES

    3. For the make table query for a backup: SELECT TableToBackUp.* INTO BackupOfTable FROM TableToBackup

    Does this backup everything - structure, data, keys, etc? If I something went wrong, how would I replace the table with the backup copy?


    It is an EXACT Copy of the Table Structure and Data, BUT it does not have any existing Relationships and NO Keys are created.
    You would need to create those manually (IF you wanted them in the Backup Table)
    IF you had to get the data back into the Main Table from the backup, you would probably be better off erasing the data from the Main table with a Delete query,
    Then using an Append query to transfer the data back into the Main Table.


    IF you are wanting to cover total disaster mode, you could always make a Backup of the Entire Database first.
    (That is using the Enterprise Managers Backup Option, NOT a Make Table on each Table.)

    So Sequence ought to be.

    1. Make a Backup of the Table
    2. Run the query that Updates JUST the bit fields to 0
    3. Modify the design of the table to make the bit fields have a 0 Default and Not allow Nulls





    Andrew

  15. #15
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    "If you had to get the data back into the Main Table from the backup, you would probably be better off erasing the data from the Main table with a Delete query, then using an Append query to transfer the data back into the Main Table."

    So the table backup/restore procedure would be:
    1. Backup
    - Select query to make copy of original table (SELECT TableToBackUp.* INTO BackupOfTable FROM TableToBackup)
    2. Restore
    - Delete query to delete data in original table
    - Append query to copy data from backup to original

    What would be the syntax for the Delete and Append queries? (I'm not a paranoid soul - just once bitten, twice shy

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •