Results 1 to 4 of 4

Thread: Access Database Form Wont' Allow me to Update Records
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2007
    Posts
    580
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Access Database Form Wont' Allow me to Update Records

    I'm ultimately trying to setup an access database form, that has current data in it from an imported excel file that can allow updates. I have the form built but when I try to type in the fields it says "This Record set is not updateable". I do keep trying to setup the primary key but it won't allow to save?

    I want to be able to make changes to the data and also refresh the data in an access database. First, what is the best strategy to load this data to do that or link it?

    Second, I want to be able to modify those records in the form and then upload those changes or open the excel file and hit refresh to upload those changes, is that possible in Access?

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,742
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Access Database Form Wont' Allow me to Update Records

    If it is a linked table, you will not be able to update the records from Access (at least not without some complex VBA code). And you cannot add fields to a linked table from Access.
    If you want to be able to update the data tables in Access, then it is best to import the data into an Access table. You can set up an Autonumber field for a primary key, to satisfy that requirement.

    Is the Data Source of your Form the table directly, or a query based on the table?
    If it is a query, note that there are a number of reasons that a query may not be updateable. See here: http://allenbrowne.com/ser-61.html
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    May 2007
    Posts
    580
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access Database Form Wont' Allow me to Update Records

    Can you convert the linked table to a local table and setup the primary key and make edits in that form that way? Or, since its already loaded, how should I load the table to where it isn't linked or unlink it? Sorry, I'm not great at access.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,742
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Access Database Form Wont' Allow me to Update Records

    There are a few ways to make a Linked Table a local Table:

    1. You can create a Make Table Query from your Linked Table to create the local Table
    2. You can import the source of the Linked Table to created the local Table
    3. You can create the shell of the local Table that you want (i.e. create a new table, with all the appropriate fields), then run an Append Query from the Linked Table to this new local Table

    There first two options are probably the easiest. There are even tutorials/YouTube videos that show you how do this, that you can find with a simple Google Search.
    Last edited by Joe4; Sep 17th, 2019 at 02:31 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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