Update Text field from text file

[mkatz13]

I need to put the contents of a text file into a memo field text box control on a form. I could cut and paste , but would like to do it thru automation. I assume that I would do some sort of DDE on maybe the on current property, but I definitely need help.

The text file changes as it is imported from a device via the com port, and then as a new record is created, we need to place the contents of the text file into the field and then empty the text file.


Thanks

Mike Katz

 

[smedvid]

I would import the text file contents into a temprary Access database table as a first step. Perhaps create an Import Template within Access. Then use Code to Import Text..

Or, open the text file via Open Command...

Here is some psuedo code from using Open Command... htwh,
...
Open APP_PATH & "dir_list_mdb.lst" For Input As #1 'open the directory list file and load table
Do While Not EOF(1)
lcAction_Text = "Ready to Compact."
llcompactflag = True
Input #1, sFilePath
'strip the base path to save the overall number of characters
If InStr(1, sFilePath, lcBase_Path) <> 0 Then
sFilePath = Mid(sFilePath, Len(lcBase_Path) + 1)
End If
If InStr(1, sFilePath, &quot;'&quot <> 0 Then
sCorrectedFilePath = Replace_String(sFilePath, &quot;'&quot;, &quot;&quot
Else
sCorrectedFilePath = sFilePath
End If
'Check Exception List
If llcompactflag = True Then
If Chk_Exception(lcBase_Path, sCorrectedFilePath) = True Then
'File in Exception List
lcAction_Text = &quot;File Excluded from Compact. File is listed in exception_list table.&quot;
llcompactflag = False
End If
End If
'Check Last Compact Date
If llcompactflag = True Then
If Chk_Compact(lcBase_Path, sCorrectedFilePath, lnComp_Gen) = True Then
'File in Exception List
lcAction_Text = &quot;File Excluded from Compact. File was recently compacted per Action_Log table.&quot;
llcompactflag = False
End If
End If
'Check Modify Date
Set oFSO = CreateObject(&quot;Scripting.FileSystemObject&quot
Set oFileOrig = oFSO.GetFile(lcBase_Path & sFilePath)
dOrigFileSize = oFileOrig.Size
dtOrigFileDate = oFileOrig.DateLastModified
If llcompactflag = True Then
If DateDiff(&quot;d&quot;, oFileOrig.DateLastModified, Now()) > lnMod_Gen Then
'Ok Compact File
Else
lcAction_Text = &quot;File Excluded from Compact. File was recently modified.&quot;
llcompactflag = False
End If
End If
'Check File Attributes
dOrigFileAttributes = oFileOrig.Attributes
If llcompactflag = True Then
If dOrigFileAttributes And 1 Then
'Read-Only Set
lcAction_Text = &quot;File Excluded from Compact. File is Read-Only.&quot;
llcompactflag = False
End If
End If

'Check ldb (lock) file - Not sure if this should be here or just before Compact Action?
If llcompactflag = True Then
If oFSO.FileExists(lcBase_Path & Left(sFilePath, Len(sFilePath) - 3) & &quot;ldb&quot Then
'Check Date-Stamp/Attributes.
Set oFileLdb = oFSO.GetFile(lcBase_Path & Left(sFilePath, Len((sFilePath)) - 3) & &quot;ldb&quot
If DateDiff(&quot;d&quot;, oFileLdb.DateLastModified, Now()) > lnMod_Gen Then
'Delete ldb File and Continue with Compact!
oFileLdbAttributes = oFileLdb.Attributes 'check if ldb file is read-only.
If oFileLdbAttributes And 1 Then
'Read-Only Set
lcAction_Text = &quot;File Excluded from Compact. Lock (ldb) File Appears Old and is Read-Only Can Not Delete.&quot;
llcompactflag = False
Else
'May Need to Add Error Handler Here if Kill Fails!
Kill lcBase_Path & oFileLdb.Name 'Delete the ldb file on basepath location.
DelayTime 10 'Slight delay to insure file action completed.
End If
Else
lcAction_Text = &quot;File Excluded from Compact. File appears to be locked since a recent ldb file exists.&quot;
llcompactflag = False
End If
Else
'Ok to Compact. No .ldb file found.
End If
End If

'Check if File can be openned - Perhaps link to a System Table on a database?
'SELECT Count(MSysObjects.Id) AS CountOfId FROM MSysObjects;


'Load data into Dir_List Table
lnCountOfDIRList = lnCountOfDIRList + 1
With RsDList
.AddNew
.Fields(&quot;rec_counter&quot = lnCountOfDIRList
.Fields(&quot;basepath&quot = lcBase_Path
.Fields(&quot;filepath&quot = sFilePath
.Fields(&quot;action&quot = lcAction_Text
.Fields(&quot;actiondate&quot = Now()
.Fields(&quot;compactflag&quot = llcompactflag
.Fields(&quot;fixedfilepath&quot = sCorrectedFilePath
.Fields(&quot;filesize&quot = dOrigFileSize
.Fields(&quot;lastmodifieddate&quot = dtOrigFileDate
.Fields(&quot;fileattributes&quot = dOrigFileAttributes
.Update
End With
Loop 'end doloop not EOF(1)
Close #1




Steve Medvid
&quot;IT Consultant & Web Master&quot;
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[mkatz13]

I thank you Steve for that rapid response. The only thing is that I am only trying to update the present record's memo field with the contents of the entire text file and then want to zap the text file. If I import, then it breaks my data into fields and attempts to create a table, and that is not what I want to do here. Thanks



Mike Katz

 

[JustLJ]

Mike --

How about importing into a table where the only field is a memo field, then doing an update from that table into the one where you actually want the data? In a macro, delete previous records from inputTable, import your data into inputTable then update finalTable from inputTable.

HTH
LJ