Update Statement is not working through VBA

[ria.arora]

Hi All,

I’m having problem in updating the Table using VBA. I have imported the data from Excel and immediately updating the Reporting Month in imported table. This update statement sometime works and sometime does not. Quite weird :banghead:… Below is the VBA code I’m using in MS Access 2003. OS – Windows XP.

objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
tsWeeklyMIByProd, Input_Dir & "\" & InputFileName2, True, File1Wks1 & "!"
strUpdateSQL = "UPDATE " & tsWeeklyMIByProd & " SET ReportingMonth = '" & txtReportingMonth & "' "
gsPnPDatabase.Execute (strUpdateSQL)

objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
tsOptionsByProd, Input_Dir & "\" & InputFileName1, True, File1Wks1 & "!"
strUpdateSQL = "UPDATE " & tsOptionsByProd & " SET ReportingMonth = '" & txtReportingMonth & "' "
gsPnPDatabase.Execute (strUpdateSQL)

I'm updating mutiple update statments and tracing the number of records updated using below code.

lngRowsUpdatedTbl1 = lngRowsUpdatedTbl1 + gsPnPDatabase.RecordsAffected

but when I display on the screen number of records affected it shows 0 records affected. Any idea what's wrong over here

Any idea what are these problems and how to resolved this?

Thanks
Ria

 

[MarkK]

I believe that the DAO.Database.Execute method does not, by default, re-raise errors encounter by the SQL processor when executing a SQL statement. I recommend adding the dbFailOnError option when using DAO.Database.Execute, and enable a VBA error handler to get more information about what failed and why. Consider the following code...

sub test1674
on error goto handler
  currentdb.execute "UPDATE tTable SET ReportingMonth = 12", [B][COLOR="DarkRed"]dbFailOnError[/COLOR][/B]
  exit sub
handler:
  msgbox err & " " & err.description
end sub

See if that reveals more information about why a SQL statement may fail.

hth
Mark

 

[ria.arora]

Thanks Mark. I have tried this already but did not work.

When I run the code in debug mode it works but in normal mode it does not. Any other suggetion to force exceution and to make sure update statement is working fine.

Thanks
Ria

 

[ria.arora]

Just to clearify. My VBA is in different database and actual data is in different database.

I don't think this should cause the problem as I'm importing / selecting / inserting and doing other activities in this database only and all those are working fine.

 

[MarkK]

Can you elaborate on, "it did not work?" That's not enough information to troubleshoot any further.

 

[ria.arora]

Hi Mark,

I mean records are not getting updated. Below is the code:

objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
tsWeeklyMIByProd, Input_Dir & "\" & InputFileName2, True, File1Wks1 & "!"
strUpdateSQL = "UPDATE " & tsWeeklyMIByProd & " SET ReportingMonth = '" & txtReportingMonth & "' "
gsPnPDatabase.Execute (strUpdateSQL), dbFailOnError
 
objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
tsOptionsByProd, Input_Dir & "\" & InputFileName1, True, File1Wks1 & "!"
strUpdateSQL = "UPDATE " & tsOptionsByProd & " SET ReportingMonth = '" & txtReportingMonth & "' "
gsPnPDatabase.Execute (strUpdateSQL), dbFailOnError
 
ExitHandler:
DoCmd.Hourglass (False)
Exit Sub
ErrHandler:
MsgBox "Error detected, error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
lblStatus.Caption = Err.Description
DoCmd.Hourglass (False)
varStatus = SysCmd(acSysCmdClearStatus)
Resume ExitHandler
End Sub

 

[MarkK]

But it's the SQL that fails, right? Not the VBA. So what matters is the table names, and the datatype of 'ReportingMonth' and so on, and your code doesn't show that.

 

[ria.arora]

Thanks for the reply. But this is not table names or datatype issue. Because if I run the same code in debug mode it works.

I suspect data is not imported while excuting update SQLs. Cause I'm importing the excel file in another database which is taking some time to refresh in other database and immediately excuting multiple update SQLs on those tables.

I think I should check number of records in Excel and compare with the table. If total number records do not tie then wait for few seconds and check total number of records again.

Is there anyway to check number of records in the Excel file?
What is the best way / code to put wait for few seconds?

Regards

 

[jdraw]

What exactly are you trying to do? It sounds that you are wanting to Update a table while that table's data is being imported.

Access is a single thread, not multi thread and can't be doing "several things at once".

Import the Excel data into a Table.
Check that the Table contents (format and number of records match your expectations)
Then do your Update.

Is there anyway to check number of records in the Excel file?

?? How could you be doing/trying to do Updates without knowing the records involved?

How do you know the Update isn't working?

And as lagbolt said, show us the table structures with names and datatypes.

 

[MarkK]

Thx jdraw.

 

[ria.arora]

Please find attached here the structure.

Is there anyway to check if prior statement / function is completed before moving to next step e.g. If I'm importing the data how to make sure that data is imported before executing the next statement e.g. INSERT / UPDATE.