Table object not deleting "consistently"

[GraeagleBill]

In the InitLog function within the general module below, the "DoCmd.DeleteObject" executes successfully, but the "CREATE TABLE" statement that follows fails because the table still exists. If I Reset and close the DB and then open the DB and re-run to cursor the TextLog Sub everything works as expected. Now, as it is, I could solve the issue if I simply emptied the table, keeping it permanently in the DB, I wouldn't have to delete and re-create at all, which I'll do in any event here but still want to know what the problem is.

Option Compare Database
Option Explicit
Dim rsLog As DAO.Recordset


Public Sub TestLog()
Dim I As Integer
Dim intRecCount As Integer


On Error GoTo ErrorHandler


If InitLog("tblLog") <> 0 Then
    MsgBox "tblLog failed to initialize."
    Exit Sub
End If
WriteLog ("EventLog") 'put the field name as first record.


For I = 1 To 10       'Write some test records
WriteLog ("Test Line Number " & I)
Next I


CloseLog     'Close the DAO Recordset


'Now add the old log history to what we have now.
DoCmd.TransferText TableName:="tblLog", Filename:="c:\SDMA\PDFActivities\Log.txt", HasFieldNames:=True


'Open the DAO Rececordset again
Set rsLog = DBEngine(0)(0).OpenRecordset("tblLog")


intRecCount = rsLog.RecordCount


If intRecCount > 100 Then
rsLog.MoveLast
    For I = 1 To intRecCount - 100
        rsLog.Delete
        rsLog.MoveLast
    Next I
End If


CloseLog


NormalExit: Exit Sub


ErrorHandler:   MsgBox "Testing error " & Err.Number & "  " & Err.Description
                GoTo NormalExit


End Sub


Public Function InitLog(tblName As String) As Integer
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  Create the new table and bind it to the DAO Recordset rsLog.  The logging action
'  will record the actions into the DAO Recordset.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*


On Error Resume Next
DoCmd.DeleteObject acTable, tblName        'Get rid of any old log table
On Error GoTo ErrorHandler


CurrentDb.Execute "CREATE TABLE " & tblName & " (EventLog Text)"    'Create a new log table
Set rsLog = DBEngine(0)(0).OpenRecordset(tblName)         'Bind new log to DAO REcordset


InitLog = 0


NormalExit: Exit Function


ErrorHandler:   InitLog = Err.Number
                MsgBox "Initialization error " & Err.Number & "  " & Err.Description
                GoTo NormalExit
End Function


Public Function WriteLog(strEvent As String)
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  Add event to the log
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
rsLog.AddNew
rsLog!EventLog = strEvent
rsLog.Update
End Function




Public Function CloseLog()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  Close the Recordset and append the log's history file.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
rsLog.Close
Set rsLog = Nothing
End Function

[madpiet]

If the structure remains constant, then don't drop / delete the table, just delete all the records in it.

Instead of that whole DoCmd.DeleteObject ... stuff, why not just basically truncate the table?

CurrentDB.Execute "DELETE * FROM [" & sTableName "];", dbFailOnError

then you don't need to recreate anything.

[GraeagleBill]

Yes, like I wrote in my OP.

Now, as it is, I could solve the issue if I simply emptied the table, keeping it permanently in the DB, I wouldn't have to delete and re-create at all, which I'll do in any event here but still want to know what the problem is.

That is what I've done.

[Welshgasman]

Perhaps add a DoEvents?, as I initially thought your create code had not worked, as I could not see the new table.

However, creating the table once and then deleting seems better to me, though I know that will create some bloat. Shame MS did not include Foxpro's Zap command into Access when they bought it. The just nicked the fast method that Foxpro had. It was called Rushmore.

[GraeagleBill]

As I posted in #3, I've abandoned the delete and re-create of the table, instead simply emptying the permanent table and filling anew as needed.

Notice within the code posted in #1 that I trim the tblLog to 100 records. WHAT YOU'RE NOT SEEING is the full coarse of processing where once the session completes its logging that I append import the Log.txt file into the table, trim to 100 records and export the updated table back to disc. With each successive session, I always end up with a 100 record Log written to disc. Each session typically logs about 20 to 25 events.

If intRecCount > 100 Then
rsLog.MoveLast
    For I = 1 To intRecCount - 100
        rsLog.Delete
        rsLog.MoveLast
    Next I
End If

[Welshgasman]

You still have to consider bloat?

[GraeagleBill]

You're a lot smarter than I am, so I must be missing something that you're seeing. Where's the bloat going to come from? Each session trims the log to 100 records before updating the Log.txt file and each session isn't going to ultimately create more than probably 35 or 40 log records. (Bloat to me means a DB is growing in size to a point of unacceptability in many regards, i.e., size, performance, etc.)

[madpiet]

Bloat. So what. Run Compact & Repair every month or so. (or when the database doesn't have a lot of records but the database size seems way larger than it should be)

[GraeagleBill]

Automation apps running at 2AM each day on a client computer need to be given every consideration given the assumption that no client hands-on will be available, so potential bloating needs to be given its due. In this case, any significant bloating has the potential of adding complications to the automated DB backups.

[moke123]

What about using a Temp or Side database?