Curious results show in table after running process

[THWatson]

A command button on a form has the following code behind it

Dim startTime As Double
  Dim endTime As Double

    Call MsgBox("The Consecutive Months table needs updating." _
    & vbCrLf & "           This takes 10 seconds or less." _
     , vbExclamation, "Updating Consecutive Months table")
                                                           
  startTime = Timer
  Call delStreakData3
  'clear out the data
  'Call PrintStreaks(#1/1/2005#, i)
  Call PrintStreaks3(DMin("MeetingDate", "tblAttendanceARCHIVE"), 6)
  endTime = Timer
  MsgBox "Update completed. Time to update = " & endTime - startTime

First the following code deletes existing date in the table

Public Sub delStreakData3()
  Dim strSql As String
  strSql = "Delete * from tblStreakDataARCHIVE"
  CurrentDb.Execute strSql
End Sub

and then repopulates the table with the following code

Public Sub insertStreakData3(memID As Long, startStreak As Date, endDate As Date, StreakLength As Integer)
  Dim strSql As String
  Dim strValues As String
  Dim strStart As String
  Dim strEnd As String
  
  strStart = getSQLDate3(startStreak)
  strEnd = getSQLDate3(endDate)
  strValues = "(" & memID & "," & strStart & "," & strEnd & "," & StreakLength & ")"
  'Debug.Print strValues
  strSql = "Insert into tblStreakDataARCHIVE (memberID_FK,streakStartDate,streakEndDate,streakLength) values " & strValues
  'Debug.Print strSql
   CurrentDb.Execute strSql
End Sub

When I look at the table after the processes run, each of the fields show #Deleted and yet the data is there. If I run another command button from the form, and pull data from the table, all the data shows. Also, if I open the table, then switch the table to Design view and then back, all of the data shows properly.

Why does #Deleted show in the table?

Tom

 

[genomon]

What happens when you add Me.Requery at the end of each process?

Beir bua agus beannacht!

 

[THWatson]

genomon
The form is unbound. The functions are Public functions in a module. So adding "Me.Requery" on the unbound form has no effect.

There's another piece of discovery on this issue. It seems to be an Access 2007 issue. I run the same processes using Access 2003 and don't have the problem.

Things are curious sometimes.

Tom

 

[genomon]

Try wrapping the DML statements in a BEGIN/COMMIT TRANS. Could be they are being treated as implicit transactions and not getting committed when we expect?

Beir bua agus beannacht!

 

[PHV]

What happens if you replace all:
CurrentDb.Execute strSql
with this ?
DoCmd.RunSQL strSQL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

genomom and PHV
Thanks for both suggestions. I am away from the computer that runs Access 2007 for a few days, but when I get back to it I will try both suggestions.

As I have indicated the problem doesn't occur on the computer that runs Access 2003...only Access 2007. Although I'm not clear why this is the case because both save the file in Access 2003 format.

Also, I wonder if I made it clear in my original post that there is nothing on the form that indicates any problem whatsoever. When I run something that uses the updated data, it works just fine. It's ONLY when I examine the data in the table itself that I see the "#Deleted" in each field. But if I flip the table into Design View and then back again, all the data is there. So it's an "appearance" thing, and not a "data missing" thing.

Anyway, I will post back in due course.

Tom

 

[genomon]

So it's an "appearance" thing, and not a "data missing" thing.

Try both suggestions, but if the form is open & has a lock on the table you will need to close it before manually opening the table in data view. A way around this may be to Dim dbYourDB As DAO.Database, set it to CurrentDB, then set the dbYourDB object to Nothing at the end of each operation. Can't reproduce it here on Acc2K vs Acc 2K7. Personally I like the DoCmd approach thatPHV suggested best. Good luck!

Ni neart go cur le cheile.

 

[THWatson]

Thanks, genomon. Will do.

Tom