Results 1 to 3 of 3
  1. #1
    SteveApa is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    84

    MS Access to update SQL Server Tables

    Hi All!
    So, here is what I have. I work for a large company (our IT dept does not like me so much)... I wrote an access db for my business unit to manage data - inputs coming from global project managers. The only way I knew of to do this for global access was by using sharepoint lists. The sharepoint lists are link lists in the database. I use DAO routines to modify / edit / update the sharepoint lists recordsets. The Db works well - not huge... after 2 years, the largest of the 12 tables is 2000 lines long with 30 columns.

    Aside from this db, I have server experience... well, I read data from all kinds of sources for reporting in access, excel, power query... I connect to sql server, denodo, oracle... So I have experience reading, but none writing.

    I am looking moving my db from sharepoint lists to sql server. I don't know much about this so I have help from a colleague that has sql server experience but no access experience. We now have our own server setup on a shared server. We have a login with read/write access.

    My questions is...
    1) Can I just move my data from the sharepoint lists to tables on the sql server, re-link the tables and go???
    2) I had my colleague create a table on the server, I linked to the table in Access, opened the table in table view mode and I was not able to modify any of the data? I can do this on the sharepoint list.
    3) I tried to create an update query (not in sql) and was not able to update the sql server table. Do I need to do passthru queries to modify / append / delete data in table or can I use the standard access design mode?



    Thanks for the help.
    Steve

  2. #2
    SteveApa is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    84
    Solved... When linking table in access, I needed to define a primary key in the table... That's it!

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Thanks for updating the thread. You found the correct solution; SQL Server tables need primary keys to be update-able in Access.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-08-2017, 07:32 AM
  2. How Access exports tables from a server
    By gg80 in forum Access
    Replies: 11
    Last Post: 11-16-2013, 06:17 PM
  3. Importing Tables to access from server
    By masterjk in forum Import/Export Data
    Replies: 4
    Last Post: 12-05-2012, 10:58 AM
  4. Replies: 1
    Last Post: 10-22-2012, 06:44 PM
  5. Replies: 1
    Last Post: 01-22-2011, 12:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums