Update a table with counts from another table

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

SQLStr = "SELECT COUNT(*) AS MYCOUNT FROM TblTmp"
            Set TmpRS = MyDB.OpenRecordset(SQLStr, dbOpenSnapshot)
            MyCnt = TmpRS!MYCOUNT
            TmpRS.Close

            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)
            .Connection.Open()

            .CommandText = "INSERT INTO Confirmed" & _
                " SELECT DISTINCT MyNUMBER FROM MyLIST"
            .ExecuteNonQuery()

            .Connection.Close()
        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)
                    .Connection.Open()
                    .CommandText = "SELECT COUNT(MyNUMBER)AS MyCOUNT FROM MyLIST" & _                        
                    dr.Item("ISSUES") = .ExecuteScalar
                    .Connection.Close()
                Next
            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")
            objDataSet.AcceptChanges()
sg1nx01Asked:
Who is Participating?
 
grayeConnect With a Mentor Commented:
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...  
0
 
grayeCommented:
oops, I forgot a step....

3a) pull a 2nd copy of the now empty Confirmed table using the normal DataAdapter Fill() method
0
 
sg1nx01Author Commented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.