Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Posts
    3

    Question Unanswered: MS Access linking to MS SQL Server tables

    I have two questions regarding Linking from Access to MS SQL tables. (I'm making a Access frontend for a MS SQL DB.)

    1. I created an ODBC conntection and linked to the MS SQL tables, but I can't update or delete the tables. Got any ideas? My UID has full permissions on the SQL server.

    2. Can I link to MS SQL tables without creating an ODBC on the end user's computer. Is there a way to connect to the MS SQL through a connection string or something?

    Thanks in advance.

    James

  2. #2
    Join Date
    Jun 2003
    Posts
    10
    Reply to point 1.
    Are you trying to update/delete RECORDS in the tables or the STRUCTURE of the table itself? If its the latter, you will have to do that in SQL server itself eg in SQL server enterpise manager, not in access (unless you write an access project instead of an access database). If its the former, you definitely have something wrong either in your SQL server user permissions, OR in the way you set up the user code part ( which is easy to get wrong) of the ODBC data source.

  3. #3
    Join Date
    Jun 2003
    Posts
    3

    Post

    Well, I created an ODBC connection on a desktop computer. Opened Access and chose "File, Get External Data, and Link Tables." From the drop-down box, I selected ODBC Connection and chose the one I already created.

    Now, I can double click on the linked table and view the data, make forms from the data, and query the data, but I can't make any inserts, updates or deletions from the linked table.

    The user that is logged into the local desktop is the same domain user that is Admin on the SQL Server.

    I didn't see anything in the properties of the ODBC Connection that looks like it limited the access to the SQL Server.


    James

  4. #4
    Join Date
    Jun 2003
    Posts
    10
    In the odbc data source is a page in the setup which refers to "How should SQL server verify the authenticity of the logon" , u have various options which r critical in what access you have to the db, if any. I cant give you more info, but you have to have these right, inc items on Client configuration button (i use named pipes here, it worked on our server). Are you SURE you have update permissions set up in sql server for user you are logged in as, and for user it may be getting from the data source, (can have user id embedded in it). It took me some trial and error to make it work. Also you must have, to be able to update records from Access in a linked SQL server table. The table must have a unique primary key in SQL server. Check that you have a unique primary key for ALL the tables. Also, a field defined in an SQL server table with datatype that isnt compatible with Access can cause problems with updating. I have fallen into all of these traps myself before.

  5. #5
    Join Date
    Jun 2003
    Posts
    3

    Thumbs up

    Thanks for your help nipsder. It was partly having the unique primary key and part permissions.

    I had added the PK to the SQL Server, but it was not showing up in the access frontend. I had to re-link the table for it to see the PK field.

    And the delete permission was not set (my bad).

    James

  6. #6
    Join Date
    Jun 2003
    Posts
    10
    Ahh thats good news. Yes, forgot to tell you that ANY chg u make to the table structure in SQL server u have to delete the link and re link. I fell into that trap too , but forgot to tell you. lol

Posting Permissions

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