Writing to an empty field in a record that was imported (1 Viewer)

[daievans]

Am busy importing text files and its working OK, save for my attempt to write the filename to each record - its a bit of an after-thought, I suppose

The challenge I'm having is wrting to the field, only if it is empty. That prevents the code from overwrting every record with teh filename of its most recent importee .... Anyone got any insight on this? Am trying to do it with an SQL UPDATE ... SET ...WHERE .... and it's not working .... :banghead:

Sub txtfileimport()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
Dim strImpSpec As String
Dim dbs As DAO.Database
Dim strSQL As String
Dim strFileNametoTable As String
Dim dtLastModified As Date
Dim strProcessTimeDate As String
strPath = "G:\ACT SJF files\upd\"
strTable = "UPD"
strImpSpec = "UPD2"
strFile = Dir(strPath & "*.txt")
blnHasFieldNames = True
Set dbs = CurrentDb
 
Do While Len(strFile) > 0
      strPathFile = strPath & strFile
      dtLastModified = FileDateTime(strPathFile)
      strFilesize = FileLen(strPathFile)
      dtSystemtime = Time
      dtSystemdate = Date
      strProcessTimeDate = dtSystemdate & " " & dtSystemtime
      
        strSQL = "INSERT INTO Vault ([Processed_File],[Filedate],[File Size],[Process Date and time]) " & _
            "VALUES ( '" & strFile & "' ,'" & dtLastModified & "','" & strFilesize & "','" & strProcessTimeDate & "')"
        
        strFileNametoTable = "UPDATE  '" & strTable & "' .[Processed_File]" & " " & _
                             "SET  '" & strTable & "' .[Processed_File] = '" & strFile & "'" & " " & _
                             "WHERE '" & strTable & "'.[Processed_File] = '' ;"
        
        
        dbs.Execute strSQL
        
      
      
        DoCmd.TransferText acImportDelim, strImpSpec, strTable, strPathFile, blnHasFieldNames
        
        dbs.Execute strFileNametoTable

      
' Uncomment out the next code step if you want to delete the
' text file after it's been imported
'       Kill strPathFile
      strFile = Dir()
Loop
dbs.Close
End Sub

 

[MarkK]

You forgot to describe any of the symptoms of the not-workingness.

 

[daievans]

You forgot to describe any of the symptoms of the not-workingness.

Ah! Yes - that would be a good idea

I must have thought you were all Veterinary surgeons who can diagnose without any specified symptoms .....

Well, the symptom is that it refuses to write the filename to the table UPD, although it exports the data file. For some reason the UPDATE SET WHERE field [processed_file] is empty does want to store the current filename variable .....

I had it working earlier, HOWEVER, the [processed_file] for the whole table was being constantly over-written and I would end up with a table full of the last filename to be processed ....

 

[MarkK]

Yeah, that UPDATE SQL is broken. For the moment, why don't you not parameterize the table name and see what you get. Like, just looking at this . .

"UPDATE  '" & strTable & "' .[Processed_File]" & " "

. . . that's going to evaluate to . . .

UPDATE 'UPD' .[Processed_File]

. . . and that's going to fail. Get that query working in the design grid first and then chop it up for your purposes, but it should be . . .

UPDATE <tablename> 
SET <fieldname> = <value>
WHERE <expression>

. . . and you've got . . .

UPDATE '<tablename>' .<fieldname>

. . . and that's probably step 1.
Cheers,