HowTo: Move from array to Recordset based on a complicated condition

[Deadline]

Hi,
I am having an array created out of a text delimited file, whose row count I don't know yet.

Now I need to Move the array values into a table called Actual. Before moving, we should check whether it(the entire row) is already there in Actual; If present already, then MOVE those values (from the Table, not from the array)to another table called Archives, which has the same structure as Actual. If not present, then write as usual into the Actual table.

Can someone suggest a logic/code for this problem ?

I think you can do it.

Thanks in advance.

RR.

This is the code that I am using now, copies the array into the Table. Take a look at this. It is certainly interesting. This works absoultely fine. With due Thanks to Snaggs.


Private Sub Form_Load()
Dim intFile As Integer, intRow As Integer, intCol As Integer
Dim intRows As Integer
Dim strBuffer As String
Dim varArray As Variant
Dim varNames As Variant

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim strSQL As String
Dim strConn As String


ReDim varNames(0) As Variant

intFile = FreeFile

Open App.Path & "\commadelimited.txt" For Input As #intFile

intRows = 0

Do Until EOF(intFile)
'Read the line in from the file.
Line Input #intFile, strBuffer

'Pick off the columns and put then in a seperate array.
varArray = Split(strBuffer, ",&quot

'Set the size of the array to hold the data.
'Be sure that the intCol value doesn't change from row to row.
ReDim Preserve varNames(intRows)

'Array of Array concept
varNames(intRows) = varArray
intRows = intRows + 1
Loop

Close #intFile

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
strConn = strConn & " Data Source=somedatabase.mdb;"
strConn = strConn & " Persist Security Info=False"

cn.Open strConn
rs.Open "sometable", cn, adOpenKeyset, adLockOptimistic, adCmdTable

For intRow = 0 To intRows - 1
rs.AddNew
For intCol = 0 To UBound(varNames(intRow))
If intCol = 2 Or intCol = 3 Or intCol = 4 Then
rs.Fields(intCol) = NumberToDate(CStr(varNames(intRow)(intCol)))
Else
rs.Fields(intCol) = varNames(intRow)(intCol)
End If
'Debug.Print varNames(intRow)(intCol) & " - "
Next intCol
rs.Update
Next intRow
If Err.Number <> 0 Then
MsgBox &quot;Error!! ->&quot; & Err.Description
rs.CancelUpdate
End If
'Debug.Print
End Sub