Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2000 / SQL 2000 (Access 2000)

    I have SQL 2000 database and am trying to create an Access 2000 front end to perform minor maintenance on one of the tables in tha db. I've managed to create an .htm page in Access that connects to the database and displays the data in the table. I am unable to perform any updates. Keep getting 'A value could not be saved in the datasource's field' error. He field(s) in question are text (or nvarchar) fields. Am I trying to do the impossible?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access 2000 / SQL 2000 (Access 2000)

    It depends on what kind of maintenance you are doing. If you are changing table designs and such, that's a pretty tall order. If however you want to simply find and delete garbage records, or edit records where there is some sort of problem, you can do it directly from the Access user interface. Since you are using Access 2000, you can either connect from an .mdb file using and ODBC driver, or you can create a .adp (access data project) and connect using the native SQL Server tools in that user interface. If you are more familiar with Access, I would suggest the former - if you work mostly in SQL Server, you will find the latter more comfortable. I would not use any sort of HTML page (unless you cannot connect to the SQL Server except via the web).
    Wendell

  3. #3
    New Lounger
    Join Date
    Jan 2002
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 / SQL 2000 (Access 2000)

    Thanks for the response, I'm finally getting back to the project after being diverted off to other things. I decided to use the Mdb and ODBC as the method. What happens now is that I get an error 'The Recordset is not updateable' whenever I enter anything into the form's fields. This is a single table data update maintance project. (Add data to empty fields and make corrections to existing data.) The table has a primary key. All the form fields, except the key field, are enabled and not locked. The user is required to login as an SQL Database user.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access 2000 / SQL 2000 (Access 2000)

    I noted that you have some nvarchar fields in SQL Server - they seem to give Access fits for some reason, so that might be one thing to look at, but more likely it's because Access doesn't think the table has a unique (primary) key - view the attached table in design view and see if Access thinks it has one. Another reason might be that you are logging into SQL Server with a UserID that doesn't have write permission on the table in question. See if one of those three things is what's going on.
    Wendell

  5. #5
    New Lounger
    Join Date
    Jan 2002
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 / SQL 2000 (Access 2000)

    Thanks for the ideas. I started by looking at the key structure of the tables. The SQL table definition has a primary key. In Access this key is not recognized. When I try and open the access table in design view to tell Access about the key on the table I get the message that the table cannot be modified because it is a linked table. This is true in that the table is part of the definition on an SQL Db View. Is there a way around this?

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access 2000 / SQL 2000 (Access 2000)

    If you are working with a view, when you initially link to it you should get a prompt that asks you if there is a unique key for the view. If you specify one at that point, it should show up as a primary key when you view the linked view in design mode. Of course you want to make sure there is a unique key, as you may inadvertently update the wrong record if there isn't one.
    Wendell

  7. #7
    New Lounger
    Join Date
    Jan 2002
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 / SQL 2000 (Access 2000)

    The view is incidental to the Access update. I want access to perform maintenace on a single table with a unique key and that table happens to be part of an SQL db view. Access will not perform maintenance on any field in the view.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access 2000 / SQL 2000 (Access 2000)

    Sorry, I misunderstood your mention of a view. If the table has a primary key, when you attache directly to a table (not a view), it should automatically detect the primary key. Access will never let you make design changes to an attached table. Being used as a part of a view (that you are not attaching to) should not affect the attachment process. Have you tried deleting the attached table, and reattaching to it?
    Wendell

  9. #9
    New Lounger
    Join Date
    Jan 2002
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 / SQL 2000 (Access 2000)

    Deleting and redefining the table did the trick. It seems to be working fine now. Thanks for the attention to this post and the responses.

Posting Permissions

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