Update after import is not working (1 Viewer)

ria_arora · Apr 4, 2011

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.

Code:

            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 · Apr 4, 2011

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 · Apr 5, 2011

After adding DoEvents my form is not getting opened.

Regards
Ria

ria_arora · Apr 5, 2011

Don't know what happened.

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

Code:

            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)

Update after import is not working (1 Viewer)

ria_arora

Registered User.

DCrake

Remembered

ria_arora

Registered User.

ria_arora

Registered User.

Similar threads

Users who are viewing this thread