Update MS Access Database Records

  • Thread starter Thread starter thomasp
  • Start date Start date
T

thomasp

First of all, thanks for the help on my previous VB.NET/MS Access questions.
This time I need do the following

1. Connect to a table
2. step through each of its records
3. read the value of two of the records fields
4. pass those values to a function
5. write the value returned by the function the same record in a third field

Hope this makes sense. Basically I want to read records and based on values
of two of their fields I want to update another of their fields.

Thanks Thomas
 
Following what you are saying:
Use a DataReader to pull the records, as it is a forward only stream. Pull
the two bits you need, run the function and update. If you have problems with
the update (possible with Access), save the value of the primary key field
and the calculation to some form of persistent store (this can be a file or
even a hashtable (temporary persistence)). When you are done with all rows,
you can run through the persistent store and update values.

A better way:
Write a query in access that runs through each record and does the
calculation. Something like:

UPDATE MyTable
SET FieldNew = Col1 * Col2


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
This is what I come up with, it works, but open for any further suggestions.

Private Sub cmdApply_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdApply.Click

Dim cmd As System.Data.OleDb.OleDbCommand
Dim sql As String
Dim strWeapon As String
Dim myConnection As New OleDbConnection(strConn)
Dim myCommand As New OleDbCommand(strSQL, myConnection)
Dim myReader As OleDbDataReader

strSQL = "SELECT * FROM Q36"
myConnection.Open()
myReader = myCommand.ExecuteReader()

Try
While myReader.Read()

If Not IsDBNull(myReader!Db) And Not
IsDBNull(myReader!Velocity) Then
strWeapon = funWeaponType(Val(myReader!Db),
Val(myReader!Velocity))
Else
strWeapon = "False"
End If

If strWeapon <> myReader!Weapon_Type Then
sql = "UPDATE Q36 SET Weapon_Type = '" & strWeapon & "'
WHERE ID = " & myReader!ID
cmd = New OleDbCommand(sql, myConnection)
cmd.ExecuteNonQuery()
End If

End While
Finally
myReader.Close()
myConnection.Close()
End Try

End Sub
 
Gregory,

Just to give you an idea of what I am doing, to two pieces of information
that are pulled from the records are the Db and Velocity of rounds tracked
by a radar. These two parameters are sent to the WeaponType function. This
function runs yet another query with the two parameters to see if they match
a weapon type. If so it returns a value and it is written to the record
where the parameters came from. The code gets run on about 3000 or more
targets each time, so I am trying to find the most profient method to use.

As I am using this, I don't think the one query route you mentioned will
work. If I am wrong, please let me know as I am sure that would work
faster.

Thanks,

Thomas
 
Just to give you an idea of what I am doing, to two pieces of information
that are pulled from the records are the Db and Velocity of rounds tracked
by a radar. These two parameters are sent to the WeaponType function.
This
function runs yet another query with the two parameters to see if they
match
a weapon type. If so it returns a value and it is written to the record
where the parameters came from. The code gets run on about 3000 or more
targets each time, so I am trying to find the most profient method to use.
Click to expand...

It does sound like you're performing a database join in your code there. Is
there some reason why you can't do the whole thing in SQL?

MC
 
Using a DataSet might be a quicker solution (read the columns you want to
check as well as those to set into a dataset using .fill , change the dataset
values you need to change, then update the database using .update.
 
I don't see how this could be done in a join. Maybe you could give an
example.

thanks

Thomas
 
Dennis,

If you have time, could you post an example of using the DataSet with only
the needed columns?

Thanks,

Thomas

P.S. I am doing all this in Iraq. I have a very simple web site that I am
trying to keep up while I am here. It just shows some of the daily stuff
that happens here.

http://www.msala.net/archives.php
 
Try something like the below...not tested of course as I have no idea what ID
is nor weapon_type nor Db Column, etc. are supposed be..I've assumed they are
columns in your DataBase and somewhere you are inputing an ID to match to the
DataBase ID column. I've also assumed that FunWeapon is a function that you
define somewhere. Good Luck!

Dim sql As String
Dim strWeapon As String
'Define a DataSet and a Data Adapter
Dim Dset as New DataSet
Dim DBAdapt As New OleDbDataAdapter
'Define SQL search string (not sure about the <>DbNull as it may be
different syntax required)
strSQL = "SELECT ID, Db, Velocity, Weapon_Type FROM Q36 WHERE ID
='ID' AND Db<>DbNull AND Velocity<>DbNull"
'Open the DataSet and return rows found matching SQL search into
Dataset
DBAdapt.SelectCommand = New OleDb.OleDbCommand(strSQL, strConn)
DBAdapt.Fill(Dset, "Q36")
'Iterate thru each row returned and reset the Weapon_Type Field
Dim drow as DataRow
For Each drow in Dset.Tables(0).Rows
strWeapon =
funWeaponType(Val(dr.Item("Db"),Val(dr.Item("Velocity)))
If strWeapon <> dr.Item("Weapon_Type) Then
dr.Item("Weapon_Type") = strWeapon
next
'Update the DataBase
DBAdapt.Update(Dset, "Q36")
'Clean up the objects
DBAdapt.Dispose
DSet.Dispose
 
Sorry, I forgot to define a CommandBuilder...insert the following line just
after the Dim DBAdapt...

Dim DBCmdBuilder As New OleDb.OleDbCommandBuilder(DBAdapt)
 
Back
Top