Modules & VBA :: Deleting File Based On Date
Feb 12, 2014
So I currently have a module which automatically backs up my database to a separate file everyday on open of my database.
If is it possible to create a function which will allow me to delete these backups after a certain amount of days (eg 30 days). They all have different file names based on the time that the backup was created but have the same extension of "*.accdb". I ask because it stores EVERY backup so the storage space required will soon build up unless manually deleting the files which is not what my client will want,
I have tried using the "Kill(pathname)" function but have had no luck yet!
If possible, I would like this to be done automatically either on open or on close of my database.
Would this be done with a module?
View Replies
ADVERTISEMENT
Dec 2, 2014
I have a button that adds pictures/files to a network location. So people from all over the company can do this and all pictures/files are stored in one location.
I also have a delete button, but a waning pops up and the file remains on the network.
PHP Code:
Run-time error '424': object required
Code:
My.Computer.FileSystem.DeleteFile (Path)
where Path is a string, files location on network.
View 4 Replies
View Related
May 28, 2014
In appendix is .mdb file with this thema.
Inside you can find one form with listbox (with multi selecting ability).
I use this code :
Dim strSQL As String
Dim i As Variant
With Me.se1
For Each i In .ItemsSelected
SQL = "DELETE '*' FROM [t1] WHERE [id] = " & .ItemData(i) & " ;"
CurrentDb.Execute (SQL)
Next
End With
Me.se1.Requery
Result is this one :
1) If I delete only one item in list, it is ok. I can do it again and again and it is working fine.
BUT
2) If I delete more items in list at once, it is ok - but if I will try to do it again then there is an error because .ItemData(i) value is Null.
View 10 Replies
View Related
Jan 7, 2015
What is the correct syntax that would delete a file when the record is deleted. The file's path is listed in a record field, MailLocation. Every time I try this code, I receive an error!
I've tried the below, and number of iterations, including calling the killfile differently (me![MailLocation], me.MailLocation).
Code:
Private Sub Form_AfterDelConfirm(Status As Integer)
Dim KillFile As String
KillFile = me!MailLocation
Kill KillFile
End Sub
View 6 Replies
View Related
Jul 26, 2013
I want to write an sql or vba code to delete records based on the month...For example I have a field called date which contains a date and I want to delete all Dates that are In April
Ive tried
Delete from LinkTable Where month(Date) = 'April'
Still Nothing
View 3 Replies
View Related
Feb 15, 2015
When creating an incoming inspection database, I want to set it up so when I am in a form to add data, I want to have a command button where I can pull up the associated CAD drawing based on part number and drawing number. I would like to enter the directory location into a table so I could change it if I need to.
So basically I want to go something like this:
.tblDirctoryLocation
txtDirectory
.tblProduct
txtProductName
txtProductNumber
txtProductCADNo
These tables would not be related to one another as there will only be one record for the directory location and multiple records for products.
Somehow I want the db to assemble file location like this:
“EQuality_DirectoryIncoming” & “ProductNumber_” & “CADDrawing” & “.pdf”
End result: EQuality_DirectoryIncomingProductNumber_CADDraw ing.pdf
Is there any way this could be done?
View 1 Replies
View Related
Mar 3, 2015
I have created a database that tracks the locations of all the faxes we receive in a day. We have a dedicated employee that enters them into the database as well as who they are assigned to and other pertinent information. At the end of the day, I monitor the network folders where the faxes are saved to change the status of the fax in the database to done, still needs processing, or awaiting approval. Is there any way that I could set up some VBA that would automatically update the status column for the record based on the location of the file? The file name is recorded in the database so then if it sees that that file is in the end folder it could automatically update the status to finished.
The response I got on another forum is as follows:
-The impression I'm getting is that you've got a database of information over here.
-And it lists the file name, but not location.
-And then you have a physical folder structure with the fax files in it.
-And there are folders for done, awaiting approval, or processing.
-So what you're looking for is something to read those folders and update the status column based on which folder it is in?
-So at the start of the day we have:
New
-Fax1.tiff
-Fax2.tiff
-Fax3.tiff
-Fax4.tiff
Finished
-
In progress
-
And at the end of the day the folders look like this:
New
-Fax1.tiff
Finished
-Fax3.tiff
-Fax4.tiff
In progress
-Fax2.tiff
And you want your code to browse those folders, and update the status of each filename based on it's folder?If the filenames are unique, you can just use VBA to open the folder, create an array of the files and then set the status. Create two separate functions, one for in progress, one for completed and call them one after the other. There'd be no need to check the current status, but you could store the wrong status if a file is duplicated, or a filename repeated.
View 3 Replies
View Related
Nov 8, 2005
I have a database that needs repaired, but can't because the database thinks users are still in it. The users listed in the .ldb are not logged in. Can I simply delete the ldb file, or the contents of the ldb file, then repair, w/o consequences?
thank you!!!
View 3 Replies
View Related
Apr 9, 2006
Problem
code:
Option Compare Database
Option Explicit
Function Backup()
Dim fso As FileSystemObject
Dim sSourcePath As String
Dim sSourceFile As String
Dim sBackUpPath As String
Dim sBackUpFile As String
Dim strSQL As String
sSourcePath = Application.CurrentProject.Path
sSourceFile = Application.CurrentProject.Name
sBackUpPath = Application.CurrentProject.Path & "Odontiatreio BackUp"
sBackUpFile = sSourceFile & Format(Date, "ddmmyyyy") & " " & Format(Time, "hhnnss") & ".mdb"
'Add new file to table.
'Assumes tblFile has 2 fields:
' FileID (AutoNumber)
' FileName (text)
strSQL = "INSERT INTO BackUp(BackUpFileName) " _
& "Values('" & sBackUpFile & "')"
CurrentDb.Execute strSQL
'Delete oldest filename if there are more than 5 records.
strSQL "DELETE * FROM BackUp " _ ============>Error<=========
& "WHERE BackUpID IN(" _
& "SELECT MIN(BackUpID) FROM BackUp " _
& "HAVING COUNT(*)>5"
CurrentDb.Execute strSQL, dbFailOnError
Set fso = New FileSystemObject
fso.CopyFile sSourcePath & sSourceFile, sBackUpPath & sBackUpFile, True
Set fso = Nothing
End Function
Error:
Compile error:
Expected Sub, Function, or Property
What is wrong???
__________________
Alexandros
View 5 Replies
View Related
Sep 23, 2005
Greetings!
I am using this in a module to copy files to a directory:
----------------------------
Public Function Backup()
Dim fso As FileSystemObject
Dim sSourcePath As String
Dim sSourceFile As String
Dim sBackupPath As String
Dim sBackupFile As String
sSourcePath = "c:a"
sSourceFile = "db2.mdb"
sBackupPath = "c:aa"
sBackupFile = "BackupDB_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hhmmss") & ".mdb"
Set fso = New FileSystemObject
fso.CopyFile sSourcePath & sSourceFile, sBackupPath & sBackupFile, True
Set fso = Nothing
End Function
---------------------
Now, what I want to do is delete the oldest file after there have been 5 files copied. In other words, I will be running this once a day and only want to retain the newest 5 copies. Once the sixth has been copied, delete the oldest one in the directory.
Any suggestions?
View 3 Replies
View Related
Nov 20, 2014
What I would like to achieve via code on a button. From the table orders
Code:
orderDate orderID ref name amt vat
02/02/2014 56789 01 magdi 600 100
22/06/2014 12122 01 magdi 654 100
22/06/2014 65897 02 simon 250 159
22/06/2014 12345 02 simon 156 140
23/07/2014 56781 01 magdi 400 250
01/08/2014 12457 01 magdi 250 900
01/09/2014 56787 02 simon 126 500
02/09/2014 56781 04 peter 400 250
02/09/2014 12345 02 simon 450 122
02/09/2014 65897 04 peter 250 231
02/09/2014 12122 05 hargr 654 100
02/09/2014 79457 05 hargr 254 200
I need to create csv files from the above based on unique date and ref and with filename showing date and ref. So from the above we should get 9 csv files in this format
02/02/2014-01.csv
22/06/2014-01.csv
22/06/2014-02.csv
23/07/2014-01.csv
01/08/2014-01.csv
01/09/2014-02.csv
02/09/2014-02.csv
02/09/2014-04.csv
02/09/2014-05.csv
How to produce the above result ...
View 5 Replies
View Related
Nov 16, 2011
We have a MS Access Application. It involves authentication as well. I split the database and created this db_be file. I deleted this. Now I'm not able to login into this Application.
View 1 Replies
View Related
Oct 16, 2014
I want to save NOW() (i.e. Date and time) into an event log file. But I just cannot work out the syntax. My insert statement works fine without the date field in, but fails on a syntax error (3134) when I include it.
strsqlac = "INSERT INTO EventLog ( EventTime, User, EventType, EventMessage, DocRef, AutoSeq, CoCode ) " & _
" Values ( '" & Now() & "', '" & GlobUser & _
"', '" & Mess2 & _
"', '" & Mess1 & _
"', '" & Docref & _
"', " & AutoSeq & _
", '" & CoCode & _
"' );"
... it's the first column, EventTime that is the issue. I have tried several different ways of wrapping it in the VALUES () without success.
My database field EventTime is defined as a General date which Access 2007 tells me will give me date and time. That's what I want.
The Value formats I have tried (currently showing as 'NOW()' above, are ...
#NOW()#
#'NOW()'=
'#NOW()#'
and (as I say) 'NOW()'
View 5 Replies
View Related
Jun 19, 2015
I have a split database with a frontend that I semi-regularly update (when one of my users finds a bug I never would have thought for looking for). On my menu screen i have a text box that says: "Last Updated: ##/##/####".
At the moment I manually update the date every time I update the frontend.
Is there a way I can use the DateModifed property of the .mdb file for the frontend as the date in my textbox using VBA?
View 5 Replies
View Related
Feb 3, 2014
Vbscript/vb macro code for retrieving the last modified date of a file uploaded in a URL.
I am able to get the file but wanted to get the last modified date of it.
View 5 Replies
View Related
Jun 5, 2014
Is there a function I could use to find the date listed in the "date created" property of an Access file?
View 4 Replies
View Related
Jan 3, 2006
Hi guys,
I have a question I've been trying to figure out with no luck. I have 2 fields, companyname and zip, these 2 fields may have duplicate values or they may be uniquei.e.
companyname zip
billybobs 68135
dilberts 68137
billybobs 68135
what I need to do is delete all of the duplicate records where both companyname and zip match. Is there a way I can do that with a query?
Any help is greatly appreciated
Thanks
James
View 14 Replies
View Related
Jun 21, 2005
I have a form with a subform inside of it. On the subform I have created a field for a check box. I wish for the user to be able to click 1 or more check boxes in the subform and then click a delete button that removes all selected entries.
I have this code from a seperate post regarding this problem, but am not sure how to edit it to reflect what I need.
Sub DeleteRecordButton_Click()
'Delete the selected record
dim strSQL as String
strSQL = "DELETE * FROM mytable WHERE mytblID = " & Me![mySubForm].Form![mytblID]
DoCmd.RunSQL strSQL
MsgBox("Confirmed")
End Sub
Thank you!
View 1 Replies
View Related
Nov 16, 2004
Hi all!
I'm rapidly beginning to get a little confused... I'm trying to mass-delete records based on the content of a field.
I want to run either a query or a button on a form (or anything, for that matter) that will delete any records when there is an X in the N_Disp field.
Any ideas?
Thanks,
Barry
View 3 Replies
View Related
Apr 1, 2013
I am trying to create a Delete query.
I am trying to delete a specific part from multiple BOMs in my database.
I have a table of the BOMs that I want to look in. I called this table PartTable. I also linked my database table SYSADM.REQUIREMENT which contains all the requirement parts for all of our BOMs.
So I am wanting to delete only part number 123XX from each of the BOMs in my PartTable.
I am able to select the records with:
Code:
SELECT SYSADM_REQUIREMENT.*
FROM SYSADM_REQUIREMENT INNER JOIN PartTable ON SYSADM_REQUIREMENT.WORKORDER_BASE_ID = PartTable.PART_ID
WHERE (((SYSADM_REQUIREMENT.WORKORDER_TYPE)="M") AND ((SYSADM_REQUIREMENT.WORKORDER_BASE_ID)=[PartTable].[PART_ID]) AND ((SYSADM_REQUIREMENT.WORKORDER_LOT_ID)="0") AND ((SYSADM_REQUIREMENT.PART_ID)="123XX"));
Now how do I delete these same records.
I am getting error saying I have to select a table to delete from....
View 11 Replies
View Related
Nov 30, 2012
I am wanting to delete a record based on 2 criteria. ive had a look on the net and it looked so simple. I tried
PHP Code:
CurrentDb.Execute _ "DELETE FROM tblRecommendedProducts " & _ "WHERE ClientDetailsID = " & [Forms]![frmClientSale]![ClientDetailsID] And "WHERE ItemsID = " & [Forms]![frmClientSale-Retail]![ItemsID], dbFailOnError
View 14 Replies
View Related
Aug 29, 2013
I have recently set up a button that exports multiple queries to one Excel file, using TransferSpreadsheet code. This works great and saves a lot of time, the only thing is on many of the queries I have a date range set, so you have to enter a date range to get the results.
Is there away that once I press the button I enter the date range once and than it exports the rest of the data based on this range, rather than entering the same dates multiple times.
View 1 Replies
View Related
Jul 26, 2015
So I have a search form that I created that has also a subform (a continous form, that has a query as its source) where my records are listed. I started on making a Between dates filter where i would be able to pick dates and get the results of records that are in that range.
I put two text boxes in which I set format to Short Date so that I get the button to select the date from a calendar for each text box. Then I made a button that is only for this and added a Clickon code so that it filters my subform over the recordsource of the form. The problem is is that i get an error and it doesn't filter.
Code:
Private Sub btnDatumUpis_Click()
Dim SQL As String
SQL = "SELECT qrySearchV.VID, qrySearchV.MarkVoz, qrySearchV.ModelVoz, " _
& "qrySearchV.TipMot, qrySearchV.Regist, qrySearchV.VlaVoz, " _
& "qrySearchV.KorVoz, qrySearchV.KatV, qrySearchV.DatumUVoz, " _
[Code] ....
View 5 Replies
View Related
Nov 10, 2014
I am trying to export records to excel that are in a given month based on an input month. The user would select the month with the records desired and click the button to export them to excel. The attached zip file has an image of the database and the code behind the button is:
Code:
Private Sub Command33_Click()
DoCmd.OpenReport "HLA_TAT", , , "Len(Exception & '') > 0 AND Receive_Date > #" & Forms!HLA_TAT.Date & "#"
End Sub
[CODE] can not access referenced form HLA_TAT [?CODE]
View 11 Replies
View Related
Aug 22, 2014
I have a form which i use for a user to select an excel file they want to import and then click a cmd button to import the file into a table which works fine, however i want to append a date into a date field from an unbound txtbx before the file is imported so it will look something like;
id;date;excel info;excel info;excel info.
View 7 Replies
View Related
Mar 28, 2014
In my Access2000 db, I have a table, on which many different queries are based. Many forms are based on those queries. I want to delete some redundant fields from the table. I tried deleting one, but I found I could no longer open any of the associated queries and forms. Is there a simple way to delete table fields so that it doesn't stop me from opening associated queries and forms? (There are hundreds of them, and I have a lot of table fields that I want to delete).
View 1 Replies
View Related
