Update a table with counts from another table

Posted on 2005-04-18
Last Modified: 2010-04-23
Hello.  I'm trying to get a count on several columns in one Access table and insert them into another Access table.  The second table has no primary key, just a bunch of columns to be used for the counts.  The problem comes when I try to update the changes because there is no primary key.

The way we used to do it in vb6 was:

'only one column for illustration purposes

            Set TmpRS = MyDB.OpenRecordset(SQLStr, dbOpenSnapshot)
            MyCnt = TmpRS!MYCOUNT

            SQLStr = "INSERT INTO CountsTable VALUES (" & _
              MyCnt & ")"
            MyDB.Execute SQLStr

This is my code in VB.NET:

Dim objBuild As OleDb.OleDbCommandBuilder

        strConnMDB = ConnectMDB()

        With cmdDB
            .Connection = New OleDb.OleDbConnection(strConnMDB)

            .CommandText = "INSERT INTO Confirmed" & _

        End With

        objDataSet = New DataSet

       objAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM Confirmed", strConnMDB)
       objAdapter.Fill(objDataSet, "Confirmed")
            With cmdDB
                For Each dr As DataRow In objDataSet.Tables(0).Rows
                    .Connection = New OleDb.OleDbConnection(strConnMDB)
                    .CommandText = "SELECT COUNT(MyNUMBER)AS MyCOUNT FROM MyLIST" & _                        
                    dr.Item("ISSUES") = .ExecuteScalar
            End With

          strConnMDB = ConnectMDB()
            strSQL = "SELECT * FROM Confirmed"
            objAdapter = New OleDb.OleDbDataAdapter(strSQL, strConnMDB)
            objBuild = New OleDb.OleDbCommandBuilder(objAdapter)
            objAdapter.UpdateCommand = objBuild.GetUpdateCommand()
            objAdapter.Update(objDataSet, "Confirmed")
Question by:sg1nx01
    LVL 41

    Accepted Solution

    Well, the correct answer is to add a primary key (but that's so incredibly obvious, that I'll have to assume that you don't want to/couldn't do that for some reason).

    A somewhat strange way arround not having a primary key is to avoid using the UpdateCommand.   This can be accomplished by a strange set of opperations:

    1) load the DataTable... add, modify, delete rows to your heart's content
    2) Accept changes at the DataTable level
    3) Blow away the contents of the underlying table (with a "Delete * from Confirmed" in an ExecuteNonQuery)
    4) Now instead of doing a DataAdapter Update(),  you use Merge().

    This will force all of the rows in your DataTable to become marked as "Added" and will therefore use the InsertCommand (which oddly enough is not at all unhappy about not having a primary key)

    Okay, Okay... I'll admit that it's a rather odd solution...  
    LVL 41

    Expert Comment

    oops, I forgot a step....

    3a) pull a 2nd copy of the now empty Confirmed table using the normal DataAdapter Fill() method

    Author Comment

    I was thinking I had to add a "real" primary key, and then add (one primary key = the other primary key) to the where clause.  But, I tried it with an arbitrary number and it worked fine.  I couldn't figure out the whole merge thing.

    Thanks for your help!

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Article by: jpaulino
    XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String ( Literal, only instead of starting and ending with w…
    Introduction When many people think of the WebBrowser ( control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
    This video discusses moving either the default database or any database to a new volume.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now