Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146

    Unanswered: Access db with SQL Server backend

    Last week I moved the backend of my db on to our sql server, using append queries I moved all of the historical data to the proper tables....

    Issue:

    Now the users cant edit any data that has been added after the move to sql server. They can however edit data in the same table prior to the move. The error was something along the lines of "the record is being edited by another user do you want to drop your changes or copy them to the clipboard".

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What sort of locking did you use in the access db? Are you using an adp? Try setting an optimistic lock..?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146
    in access my options are set as follows:
    Default recod locking: No locks

    Open db using record-level locking: this is checked now?

    ---
    no adp and is there a way to set optimistic locking in the options?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You may want to consider using an adp. I don't think you'll experience as many problems.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    What is an ADP? I have an access database that has about 4 to 5 users... (not on all the time but could be). Do I need some sort of record locking that isnt defaulted with a .mdw?

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  6. #6
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Hi there,

    There is no need to use an ADP file with SQL if you don't want to, especially with the number of users you are talking about. I quite happily run an Access 2000 database with a SQL Server 2000 database using ODBC.

    Try deleting and then re-linking all of your tables within the Access front-End. When access prompts you to "Select Unique Record Identifier' don't just press 'Cancel' - u must select a field here if you want your data to be read/write.

    Let me know how you get on.
    Cheers

  7. #7
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Quote Originally Posted by JSThePatriot
    What is an ADP? I have an access database that has about 4 to 5 users... (not on all the time but could be). Do I need some sort of record locking that isnt defaulted with a .mdw?

    JS
    ADP is an Access Database Project isnt it?

    Got that part figured out. But I still need to know about record locking. Does anyone know if I should implement some sort of record locking that isnt default with an .mdw?

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  8. #8
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146
    i deleted my linked table and relinked it with no luck.

    the odd thing is why can't i edit new records but i CAN edit older records...

  9. #9
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    HAve u tried seeing if you can edit them directly in SQL server? It might give a clue as to where the problmes lies?

  10. #10
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146
    i can edit the records on the server...

    i tried creating a new access db and linked in the same table and i still get the same error.

  11. #11
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    If the users can't edit new data how have they entered it into SQL Server in the first place, I'm confused, sorry.

Posting Permissions

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