You want to add OCR text to existing records. TransferText would create new record. So this requires opening and manipulating a text file object in VBA to update field of relevant record.
To update all the existing records, will probably want to open a recordset of the table, loop through the recordset to read the file name, open the file, read text, update field (this is a crude example of pseudocode - writing out the steps needed to be coded).
Then you might want a procedure to get the OCR text when a new record is created. So you could have 3 procedures. One procedure that has the code to do the actual manipulation of the text file and then two procedures that call the first one.
The following code would be in a general module:
Code:
Function GetOCR(strFile As String) As Variant
Dim objFSO As Object
Dim objTextStream
If Dir(strFile) <> "" Then
Set objFSO = CreateObject("Scripting.FileSystemObject")'Open the text file
Set objTextStream = objFSO.OpenTextFile(strFile, 1)
'return the text string
GetOCR = objTextStream.ReadAll
'Close the file and clean up
objTextStream.Close
Set objTextStream = Nothing
Set objFSO = Nothing
Else
GetOCR = Null 'or return a string like None
End If
End Function
Then that function procedure can be called in other procedures. Here is code to populate the existing records. Run it once and maybe never again.
Code:
Sub UpdateRecordsOCR()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT ID, Textpath, OCRText FROM Writings WHERE NOT Textpath Is Null")
While Not rs.EOF
rs.Edit
rs!OCRText = GetOCR(rs!Textpath)
rs.Update
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
End Sub
All tested and works for me.
Now you can call the function whenever a new record is created and the filepath is available to pass to the function. Perhaps in a button Click event or Textpath textbox AfterUpdate. Simple example referencing textbox controls that have been named with a tbx prefix:
If Not IsNull(Me.tbxTextpath) Then Me.tbxOCRText = GetOCR(Me.tbxTextpath)