Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    313

    Unanswered: MS Access and SQL Server

    Hi,

    In MS Access, I used ODBC to link the SQL Server tables to MS Access.
    When I open the linked tables, the record is read-only. I cannot change/insert data in the table.

    What's wrong with it?

    How can I fix it?

    Thanks.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Does the table have a primary key? Have you been granted INSERT and/or UPDATE access in SQL security? Are you dealing with a query based on a join?
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Sep 2005
    Posts
    313
    How?

    I just installed SQL Server. I make ODBC link tables in MS Access. After link SQL Server tables, it is only-read.

    In SQL Server, I can insert/update the record of the tables. However, linked tables from SQL Server in MS Access cannot insert/update the record of the table.

    Why?

  4. #4
    Join Date
    Jan 2004
    Location
    Netherlands
    Posts
    26
    Check your odbc settings. did you use a user/password combination with enough privileges on the database?

  5. #5
    Join Date
    Sep 2005
    Posts
    313
    I understand why.

    I fixed it.

    When I make ODBC linked tables to MS Access, there is popup box message:

    Select Unique Record Identifier

    I select the fields, then I can update/insert the record in the table of the MS Access.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    That is related to what Teddy was alluding to. If you have no unique identifiers then you have no primary key and you also cannot have any relationships which means there is no relational integrity. That means you don't have a relational database.

    Access won't write to a linked table (or use a linked table in an update query) until it is assured that each record will be unique hence the request for a unique key. I really wouldn't advise that you rely on your FE to enforce uniqueness and in any case there is no RI with this setup.

    Hope this helps:
    http://r937.com/relational.html
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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