Why can't I delete from my table?

[benlinkknilneb]

Hi all,

I've got an access database that produces a report. It's set by a scheduled task to run in "headless" mode via a service account, and it's set to auto-start running my code when I kick off the database.

This all works fine if I manually review it. However, when the scheduled task kicks it off, I fail with error 3086: Could not delete from specified tables.

This table is a local Access table that I use to temporarily hold things while I'm working through the report creation process. It's not attached to anything. All I'm doing is "DELETE * FROM TEMPTable;" via a RunSQL command. As I said before, if I run this code manually, it works fine.

I noticed that at the same time the Access process gets automatically started by the task, the System Event log shows a 1003: "The Software Protection service has completed licensing status check." Googling so far hasn't turned up anything interesting on this other than people whose Office installations won't start up at all... but that doesn't match my problem.

However, I have other access databases on the same server that are scheduled in the same way (albeit at different times) and they run without incident.

Can anyone see what I'm missing?

Ben

 

[trevil620]

So, this scheduled task is on a server, and you have Office (or at least Access) installed ther, correct?

First guess is check the file to see if somehow it is set as Read-Only.
Second guess is does the account under which the task runs have permissions to the file and folder?
Does any of the ones that work also perform updates or deletes? If not, create a dummy table with a few rows and try the delete. That may narrow down where to look.

The following is one link:

http://office.microsoft.com/en-us/access-help/HV080760256.aspx

When all else fails, manipulate the data.

 

[benlinkknilneb]

That's the mystery... If I log in and run the vba manually on this report, it works fine. But if I let it kick off from the scheduled task (running as the same user I just logged in with), it fails.

Ben
Hear the beauty...
#> /proc/kcore>/dev/audio

 

[dhookom]

Are you setting warnings off?
What is your exact code?

Have you tried something like:

Dim db as DAO.Database
Dim strSQL as String
Set db = Currentdb
strSQL = "DELETE * FROM TEMPTable" 
db.Execute strSQL , dbFailOnError
Set db = Nothing

Duane
Hook'D on Access
MS Access MVP

 

[benlinkknilneb]

Warnings are indeed off.

This is what it does:

docmd.Setwarnings False
docmd.RunSQL "DELETE * FROM TEMPTable;"
docmd.Setwarnings True

I notice that you use the db.Execute rather than docmd.RunSQL... is that just a personal preference?

Ben
Hear the beauty...
#> /proc/kcore>/dev/audio

 

[dhookom]

I think db.execute is more efficient. Did you try it to see if it makes a difference?

Does your table have any related tables? Is your table bound to any open forms?

Duane
Hook'D on Access
MS Access MVP

 

[trevil620]

Where is your code to delete located... I see you are running a report? Does that report use that table?


When all else fails, manipulate the data.

 

[benlinkknilneb]

Hey, sorry for disappearing... got busy with other issues!

trevil620: the code to delete is in a module inside the .accdb file. The report isn't an Access report, but a spreadsheet that links to the .accdb via MS Query that does use that table but calls it much later in the day. I have confirmed that it is not open when the module fails.

dhookom: I am not using any access forms and no tables are related according to the relationships pane. I have not yet attempted a run using db.execute, but I'm not particularly sanguine about its effectiveness anyway; I have other .accdbs that work the exact same way as this on the same machine but do not fail.


I also noticed that sometimes I get an error 3027 instead of the 3086.

Ben
Hear the beauty...
#> /proc/kcore>/dev/audio

 

[benlinkknilneb]

Update: I just found a page describing a permissions issue that caused the 3027 error. I noticed that the service account used to call the .accdb automatically was not explicitly granted permissions to the folder.

I've given my service account full permissions to the directory and I'm rerunning the report...

Ben
Hear the beauty...
#> /proc/kcore>/dev/audio

 

[benlinkknilneb]

...aaaaaand 3086 again.

So the scenario here is that I have to run the same report for multiple criteria, which I do in a loop. What I do to keep my code readable is write the data via append queries to a local temporary table, which gets cleared (DELETE * FROM x after each run through the loop.

I'm failing on the very first run through the loop (I do the "delete * from x" operation before populating the table the first time to ensure no data got left over from a prior run), before any of the other processing occurs. This is practically the first thing that happens in the module.

If I run the code in the debugger, or even by manually kicking off the module, it works fine. It's ONLY in the case that my task scheduler calls the report that it fails, which runs under the same Windows account that I manually tested it under.

Here's the sub up to the point of failure:

Public Sub DeleteTempTables()
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM TEMP_ApplicationPerWeek;"

TEMP_ApplicationPerWeek is an Access-created table that lives in this same .accdb file. It is not joined to anything else or in use by anyone else at runtime.

Error 3086: Could not delete from specified tables.

I'm completely flabbergasted. Anyone have any further ideas?

Ben
Hear the beauty...
#> /proc/kcore>/dev/audio

 

[dhookom]

This might be related to timing of the events. Can you share more of your code?

Duane
Hook'D on Access
MS Access MVP

 

[benlinkknilneb]

Here's the code leading up to the point of failure. First I check the data source to be sure it's up to date. Then I grab the list of "versions" of the report I run (same report, different data sources) and start a while loop where we actually run each of them. Other stuff happens after this code, but since I never get through this one time, I can't imagine how that would affect the situation.

    If DateAdd("d", -1, ReportDate) > DateValue(checktable("info")) Then
        writelogentry 5, "WeeklyTicketTrends is unable to run because the info table is not sufficiently up to date.", "WeeklyTicketTrends", , "RunReport"
        Application.Quit
    End If
    
    Set PackageList = CurrentDb.OpenRecordset("SELECT DISTINCT PackageName FROM StoredData_Packages;")
    
    If PackageList.RecordCount < 1 Then
        writelogentry 5, "No packages found for this report! Cannot proceed!", "WeeklyTicketTrends", , "RunReport"
        Application.Quit
    End If
    
    While Not PackageList.EOF
        DeleteTempTables

and then DeleteTempTables contains this (note, I always die on the first delete):

Public Sub DeleteTempTables()
    
    DoCmd.SetWarnings False
    
    DoCmd.RunSQL "DELETE * FROM TEMP_ApplicationPerWeek;"
    DoCmd.RunSQL "DELETE * FROM TEMP_AverageTimeToClose;"
    DoCmd.RunSQL "DELETE * FROM TEMP_NumberOfTicketsPerWeek;"
    DoCmd.RunSQL "DELETE * FROM TEMP_QCvsNV;"
    DoCmd.RunSQL "DELETE * FROM TEMP_RootCausePerWeek;"
    DoCmd.RunSQL "DELETE * FROM TEMP_SLAMissesPerWeek;"
    
    DoCmd.SetWarnings True
    
    writelogentry 2, "Temp Tables Deleted.", "WeeklyTicketTrends", , "DeleteTempTables"
End Sub

Ben
Hear the beauty...
#> /proc/kcore>/dev/audio

 

[trevil620]

In your query, is 'StoredData_Packages' a table, or is that a query?
In 'DeleteTempTables' why not put an error trap, then write the error to a log so you will know exactly which table is failing? I.e. the following code:

Public Sub DeleteTempTables()
    Dim strTable    As String

    On Error GoTo Error_Trap
    DoCmd.SetWarnings False
    strTable = "TEMP_ApplicationPerWeek"
    DoCmd.RunSQL "DELETE * FROM TEMP_ApplicationPerWeek;"
    strTable = "TEMP_AverageTimeToClose"
    DoCmd.RunSQL "DELETE * FROM TEMP_AverageTimeToClose;"
    strTable = "TEMP_NumberOfTicketsPerWeek"
    DoCmd.RunSQL "DELETE * FROM TEMP_NumberOfTicketsPerWeek;"
    strTable = "TEMP_QCvsNV"
    DoCmd.RunSQL "DELETE * FROM TEMP_QCvsNV;"
    strTable = "TEMP_RootCausePerWeek"
    DoCmd.RunSQL "DELETE * FROM TEMP_RootCausePerWeek;"
    strTable = "TEMP_SLAMissesPerWeek"
    DoCmd.RunSQL "DELETE * FROM TEMP_SLAMissesPerWeek;"
    
    DoCmd.SetWarnings True
    
    writelogentry 2, "Temp Tables Deleted.", "WeeklyTicketTrends", , "DeleteTempTables"
    Exit Sub
    
Error_Trap:
    writelogentry 2, "Error: " & Err.Number & vbTab & Err.Description & vbCrLf & _
        "Trying to delete table : " & strTable
    DoCmd.SetWarnings True
    Exit Sub
End Sub

When all else fails, manipulate the data.

 

[benlinkknilneb]

StoredData_Packages is a local table.

I removed the logging from DeleteTempTables() for brevity's sake. I can see that it never gets past the first table deletion.

Incidentally, all the things being deleted from in that sub are individual tables and have no relationships to anything else around them (ie no joined tables/queries).

Ben
Hear the beauty...
#> /proc/kcore>/dev/audio

 

[trevil620]

Having just looked at your posts again, I see "So the scenario here is that I have to run the same report for multiple criteria, which I do in a loop." Could we see the code in that loop? Specifically, how you are running the report, and other code in that loop...

When all else fails, manipulate the data.

 

[benlinkknilneb]

Here's the whole function. "RunReportQuery" is a wrapper that passes the package name and report date to an append query of the name provided in the call. I call DeleteTempTables() twice, I know... since I auto-compact on close I wanted the local tables to be as small as possible for storage. Generatereport opens an excel spreadsheet template that's pointed at this database, refreshes all data connections, and saves as a different file name.

Public Function RunReport(Optional ByVal ReportDate As Date, Optional ByVal TESTRUN As Boolean) As Boolean
    On Error GoTo ReportFail
    Dim PackageList As Recordset
    Dim PackageID As String
    Dim starttime As Date
    
    starttime = Now
    
    Application.SetOption "Auto Compact", True
    
    If Year(ReportDate) < 2000 Then ReportDate = Date
    If IsMissing(TESTRUN) Then TESTRUN = False
    
    If DateAdd("d", -1, ReportDate) > DateValue(checktable("info")) Then
        writelogentry 5, "WeeklyTicketTrends is unable to run because the info table is not sufficiently up to date.", "WeeklyTicketTrends", , "RunReport"
        Application.Quit
    End If
    
    Set PackageList = CurrentDb.OpenRecordset("SELECT DISTINCT PackageName FROM StoredData_Packages;")
    
    If PackageList.RecordCount < 1 Then
        writelogentry 5, "No packages found for this report! Cannot proceed!", "WeeklyTicketTrends", , "RunReport"
        Application.Quit
    End If
    
    While Not PackageList.EOF
        DeleteTempTables
        
        PackageID = PackageList!PackageName
        
        'Run queries
        RunReportQuery "2-ApplicationPerWeek", PackageID, ReportDate
        RunReportQuery "2-NumberOfTicketsPerWeek", PackageID, ReportDate
        RunReportQuery "2-QCvsNV", PackageID, ReportDate
        RunReportQuery "2-RootCausePerWeek", PackageID, ReportDate
        RunReportQuery "3-AverageTimeToClose", PackageID, ReportDate
        RunReportQuery "3-SLAMissesPerWeek", PackageID, ReportDate
        
        generatereport "WeeklyTicketTrends", PackageID, CurrentProject.Path & "\Template.xlsx", "WeeklyTicketTrends_" & PackageID & "_" & Format(ReportDate, "yyyymmdd") & ".xlsx", "INTRODUCTION", "A5", PackageID & " - " & ReportDate, Not TESTRUN
        
        PackageList.MoveNext
        DeleteTempTables
    Wend
    RunReport = True
    writelogentry 6, "Weekly Ticket Trends report completed successfully. Processing time = " & DateDiff("s", starttime, Now) & " seconds.", "WeeklyTicketTrends", , "RunReport"
    Exit Function
ReportFail:
    RunReport = False
End Function

Ben
Hear the beauty...
#> /proc/kcore>/dev/audio