I am trying to work with MSAccess through the use of ADOBC with VB6. I am having trouble with modifying current values.

I have 3 input text fields in the form. FCUIPADDRESS, MCASTIPADDRESSA, MCASTIPADDRESSB. The MCASTIPADDRESSA+B are associated with the FCUIPADDRESS.

For example: 204.168.11.234, 192.168.1.1, 192.168.1.2

I also have the same fields in MS Access(3 columns)

I want to check if the FCUIPADDRESS value exist in the FCUIPADDRESS column, if it exist then go to the MCASTIPADDRESSA+B that is associated with that FCUIPADDRESS and update it. The MCASTIPADDRESSA+B should be in the same row as the FCUIPADDRESS.

If it does not exist then add all three values into each column in Access.

Thanks.


If rstRecordSet.EOF = False Then
'
'Move to the first record
'
rstRecordSet.MoveFirst


'This checks if the FCUIP data exist. If so, it will update.
Do


If (rstRecordSet.Fields("FCUIPAddress")) = Text1.Text Then

Exit Do

End If

rstRecordSet.MoveNext
i = i + 1

Loop Until rstRecordSet.EOF = True

'This will move the cursor to the correct row if the FCUIP is found or if not then it will go to the last row.
Do

rstRecordSet.MoveFirst
If MoveNext = i Then
With rstRecordSet
'.AddNew
.Fields("FCUIPAddress") = Text1.Text
.Fields("McastIPAddressA") = Text2.Text
.Fields("McastIPAddressB") = Text3.Text
.Update
End With
End If
MoveNext = MoveNext + 1
rstRecordSet.MoveNext
Loop Until MoveNext = i + 1


rstRecordSet.Close

End If

'

conConnection.Close
'
'Release your variable references
'
Set conConnection = Nothing
Set cmdCommand = Nothing
Set rstRecordSet = Nothing

Hi,

U dont have to Loop thru Record sets to Find the IP Address.. Check this Code:

Dim sSQL As String
Dim RST As New ADODB.RecordSet

sSQL ="Select * From Table_Name Where FCUIPAddress ='" _
& Text1.Text & "'
Set RST= Nothing
RST.Open sSQl,Conn,AdOpenDynamic, AdOpenLockOptimistic
'
If RST.EOF Then
' rec Not Found
RST.AddNew
RST("FCUIPAddress") = Text1.Text
Else
'Rec Found Edit It
RST.MoveFirst
End If
RST("McastIPAddressA") = Text2.Text
RST("McastIPAddressB") = Text3.Text
RST.Update

RST.Close
Set RST=Nothing


Replace Table_Name with ur actual table name

Regards
Veena

See, you have a ADO cursor.(recordset) right.

The following loop iterate the cursor from the first record to the last.
A flag is to be set true if an existing FCUIPAddress is found in the iteration and the other two fields are updated with your Text fields

Dim AlreadyDone as Boolean
AlreadyDone = False

If Not RST.BOF() then
RST.MoveFirst
endif

Do whlie NOT RST.EOF()
if RST.Fields("FCUIPAddress") = Text1.Text then
RST("McastIPAddressA") = Text2.Text
RST("McastIPAddressB") = Text3.Text
RST.Update
AlreadyDone=True
exit do
else
RST.MoveNext
Loop

'The whole record in the table are searched and

If Not AlreadyDone then
With rstRecordSet
'.AddNew
.Fields("FCUIPAddress") = Text1.Text
.Fields("McastIPAddressA") = Text2.Text
.Fields("McastIPAddressB") = Text3.Text
.Update
End With
End If

This article has been dead for over six months. Start a new discussion instead.