Importing Text Files

[abbyanu]

Hi

I'm still learning VB, and I have some code that's giving me a headache.

table name: yeeken
Fields:
1. ProductNumber (primary field)
2. Notes (Memo field)

Each ProductNumber has a corresponding ProductNumber.txt file, which I wish to import into the [Notes] field, thus for example if the product number is ProdX, I would import the file ProdX.txt into the [Notes] field.
All the relevant .txt files are stored in one directory

========

Option Compare Database

Public Function GetPartData(FileLoc As String)
Dim FNames(), Prodno, FileRead As String
Dim StartPos, CalculatedNumber As Integer
Dim var As Variant
Dim i, j As Double
Dim db As Database
Dim Rst As DAO.Recordset

Set db = CurrentDb
Set Rst = db.OpenRecordset("yeeken", dbOpenDynaset)

If Not IsNull(var) Then
    With Application.FileSearch
        .LookIn = FileLoc
        .SearchSubFolders = False
        .FileName = "*.txt"
        If .Execute() > 0 Then
            j = .FoundFiles.Count
            For i = 1 To j
                ReDim Preserve FNames(i)
                FNames(i) = .FoundFiles(i)
            Next i
        Else
            MsgBox "There were no files found."
        End If
    End With
    StartPos = Len(FileLoc) + 2
    For i = 1 To j
        Close #1
        Open FNames(i) For Input As #1
            Input #1, FileRead

' For each [ProductNumber], its [Notes] are stored in an external text file
' called [ProductNumber].txt. The code below extracts the [ProductNumber] from
' the textfile into a string called [Prodno] e.g. if Notes textfile name is
' "ProductNumberX.txt", Prodno returns "ProductNumberX".

            CalculatedNumber = InStr(StartPos, FNames(i), ".txt")
            Prodno = Mid(FNames(i), StartPos, (CalculatedNumber - StartPos))

'How can I implement SQL or other condition below
'equivalent to UPDATE !Notes WITH [FileRead] WHERE ProductNumber = Prodno

                With Rst
                    .AddNew

!ProductNumber = Prodno

                    !Notes = FileRead
                        On Error Resume Next
                    .Update
                End With
        Close #1
    Next i
    MsgBox "All Available Product Notes updated."
End Function

===========
Thanks so much for your assistance.

AbbyAnu.

 

[kevinclark]

Could you possibly use the FindFirst / NoMatch method ?

With Rst

.FindFirst ("ProductNumber ='" & Prodno)
If .NoMatch = False Then
.Edit
.Fields("ProductNumber&quot.Value = Prodno
.Update
Else
.addNew
.Fields("ProductNumber&quot.Value = Prodno
.Update
End If
End With

 

[abbyanu]

Hi

The line

.FindFirst ("ProductNumber ='" & Prodno)

is generating an error:

Run-time erroe '3077'
Syntax error (missing operator) in expression.

Tried all variations, but in vain. Any idea what's causing the problem? It's also a bit slow.

I've also read somewhere in this forum that

seek

is faster than

FindFirst

. Can this be used instead, in my case?

Thanks for your time.

AbbyAnu.

 

[kevinclark]

You can always give Seek a try but I think you will find the problem with the above code is likely the missing '
in the following line.

.FindFirst ("ProductNumber ='" & Prodno)

try writing as:


.FindFirst ("ProductNumber ='" & Prodno & "'&quot

 

[abbyanu]

Thanks so much, Kevin

Works like Kudzu!

Peace,
AbbyAnu.