Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2013
    Posts
    62

    Kill statement not working in Access 2016


    Greetings,
    I have a db built which executes a Kill statement on files it processes. This has been working perfectly on both 2013 and 2010, and still does. Now, one of my end-users was upgraded to 2016 (32-bit) and when the macro hits this function, it throws an error at the Kill statement line. Has Kill been made obsolete, or is there a Reference I may be missing in 2016, or does my syntax need to change somehow for the new version?

    Code:
    Public Function archive_files()
    Dim d As String, ext, x
    Dim srcPath As String, destPath As String, srcFile As String
    srcPath = "\\drive\path\"
    destPath = "\\drive\path\path\"
    ext = Array("*.txt")
    For Each x In ext
        d = Dir(srcPath & x)
            Do While d <> ""
                srcFile = srcPath & d
                FileCopy srcFile, destPath & d
                Kill srcFile            'this is where it errors
                d = Dir
            Loop
    Next
    End Function

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    What's the error being reported?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Has Kill been made obsolete, or is there a Reference I may be missing in 2016, or does my syntax need to change somehow for the new version?
    No. Possibly. No ... in that order
    Check whether there are any references marked as MISSING on that computer
    However as Kill is part of the standard VBA reference library VBE7.dll which isn't Access version specific it seems unlikely that is the cause.
    However it may be a symptom of a wider underlying problem

    Do you have Option Explicit on each code module?
    Does your code compile without error?

    You should also be aware that each new Access version is less tolerant of coding errors,
    So it may be another error in the section of code which Kill is part of
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What is the real code that is being executed?

    What gets substituted for this
    Code:
    \\drive\path\path\

  5. #5
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2013
    Posts
    62
    Quote Originally Posted by ridders52 View Post
    No. Possibly. No ... in that order
    Check whether there are any references marked as MISSING on that computer
    However as Kill is part of the standard VBA reference library VBE7.dll which isn't Access version specific it seems unlikely that is the cause.
    However it may be a symptom of a wider underlying problem

    Do you have Option Explicit on each code module?
    Does your code compile without error?

    You should also be aware that each new Access version is less tolerant of coding errors,
    So it may be another error in the section of code which Kill is part of
    The error is Run-time error '13' Type mismatch.

    I do not have any MISSING references.
    I do have Option Explicit; still not working.
    I only have remote access to this user, but I will attempt to compile next chance I get.

    The whole function copies the files it finds into a subdirectory, then deletes the files in the original directory. The copying does take place, so that part appears to be firing without issue, however when it comes to the Kill statement it throws the error.

    This is perplexing!

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Error 13 isn't caused by the use of the Kill function.

    The question was whether the other user has any missing references.
    Also whether it's running in a trusted location.
    If you use activeX controls, check whether these are disabled by default on that PC.

    Worth checking whether it compiles on your PC.
    Your byline says you have 64 bit and you already said the other user has 32 bit Access.
    So that's another possible issue to look into.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2013
    Posts
    62
    Quote Originally Posted by ridders52 View Post
    Error 13 isn't caused by the use of the Kill function.

    The question was whether the other user has any missing references.
    Also whether it's running in a trusted location.
    If you use activeX controls, check whether these are disabled by default on that PC.

    Worth checking whether it compiles on your PC.
    Your byline says you have 64 bit and you already said the other user has 32 bit Access.
    So that's another possible issue to look into.
    - The VBA prior to the Kill line executes, and when I hit debug it highlights the Kill statement, so if Kill isn't causing the error I'm not sure what would be.
    - Sorry for being unclear: the user does not have any missing references.
    - It is trusted (for the user)
    - ActiveX is enabled (for the user)
    - I am running 2013 x64, however this db has been in active, daily use by multiple users for nearly 7 months, running 2010 x32, without issue. One user has been upgraded now to 2016 x32, and this error has appeared. I have commented out the Kill statement within this function as a workaround until I can figure this out, and the rest of the db including all other VBA functions are executing without issue

  8. #8
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I would suspect this is a reference issue, I found a number of things that broke when I switched from 32 to 64 bit access, some of them very unfathomable.
    However I am using Kill in a routine to perform a very similar task, and it works fine on my Acc 2016 64 Bit Office.

    I've take a picture of the references I have just in case it helps.
    Click image for larger version. 

Name:	References.PNG 
Views:	20 
Size:	104.8 KB 
ID:	33848
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I would also have expected that but error 13 suggests a datatype error.
    Please double check you have ticked require variable declaration in VBE Options.
    Also that you have Option Explicit on EVERY code module
    Then compile on the offending PC.

    If it stills fails to kill, suggest you reverse the process and DECOMPILE to remove any corrupt compile code.
    If that's new to you, read this link http://www.fmsinc.com/MicrosoftAcces.../Decompile.asp
    It will take less than a minute to do.
    After that compile again then compact
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Perhaps it is worth noting that ext and x are variant data types, which could play right into a "data type mis-match error". So some sort of array object is being passed to a variant, which may be OK. However, as I see it, Nulls are being appended to strings. Afraid I cannot recall the effect of that - it may coerce the variable to be Null. Suggest you debug.print the values of all the variables to see if they're what you expect.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2013
    Posts
    62
    I'll try the debug.print and report back.

  12. #12
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2013
    Posts
    62
    OK, I got on the users computer and was able to test each of the suggestions, I will recap each from above:

    - Minty: the references shown in your screenshot, I have the same enabled
    - Require Variable Declaration is ticked under VBA options
    - Option Explicit is on every module within the db
    - I have run Compile/Compact/Repair
    - I followed the steps to the linked Decompile process, then Compiled/Repaired
    - I tried debug.Print srcFile, and it returned the correct path/string that it should have.

    I want to point out once again that by commenting out just the Kill line, the remaining function executes as it should. This function loops through the text files it finds in the defined directory and then copies/pastes them to a subdirectory.

    I have tried creating a completely separate function which does nothing other than execute Kill, with the same error 13 result.

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Thanks for doing all the tests.
    Next possibly stupid question.
    Are you absolutely sure the file exists on the user's computer and is in the specified folder
    I ask because some files have different locations in 32-bit /64-bit

    Have you tried defining x, Ext explicitly? As linq spotted earlier, at the moment they are defaulting as variant
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by ridders52 View Post
    As linq spotted earlier, at the moment they are defaulting as variant
    Good thing we don't get points for a good answer
    Not that my answer was that good. Remains to be seen.

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Micron View Post
    Good thing we don't get points for a good answer
    Not that my answer was that good. Remains to be seen.
    Ahem. Of course I should have said 'as micron said earlier ....'
    That plus my previous attempt with two ford transits in another thread, I certainly don't deserve points for a good answer...
    Sorry
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 03-02-2018, 11:08 AM
  2. Automating Outlook 2016 from Access 2016
    By jcc285 in forum Programming
    Replies: 10
    Last Post: 09-30-2017, 01:53 PM
  3. Replies: 5
    Last Post: 09-20-2017, 10:07 AM
  4. Replies: 1
    Last Post: 02-23-2016, 11:14 AM
  5. Replies: 2
    Last Post: 10-10-2012, 02:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums