Update after import is not working

[ria_arora]

I'm trying to update one of the field after loading / importing the data in the table. But somehow that field is not getting updated.

Below is the code I have used for the same.

            Set objAccess = CreateObject("Access.Application")
            objAccess.OpenCurrentDatabase strCnPDatabaseName, , strCnPDatabasePassword
        
            objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
            strTableName, Input_Dir & "\" & InputFileName1, True, File1Wks1 & "!"
 
            strUpdateSQL = "UPDATE " & strTableName & " SET Last_Updated = '" & gsCurrentDateTime & "' "
            gsCnPDatabase.Execute (strUpdateSQL)

If I run the code in debug mode then column is getting updated but if I run without debug mode then it's not getting updated

I suspact it might takes sometime to refresh the data after export that's why records are not getting update... Any solution?

Regards
Ria

 

[DCrake]

Insert a DoEvents between the two actions to render Access to wait for one action to be completed before continuing with the next.

 

[ria_arora]

After adding DoEvents my form is not getting opened.

Regards
Ria

 

[ria_arora]

Don't know what happened.

I created new form and in that DoEvent is working but update is still not working

            Set objAccess = CreateObject("Access.Application")
            objAccess.OpenCurrentDatabase strCnPDatabaseName, , strCnPDatabasePassword
        
            objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
            strTableName, Input_Dir & "\" & InputFileName1, True, File1Wks1 & "!"
 
            strUpdateSQL = "UPDATE " & strTableName & " SET Last_Updated = '" & gsCurrentDateTime & "' "
            gsCnPDatabase.Execute (strUpdateSQL)