Results 1 to 7 of 7
  1. #1
    Join Date
    May 2005
    Posts
    1,191

    Unanswered: DELETE Query to SQL Server Fails

    To give you a little information on the environment in question, I have a Access 2003 FE running off a SQL Server BE via Network Login ID authenticated ODBC connection. I have a mdb file setup to (in theory) grab up-to-date data from another server across the country, filter it and do various oeprations to it, clear the data from the current SQL table and then import the new clean data in, all done automatically overnight (again, in theory).

    Everything works fine up until I try to run a delete query on the SQL table to clear the old data; here's the bit of code:
    Code:
        strTableName = "tblName"
        strSQL = "DELETE [" & strTableName & "].* FROM [" & strTableName & "];"
        If Not ExecuteDAOQuery(strSQL) Then GoTo ErrorHandler
    Code:
    Public Function ExecuteDAOQuery(strSQL As String, _
                                    Optional ByVal db As DAO.Database, _
                                    Optional blnLeaveQueryDef As Boolean = False, _
                                    Optional strQueryName As String = "") As Boolean
        
        On Error GoTo ExitProc
        
        ExecuteDAOQuery = False
        
        'instantiate
        Dim qdfX As QueryDef
        
        'initiate
        If Not Len(strQueryName) > 0 Then strQueryName = "qryTemp" & Format(Now(), "yyyymmddHhNnSs")
        
        'assign default db if null
        If (db Is Nothing) Then Set db = CurrentDb
        
        'Delete if already exists
        For Each qdfX In db.QueryDefs
            If qdfX.Name = strQueryName Then db.QueryDefs.Delete strQueryName
        Next qdfX
        
        'run query
        Set qdfX = db.CreateQueryDef(strQueryName, strSQL)
        DoCmd.SetWarnings False
        qdfX.Execute
        DoCmd.SetWarnings True
        
        'clean up
        Set qdfX = Nothing
        If Not blnLeaveQueryDef Then db.QueryDefs.Delete strQueryName
        Set db = Nothing
        
        ExecuteDAOQuery = True
        
    ExitProc:
        Exit Function
        
    End Function
    When it gets to the "qdfX.Execute" line, it tries to run the query for about 3 hours or so, and then gives up and throws up an error. Any ideas why it won't run?
    Me.Geek = True

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What happens if you simply try:
    Code:
    strSQL = "DELETE * FROM [" & strTableName & "];"
    Currentdb.Execute strSQL
    It should return an error code if something goes wrong.
    Have a nice day!

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Perhaps if you stop suppressing error messaging the system might tell you what is wrong?

    REM this: On Error GoTo ExitProc
    and this: DoCmd.SetWarnings False

    Run it again and see if the system gives you an error message.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Thanks for the suggestions!

    I decided to tweak the error handling to display the error when it occurs.

    But when I tried to run the code again, it came to this line of query, took about 42 hours to execute this single line of code, and then continued on to successfully complete the rest of the code.

    Has anyone ever seen this where sometimes this sort of thing works (although it does take far longer than it should), sometimes not? Any ideas?

    I'll try running it again sometime soon to see if I can get the error to trigger again (this time with better error catching).
    Me.Geek = True

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    42 hours?? O.O

    I've seen a report take like 30 minutes to run but nothing near 2 days !!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    Ya, the rest of my queries run just fine (even some of them that are also DELETE queries). It's just this one query that takes an exorbitantly long time to run. I know it's trying to clear a table that's ~700Mb, but still!
    Me.Geek = True

  7. #7
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Does the SQL Profiler on the server shed any light?

    Are there any database locks perhaps?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •