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

    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.

  2. #2
    SteveApa is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    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 online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Nevada, USA
    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

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 - Senior Forums