none
How to use a dataset to update an Access table? RRS feed

  • Question

  • I am writing a small VB program to update a table in an Access database. It reads a view to get a bunch of ids from the database, the view having been created specifically for this purpose. After doing the work, a table is to be updated.

    The view joins 3 tables, A, B, and C, and for example returns 30 records. After getting the records from the view, the code works on all 30 records as one batch. Once done, the individual records in table A need to be updated with the results.

    I am not sure of the best way to proceed. Here's is what i did, simply because it worked. Went to Project->Add New Data Source... and added a Data Source. Then looped through the view to get the data.

    Dim x As New List(Of String)  
    Dim Data_Adapter As New xxxDataSetTableAdapters.yyyTableAdapter
    
    For Each row As xxxDataSet.yyyRow In Data_Adapter.GetData    
     If row.col1 = "value" Then x.Add(row.col2)
    Next

    The code then calls a function and passes x.ToArray(), getting the results.

    Now i want to update the other table which is part of the same datasource. The records being updated make up a small amount of the table, for example, the table right now has 22,445 records, and i want to update 36 of them. What is the best way to do that?

    Monday, June 24, 2019 7:23 PM

All replies

  • Hello,

    Using a standard SELECT with one or more JOIN will not update correctly as this is one of the limitations of a TableAdapter component in how it generates UPDATE and DELETE commands. 

    The following right off the bat may be seen as incorrect as it's using SQL-Server but the text reflects about the limitations of a TableAdapter component and MS-Access acts the same as SQL-Server in regards to dealing with JOIN.

    https://docs.microsoft.com/en-us/aspnet/web-forms/overview/data-access/advanced-data-access-scenarios/updating-the-tableadapter-to-use-joins-vb

    How should this be handled? Well that is explained in the link above but again it has to be adapted to MS-Access.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Monday, June 24, 2019 8:20 PM
    Moderator
  • Thank you for taking a look.

    I do not require a join as all the columns are in the table. What i really need is an EXISTS(), but for some reason the query keeps returning all the records. So, i switched it for IN() which is working fine query-wise.

    So, i went into the data set designer and added IN() to the GetData query, and now it returns the correct data.

    Monday, June 24, 2019 8:56 PM
  • Hi,

    Do you resolve the issue?we appreciated you shared us your solution.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 4, 2019 8:03 AM