Recordset comparison

[315hughes]

Evening all i am importing a text file into my database the text file is just a number and each new number is on a new line. The method i am using to get the values from the text file is

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile(Application.CurrentProject.Path & "\Import.txt", 1)
 
    Do Until objFile.AtEndOfStream
        l = objFile.ReadLine
Loop
objFile.Close

What i would like to do with each value is to check this against the values of a column in a table. The table is called tblProduct the column is ProdID. What i would like to do is if the text file values match a value in ProdID carry on to the next text file value. If it does not match i would like it to chuck a error.
I have created a recordset but it is only checking the first value my total code is as follows

    Set objCHK = CreateObject("Scripting.FileSystemObject")
    Set objFile = objCHK.OpenTextFile(Application.CurrentProject.Path & "\Import.txt", 1)
 
 
  Set db = CurrentDb
    Set r = db.OpenRecordset("tblProduct")
 
   While Not r.EOF
 
   o = r!ProdID
 
   Debug.Print o
   r.MoveNext
    Wend
 
    Do Until objFile.AtEndOfStream
        c = objFile.ReadLine
 
    Debug.Print c
    Debug.Print o
 
     If c = o Then
     Exit Sub
     Else
 
     End If
 
 
Loop
objFile.Close

[NeoPa]

May I suggest an alternative approach? If this doesn't suit then say so. I'm not saying what you're trying to do can't be done by any means, but it just seems such a convoluted approach to the problem.

Why not import the file into a basic, unrestricted, table. Clear the contents of the table first. This table is here for this specific task - just to hold imported data so that checks can be run on it.

The next step is to run checking queries on the data. Match it up to your existing table and show all those records where there is no match. A list of the failures in one go is probably better than a number of error messages.

When you're happy with your data then process it. Generally use an APPEND query to another table.

As a general rule, when working with any data in an RDBMS, let the RDBMS manage the data.

[ADezii]

There is a very simple, basic solution to your problem assuming that the Number of Records in tblProduct is exactly equal to the Number of Lines in Import.txt. I ran the Code against 30 Records in tblProduct sequentially numbered (1-30), and 30 Lines in Import.txt with mismatches on the Odd Lines (1,3,5,7,9, etc.). Here is the Code with the generated Results. See what you think:

Dim db As DAO.Database
Dim r As DAO.Recordset
Dim TextLine
Dim o
Dim intLineNum As Integer
 
Set db = CurrentDb
Set r = db.OpenRecordset("tblProduct")
 
r.MoveFirst     'Must be on Record# 1
 
Open CurrentProject.Path & "\Import.txt" For Input As #1
 
Do While Not EOF(1)
  intLineNum = intLineNum + 1
  o = r!ProdID
  Line Input #1, TextLine       'Read Line into Variable
    If Val(o) <> Val(TextLine) Then Debug.Print "Mismatch on Line " & intLineNum & _
                                                " ProdID: " & o
      r.MoveNext
Loop
 
 
r.Close
Set r = Nothing
 
Close #1

OUTPUT:

Mismatch on Line 1 ProdID: 1 Actual: 11
Mismatch on Line 3 ProdID: 3 Actual: 33
Mismatch on Line 5 ProdID: 5 Actual: 55
Mismatch on Line 7 ProdID: 7 Actual: 77
Mismatch on Line 9 ProdID: 9 Actual: 99
Mismatch on Line 11 ProdID: 11 Actual: 111
Mismatch on Line 13 ProdID: 13 Actual: 133
Mismatch on Line 15 ProdID: 15 Actual: 55
Mismatch on Line 17 ProdID: 17 Actual: 177
Mismatch on Line 19 ProdID: 19 Actual: 199
Mismatch on Line 21 ProdID: 21 Actual: 211
Mismatch on Line 23 ProdID: 23 Actual: 233
Mismatch on Line 25 ProdID: 25 Actual: 55
Mismatch on Line 27 ProdID: 27 Actual: 277
Mismatch on Line 29 ProdID: 29 Actual: 299