Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581

    Deleting from the server


    I have files on the server that I need to delete. I can delete them one at a time. But, I really want to delete several of them at one time. I have a query that shows which files that I want to delete. On that query I have the server path and the name of the files. How would I delete all of them from the server?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    bit of vba to loop through the form recordset and delete them one by one?

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Use Shell to delete them all at once?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I don't know how to do either of those. I will need to look it up.

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    As you already have the names in a query, then Ajax's method would easier.

    I was just thinking of "del MyNameOfFiles.*" with the Shell command, if they were named correctly.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You can try this loop, just added to a command button Click event (adjust it to match your names):
    Code:
    Dim db as DAO.Database,rsDelete as DAO.Recordset
    Dim sServerPath as string, sFileName as String, sFullFileName as string
    
    
    Set db=CurrentDB
    Set rsDelete = db.OpenRecordset ("Select * From qryYourQueryName;",dbOpenSnapShot)
    
    
    If rsDelete.RecordCount=0 Then Exit Sub
    
    
    Do until rsDelete.EOF
    	sServerPath = rsDelete("ServerPath") 'replace with the server path field name
    	if Right(sServerPath,1)<>"\" Then sServerPath =sServerPath & "\"
    	sFileName =rsDelete("FileName") 'replace with the file name field name
    	sFullFileName =sServerPath & sFileName 
    	Kill sFullFileName 'delete the file
    rsDelete.MoveNext
    Loop
    
    
    Set rsDelete = Nothing
    Set db =Nothing
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Or even easier directly from your query via a custom VBA function (be very careful when testing as the files are deleted with no warning).
    https://docs.microsoft.com/en-us/off...kill-statement

    Have a look at the attached sample. Open the table and modify the ServerPath and FileName data to include some test files you want deleted. Then simply run the query... You will just need to copy the public function into one of your standard modules.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Thanks. I think I followed your instructions correctly. It took me to the debugger. Here are the errors it had.
    Click image for larger version. 

Name:	Error1.jpg 
Views:	28 
Size:	10.7 KB 
ID:	46738

    Click image for larger version. 

Name:	Error2.jpg 
Views:	28 
Size:	68.1 KB 
ID:	46739

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Make sure the query runs without presenting a parameter prompt. Since you seem to be using all results from the query, there's no need to write it as a sql Select statement. Just db.OpenRecordset("qryRecordsReportAttachment", dbOpenSnapshot)? Probably not the cause, but the semicolon isn't necessary in code.

    You get this error when a parameter isn't available to DAO. That can be a simple spelling mistake in a referenced field for example, or form/control that is not available. If the query is a parameter query you'll have to make any parameters available to DAO. One way is to define them in the query properties sheet itself (right click on query background in design view to see sheet). Another way is to declare and set the parameters in your code. FWIW, another way is to pass the parameter to a variable and reference the variable, but that doesn't apply in this case.

    Most will tell you that it's advisable to have Option Explicit in the declaration portion of all modules (along with the Option Compare statement) so as to catch undeclared variables when compiling. It is an option in the vb editor (Require variable declaration).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here is some updated code that should work with your parameter query, but the solution in post #7 would probably be easier:
    Code:
    Dim db as DAO.Database,rsDelete as DAO.Recordset
    Dim sServerPath as string, sFileName as String, sFullFileName as string
    Dim prm as Parameter, qdf as DAO.QueryDef
    
    
    Set db=CurrentDB
    'Set rsDelete = db.OpenRecordset ("Select * From qryRecordsReportAttachment;",dbOpenSnapShot)
    Set qdf = db.QueryDefs("qryRecordsReportAttachment")
    For each prm in qdf.Parameters
        prm.Value=Eval(prm.name)
    Next prm
    Set rsDelete = qdf.OpenRecordset dbOpenSnapShot
    
    
    If rsDelete.RecordCount=0 Then Exit Sub
    
    
    
    
    Do until rsDelete.EOF
    	sServerPath = rsDelete("Path") 'replace with the server path field name
    	if Right(sServerPath,1)<>"\" Then sServerPath =sServerPath & "\"
    	sFileName =rsDelete("AttachmentName") 'replace with the file name field name
    	sFullFileName =sServerPath & sFileName 
    	Kill sFullFileName 'delete the file
    rsDelete.MoveNext
    Loop
    
    
    
    
    Set rsDelete = Nothing
    Set db =Nothing
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I had a problem with using the query. When I double clicked on the query itself, it deleted the files with no problem. When I added it to my command button, it did not delete any files. I would like to try the vba code instead, if possible. This is really interesting and I'm trying to learn. I added the updated code and got this:
    Click image for larger version. 

Name:	Error2.jpg 
Views:	22 
Size:	60.3 KB 
ID:	46750

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Not sure what you mean, are you saying you got an error? If the code just stopped on that line you should just continue run it by clicking the Run button on the ribbon or pressing F5. Then click Debug\Compile to compile your VBA project and finally do a compact and repair.

    Cheers.
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If the code stopped there it is likely because you had a break on that line and it's being remembered. It's called a ghost break. Compiling may not fix the issue. Best to edit the code so as to enable the compile feature (any edit will do). Then undo your edit and compile. That usually fixes it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Yes, I'm getting an error. I guess working late nights, I forgot to add it. Here is what I'm getting.

    Click image for larger version. 

Name:	Error1.jpg 
Views:	10 
Size:	76.7 KB 
ID:	46755

    Click image for larger version. 

Name:	Error2.jpg 
Views:	10 
Size:	81.1 KB 
ID:	46756

  15. #15
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    This is really strange. I just tried it again today. It works great. I'm not sure what I did wrong. I really appreciate your help. I went over the code so many times that I think I understand what you did. Thank you again.

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

Similar Threads

  1. Replies: 3
    Last Post: 09-30-2021, 11:53 AM
  2. Replies: 5
    Last Post: 03-11-2019, 02:35 PM
  3. Upgrade from SQL Server 2008 to SQL Server 2014
    By RayMilhon in forum SQL Server
    Replies: 4
    Last Post: 07-12-2017, 04:54 PM
  4. Replies: 0
    Last Post: 12-20-2012, 01:44 PM
  5. Replies: 11
    Last Post: 12-14-2010, 01:25 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