Thanks Thanks:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: MS Access table issue after adding row

  1. #1
    Board Regular
    Join Date
    Feb 2017
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default MS Access table issue after adding row

    Lads,

    I'm working with Table that uses query from MS Access and I have added manually few columns like you would normally do on excel. I have manually filled cells with data. When data is refreshed from query and if rows are automatically added it shifts all data to different rows, but data entered manually by me on added columns stays on the same row. Is there a way to make them also shift? Thanks.

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    46,985
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    6 Thread(s)

    Default Re: MS Access table issue after adding row

    I am not quite clear I understand what you are saying.

    You can only add new fields that you can hard-code data into in the Table.
    In a Query, the only type of fields you can add are Calculated Fields (which you cannot hard-code into, because they are formulas).
    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
    Feb 2017
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access table issue after adding row

    I don't know how to explain it in any other way. Lets say below columns A/B/C are connected to query and columns E/F/G are contain values that I need to change manually. If one row is added automatically by query after refresh it shifts all data 1 row down. See second table where data LIVE/0/Yellow were added by query. Manually added data on cells E2/F2/G2 need to shift down 1 row as well as data is relating to LIVE/1/Red. Hope this makes sense. Sorry if this sounds silly, but I have never worked with tables that are connected with query. Thanks.

    Current
    A B C D E F G
    1 status product type colour
    2 LIVE 1 Red ab ad ae
    3 LIVE 2 blue
    4 LIVE 3 green
    5 LIVE 4 blue

    After row added (Query data refresh)
    A B C D E F G
    1 status product type colour
    2 LIVE 0 Yellow ab ad ae
    3 LIVE 1 Red
    4 LIVE 2 blue
    5 LIVE 3 green

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    46,985
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    6 Thread(s)

    Default Re: MS Access table issue after adding row

    Where exactly is this Table located?
    Is it an Access table, or an Excel table?

    If it is a Table Excel that is set up for columns A, B, and C, then your other columns (D-G) are separate and outside of your table.
    Since there is no connection between those columns, and columns A-C, they would not move with data added to the Table.
    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!"

  5. #5
    Board Regular
    Join Date
    Feb 2017
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access table issue after adding row

    Joe4,

    Yes it's located in excel spreadsheet. Is there a way to connect D-G columns?

  6. #6
    Board Regular
    Join Date
    Feb 2017
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access table issue after adding row

    Anyone? What about marco's?
    Last edited by strongman86; Apr 17th, 2018 at 06:25 AM.

  7. #7
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    46,985
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    6 Thread(s)

    Default Re: MS Access table issue after adding row

    Anyone? What about marco's?
    A little patience please. You posted at 2:00 AM and 6:00 AM my time, and I am usually sleeping then.

    You would need to expand the columns of your table.
    However, it is a linked Table/Query from Access, I do not know if that will work (unless you "unlink" it and make it a permanent copy in Excel no longer related to Access).
    Otherwise, you may need to add the new columns in Access, then re-link/refresh that in Excel.
    Last edited by Joe4; Apr 17th, 2018 at 07:54 AM.
    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!"

  8. #8
    Board Regular
    Join Date
    Feb 2017
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access table issue after adding row

    Joe4,

    Unlinking is not an option here it needs to up to date all times and neither editing database as I have no editing access/rights. I have come up with this post : https://www.mrexcel.com/forum/excel-...ata-query.html

    And I'm quite confused and too dumb to understand it. Could this approach would work you think? If yes would you be able to explain it in more simple way? Much appreciated.
    Last edited by strongman86; Apr 17th, 2018 at 09:20 AM.

  9. #9
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    46,985
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    6 Thread(s)

    Default Re: MS Access table issue after adding row

    I think that approach could work, though I have never used it myself.
    As it is a method that I have never attempted myself, I don't think I could explain it any simpler than Fazza did.
    You may want to reply to that thread where Fazza describes the method, and mention that you have a similar situation that you are trying to get to work (and provide a link to this thread there).
    Maybe Fazza will see it and weigh in here.
    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!"

  10. #10
    New Member
    Join Date
    Aug 2012
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access table issue after adding row

    Hi Strongman.

    Are you adding data in excel sheet next to data that is linked to an access table? or adding data to an access table that is linked to an excel sheet?

    From the conversation it seems to me it may be the Excel sheet with data linked in from Access, in which case the excel sheet is acting as an output to the access database and the additional data you are adding is not related in any way to that.

    If this is the case it's going to take a bit to keep it together as it's a little like printing a report and writing on the paper then printing a new copy of the report and wanting the writing to appear on the new copy.

    So to accomplish this you'll need a unique identifier in the data coming in from access, line number, some kind of ID.
    In your additional comments you need to include this ID along with what you're adding.
    You then need a vlookup to put your additional data into the table next to the access data.

    If this sounds about right let me know and send me your excel sheet and I can add the bits or explain further.

    Ron

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
  •