ADODB how to find and update records

[kap1]

I have a visual basic 6 application that saved data in the wrong Access fields.
Ex. Table: Expenses
form field "personal" saved in Expenses.Business
form field "business" saved in Expenses.Personal

I need to create an executable to send to customers to correct this problem. I read that RecordSets and Cursors shouldn't be used (multi-user concerns). How can I do this? (ADODB)

I thought I would do the following but don't know how to code it.
-query table for records where personal or business are not blank
-save data into temporary variable
-update correct field
-loop thru the rest of the records
Thanks for the help.

 

[Dimandja]

This sort of problem usually happens in homeworks. You don't really have any specific question to ask, right?

If this is for real, isn't your boss quite reckless? Asking someone who has no knowledge of the language or the database to fix them?

Anyway, try coding first. If you run into a problem, let us know.

 

[kap1]

Here is a stripped down version of the code I came up with to accomplish switching data in two Access dbs fields. This works but I'm wondering why should I use a command object.execute vs. a recordset.Open?

Sub ModifyTable(strDBPath As String)
Dim conModify As New ADODB.Connection
Dim retvalue As Boolean

' Open connection
Set conModify = New ADODB.Connection
conModify.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDBPath

'SQL Query
sqlQuery = "SELECT …”

Set recExpUnMedical = New ADODB.Recordset
'.open opens a cursor
recExpUnMedical.Open sqlQuery, conModify, adOpenForwardOnly, adLockPessimistic, adCmdText

While Not recExpUnMedical.EOF
If IsNull(recExpUnMedical!Orthodontia) = True Then
OpticalNullFlag = True
Else
tempOptical = recExpUnMedical!Orthodontia
End If

'build the UPDATE statement to switch the data
strSQL = "UPDATE ExpUnMedical set"
If OrthodontiaNullFlag = True Then
strSQL = strSQL & " Orthodontia = NULL"
Else
strSQL = strSQL & " Orthodontia = " & tempOrthodontia
End If
strSQL = strSQL & " WHERE ClientID = '" & recExpUnMedical!ClientID & "';"

conModify.Execute strSQL
recExpUnMedical.MoveNext
Wend

'clean up open recordset and connection
recExpUnMedical.Close
Set recExpUnMedical = Nothing
conModify.Close
Set conModify = Nothing
End Sub

 

[Dimandja]

ADO is full of implicit behavior, although I'm sure it was done for our benefit. [...]

Here's a classic example: you create and configure an ADO recordset object to be a client-side, static, optimistically locked recordset. You then set the recordset equal to a command object's Execute method. Poof! You get a server-side, forward-only, read-only recordset. Surprise! There's a workaround, using the recordset's Open method with the Source argument set to the command object."

From:

http://member.advisor.com/doc/06131

 

[fredericofonseca]

Some comments on your code.

Dim conModify As New ADODB.Connection (A)
Dim retvalue As Boolean

' Open connection
Set conModify = New ADODB.Connection (B)

You should not dim the object as a "new". This is done on the "set obj = ".

You did not "dimmed" the recordset( unless this is done outside the code shown). You should always do it, and an easy way to force this is to add "option explicit" to the top of your modules/code.

OpticalNullFlag = True
Else
....
If OrthodontiaNullFlag = True Then
Should you not be using the same flag?



As for your other question.

You can use the connection and the command objects to perform updates.
The recordset can only be used to perform selects (e.g. something that returns a record)

On this particular case I would use a command object, as it will be faster if you preprepare the SQL statement.


On the overall problem you should probably not be doing it like this.

a update with a inner select should be a lot faster and require only one line of SQL, and you would also not have to worry about the NULL bit.

something like
update tbl f1
set f1.fld = (select f2.fld from ...) (leave the rest to your imagination.)
where f1.key in (select key from tbl2)

You may have some problem if you have duplicates, but there are ways around that.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com