Deleting Oldest File

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 Replies


ADVERTISEMENT

Deleting Oldest File

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

Deleting .ldb File

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

Modules & VBA :: Deleting File From Network

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

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 1 Replies View Related

Error After Deleting Split Database File

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

Modules & VBA :: MDB File - Deleting Multiple Items In List At Once

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

Modules & VBA :: Deleting Record And Then Delete Listed File?

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

Identify Oldest Inventory

Oct 11, 2006

I am trying to identify how long inventory has been in a specific location. The information that I have is the current inventory level and movements that have put the inventory there. Assuming FIFO I would like figure out the following.

ex: I have 4 movements with various dates AND I have 3 pieces currently on hand, how do identify the date of the last 3 movements (assuming that the one that has been moved out was the first one in)? Of course there is a long list of part #'s, on hand quantities and movements into the location so the logic should work for all.

Sample data

Part # Date Quantity
123 09/30/1999 1
123 09/01/1999 1
123 08/30/1999 1
123 07/30/1999 1
456 09/30/1999 1
456 09/01/1999 1


Part # On Hand
123 3
456 1

the Result I would like to see:
123 09/30/1999 1
123 09/01/1999 1
123 08/30/1999 1
456 09/30/1999 1

Is there a way to do this?

Thanks
Pat

View 3 Replies View Related

Sort By Oldest Date First (records)

Nov 16, 2006

Hello All,

I currently have a form where I would like the form to display the oldest account first, so the overall objective is the employee will action the oldest account first and then go onto the next one etc.

Can anyone please tell me how to do this?

Thank You.

Mos

View 2 Replies View Related

Displaying Oldest And Newest Date In Query?

Jul 28, 2015

I have Query which has dates in multiple columns. I need to know if there is a formula that will allow me to display the oldest date and the newest date?

Below is the output of my Query in Excel. I need the output to have two additional columns: Beginning Date and End Date. Based on the example below, I would need the beginning date to show 6/21 and the end date column to show 6/23 so when I export to excel, it shows these two dates. In Excel, there is a min/max formula that can do this but it looks like the min/max in Access only displays the min/max data? Unfortunately, creating this formula is not an option after the fact. Secondly, I need the word "DIRECTION" to show in it's own separate column in the in the query. I don't know if I can do this without adding another table to the Query or if I need can just add a column in design view and put something in the criteria that will do this.

I have included an example of my current output and what I need the output to read.

Current Output
Sunday
In
Out
Monday
In
Out
Tuesday
In
Out

[code]....

View 2 Replies View Related

Forms :: Date Sort Order - Newest To Oldest

Dec 17, 2013

I have a combobox (cbo_FileNames) that is unbound and gets fed on activate from a folder

Code:
Set SourceFolder = FSO.GetFolder("c:GundogManagerBackUp")

My problem is that the file names are as follows:

GundogManagerBackUp 02_12_13.xls
GundogManagerBackUp 03_12_13.xls
GundogManagerBackUp 07_08_13.xls
GundogManagerBackUp 16_11_13.xls
GundogManagerBackUp 17_12_13.xls

As you can see it consists of the name and a date of the backup. When it populates the combobox I want it to go in date order I newest to oldest. At the moment its going of the first number.

View 9 Replies View Related

Modules & VBA :: Open In Main Form The Oldest Case Taken From Query

Jul 7, 2015

I need to make a query that will show the oldest cases and then open this one in the main form. It will be possible that there could be several cases with the same date. For example the query runs and the results are 7, how could I get one of those cases in the main form.

The idea is that a person will click on a button called "next case", the query will run and then it will open the case in the main form.

How to get this started?

View 7 Replies View Related

Deleting All Related Sub Records When Deleting The "main" Record.

Apr 12, 2008

I have two tables.

1. Persons (list of persons)
2. Job history (list of jobs)

each person have their own job history. all these jobs are stored in the job history table. when i delete a person i would like the job history for this person deleted as well. each job stored in the job table have a field with person name, so that it is linked to this person.

how can i do this? vba or simple properties options?

- Roy

View 1 Replies View Related

Subtract Sales From Stock With Oldest Date First And Then Move To Next Date?

Sep 16, 2014

Table 1 has all the items I am trying to sell with sell by date after which I cannot sell this item. Then in Table 2 I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.

So now somehow I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with sale by date 23/09.

So based on the attached example, I can see that on 16/09 I will consume only 5 cases from sell by date 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.

I would like also to include the logic that if Item is out of date it would move to the next sell by date.

So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10.

For Item 2 I can see that units with Sell by date 30/09 will be consumed on 25/09 and I will start taking stock from next sell by date which is 14/10.

View 2 Replies View Related

Deleting

Feb 21, 2005

I have a problem about deleting record from a form, it will be grateful if anyone can help me with this.

Here are my tables:
Par_tor: some personal details such as ID, name, phone NO., etc
Par_tion: the record of who has participated which activities.
Activity: name of the activity.

My problem is: the main form of the my form contains the stuffs in table Par_tor, and the subform contains the stuffs in Par_tion, so the user can view these participator's details and the activities they have participated.

In the main form, I also wanted to created a delete button to delete an entire record (including a participator's detail + his/her participation record) at once, but it didn't work since the participation was in the Par_tion table not in the Par_tor.

So is there any way that I can delete these records at once?

View 4 Replies View Related

Deleting A Record With VB

Jun 15, 2005

Hi

I have a form with a "Clear Form" button on to allow the user to delete the record they are working on without updating the database. A question window pops up asking if they are sure they want to delete the record, if they answer yes the form will clear and re-load without updating the database.

The OnClick Event Procedure will not allow me to delete the data, it always saves. I've tried
DoCmd.SetWarnings False
If MsgBox("Are you sure you want to abandon changes to this record?", vbExclamation + vbYesNo, "LogBook 2002") = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
Else
DoCmd.SetWarnings True
End If
but I get the error "2046 - The command or action DeleteRecord isn't available now." Is there another way, a better way?

View 9 Replies View Related

Deleting Records

Aug 4, 2005

I have two tables (same data but slightly different attribute structure) with a one-to-one relationship (the join field is "ID"). There are 69 matching records in these tables. How can I delete these matching records from table A, while leaving them alone in table B?

I'm confused because I brought both tables into a select query, created a join from ID to ID (within in the query's design view), and then added all fields from table A to the query. I then ran the select query and saw the 69 records from table A in the query's data view that I wanted to delete, highlighted all records and clicked delete. However, this action deleted the 69 matching records from TABLE B, not Table A!!! How is this possible? What should I do instead? Thanks.

View 3 Replies View Related

Deleting Records

Nov 11, 2005

A strange request but I hope someone can help with this one

I have a table (tbl_Econ) where I have to delete a specified number of records from a table. It does not matter which records as long as I delete the exact number

e.g On a form text box I enter the number or records to be deleted (e.g.6000).

The table (tbl_Econ) has 8000 records, so I have to delete 6000 records. I need to be able to do this automatically :eek:

View 1 Replies View Related

Deleting Records

Feb 1, 2006

How can I go about deleting records fully? So that there autonumber, etc can be used again without access moaning.

Thanks :D

View 1 Replies View Related

Deleting Records

Mar 14, 2006

I have a query (Query1) which returns results In one field recordID.

How do I delete records in a table where the RecordID is in the list Query1.recordID?

Thanks

View 4 Replies View Related

Deleting Tables

May 11, 2006

:confused:
I am Developing a simple VBA program for Access for my final year of my Degree and I have come up with a problem that I don’t seem to be able to fix.

I have create tables using SQL

SQL = "CREATE TABLE tblInvoiceLines" & _
"(InvoiceNo LONG, PartNo LONG," & _
"Quantity LONG)"

DoCmd.RunSQL (SQL)


Deleting this with the below code works,

SQL = ""
'This will delete the Invoice Line Table
SQL = "DROP TABLE tblInvoiceLines"
DoCmd.RunSQL (SQL)


But once I alter the table with the below code the Delete command doesn’t work, does anyone know how I can fix this problem?

SQL = "ALTER TABLE tblInvoiceLines " & _
"ADD CONSTRAINT fkInvoiceNo " & _
"FOREIGN KEY NO INDEX (InvoiceNo) REFERENCES " & _
"tblInvoices (InvoiceNo) " & _
"ON UPDATE CASCADE " & _
"ON DELETE CASCADE "


conDatabase.Execute SQL


Thanks
Coops

View 2 Replies View Related

Deleting Tables

Jun 7, 2006

Hi i have a lot of linked table that i want to delete I have the macro below that deletes one at a time but i have to put in the name is there a way to delete all the ones that end in _cfd_posting as before this name there is a six digit number but these r all different is this possible

Sub delete()
DoCmd.DeleteObject acTable, "060204_cfd_postings"
End Sub

View 1 Replies View Related

Deleting A Record

Nov 22, 2006

Hi All,

I have a very simple name and address DB. What I would like to know is "What is the easiest way of finding a record using the switchboard and then deleting that record from the DB?"
I am an Access novice so nice easy replies would be appreciated.

Many Thanks
Keith:o

View 4 Replies View Related

Deleting Record After Use

May 6, 2007

Hi All

I have 2 tables "Customers" and "Work in Progress (WIP)"

The "WIP" table is result of the form "WIP" where I input all my data for each job completed. I use a lookup control "JobNo" to input the customer details from the table "Customers".

The objective is to call up the customer details into "WIP" add the job details, then on save, delete the record in the "Customers" table.

If this is possible, could you give me some ideas how.

Thanks in Advance.

Geoffk

View 5 Replies View Related

Deleting Lines

Jun 18, 2007

Let's say we have a table:

Sipping branchReceiving BranchProduct #Qty to ship
a c PR1 43
a d PR1 25
a k PR1 1
....


Now I want to delete all lines that have same product code and shipping branch as a privous line.
So the only max amounts for each product and each shipping branch will remain.

How to delete those lines?

Thank you.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved