Eh... you can't, really. Access doesn't have an agent to leverage.
Are you planning on having this open on a computer 24/7? If so (bad idea), you could create a form that stays hidden, then use it's "OnTimer" event to decide when you should perform the archive process.
Another way to do this would be creating a stand-alone application that fires via windows scheduler that would hit your db and perform the required tasks.
I have a simple archival procedure. The data I use if typically use once then throw away, but sometimes we need to refer back to it. We archive to keep the size of the table down to a minimum. All I do is have a second table in a second database that has the same structure and I append records into the archive table and then delete them in the active table.
If you ever need to search the archive you can use a UNION ALL query (it will probably be very slow).
This type of archiving works for us, but may not be appropriate for you. You will have to decide that for yourself.
Oh sorry, probably I didn't explain myself properly. Is there a way to export the data in particular fields of a table into a speadsheet? So when the user clicks on the button Export, he can select the field or the entire table and it is exported to MS Excel. Since I would like this to be an archival scenario, it is best to continuously add data to that same spreadsheet rather than create new spreadsheets for each export. What do you think? And on clicking another button, the user can retrieve the data say based on a given month (based on a field in the table).
To be able to export a variable number of fields to Excel you will probably have to have the user select the fields to include, and maybe the order and then from the selections create a custom query (on the fly), save it, and then use it with the DoCmd.TransferSpreadsheet to export it.
I also think you will have to use automation to 'append' new dat to an exisitng spreadsheet. It will be easier for you to export all of the data at once.
1) To allow the user to select certain fields to export:
- Create a query that contains the desired data.
- Mask the field names to make it easier for Joe User.
- On a form, add 2 list boxes. List1 RowSourceType is "Field List" and RowSource is the query you created. List2 RowSourceType is "Value List" and RowSource starts out blank.
- Double click on List1 adds the selected value to the RowSource of List2. (reply if you need help on this, but there is examples in thre help file)
2) Create the query dynamically:
- A command button creates a string that contains the SQL statement that selects each field in List2's RowSource.
Dim strSQL As String
Dim iC As Integer
Dim qry As DAO.QueryDef
strSQL = "SELECT "
For iC = 0 To Me.lstFields.ListCount - 2
strSQL = strSQL & Me.lstFields.Column(0, iC) & ", "
strSQL = strSQL & Me.lstFields.Column(0, iC+1)
strSQL = strSQL & " FROM qryYourQuery"
Set qry = CurrentDb.CreateQueryDef("qryNew", strSQL)
Alter the code slightly if the query already exists and you want to update the SQL.
3) Quick and easy way to dump the results of the query into Excel:
DoCmd.OutputTo acOutputQuery, "qryNew", acFormatXLS, "File Name (can be left blank and user is prompted)", True
4) Archiving to a DB instead of a Spreadsheet:
I would agree that this is more solid, more useful and easier to control (fewer things that can go wrong). Using the above techniques, you then prompt the user for the name of the destination DB (or hard code it), use a database object to connect to the DB, create a table there based on the fields of the query, momentarily link to it and run the query as an update query.
For help on the update query, use the QBE to make one and you'll see how the SQL looks.
I created another database that houses the tables I want archive. I will append all the data based on some date criteria. The appending to the archive table is working ((after it appends to archive, the data is deleted from the table) but I am trying to retrieve the data and insert it back into the original table and that is giving me beans.
Have a look at my code: I have a retrieve command button on a dialog form, so when the user enters a start date and end date, the code needs to open the archive database, select the records based on the date criteria, insert the records into the original table and close the archive db.
Private Sub cmdRetrieve_Click()
Dim conConnector As ADODB.Connection
Dim strSQL As String
If IsNull(Me.StartDate) Or IsNull(Me.EndDate) Then
MsgBox "Please enter a Date in the format mm/dd/yyyy", vbInformation, "No Information"
Set conConnector = New ADODB.Connection
conConnector.Open "Provider='Microsoft.JET.OLEDB.4.0';" & _
"Data Source ='C:\DB\Archive.mdb';"
strSQL = "INSERT INTO tblArchiveAppend( ChequeNo, ChequeDate, Payee,Amount, AmountCashed, DatePresented, Status )"
IN 'C:\DB\Archive.mdb'" SELECT tblCheques.ChequeNo, tblCheques.ChequeDate, tblCheques.Payee, tblCheques.Amount, tblCheques.AmountCashed, tblCheques.DatePresented, tblCheques.Status
FROM tblCheques WHERE (((tblCheques.ChequeDate) Between [Forms]![frmArchivePeriod]![StartDate] And [Forms]![frmArchivePeriod]![EndDate]))"
Set conConnector = Nothing
I don't know if I have to reset the database to the current db in order to insert it back into the original table or if my sql statement is incorrect.