Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2006
    Posts
    48

    Unanswered: Archiving tables

    I have three tables in my database and I have to perform periodic archival of the data. This is to happen every month. What is the best way to go about this using vba code?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    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.

  4. #4
    Join Date
    Jun 2006
    Posts
    48
    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).

    Thanks

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That still doesn't address the scheduling aspect...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jun 2006
    Posts
    48
    No, it doesn't but in my case, it need not be a fixed schedule as the user has to decide at what point in time, this archive should be performed.

    DCKunkle, thanks for your post. It's a good idea, I didn't even think about a second database. I'll try this out.

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    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.

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Hello,

    Couple ideas for you:

    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.
    Code:
    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) & ", "
    Next
    strSQL = strSQL & Me.lstFields.Column(0, iC+1)
    strSQL = strSQL & " FROM qryYourQuery"
    
    Set qry = CurrentDb.CreateQueryDef("qryNew", strSQL)
    CurrentDb.QueryDefs.Refresh
    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:
    Code:
    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.

    have fun,
    tc

  9. #9
    Join Date
    Jun 2006
    Posts
    48
    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"
    Else
    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]))"
    conConnector.Execute strSQL
    'conConnector.Close
    Set conConnector = Nothing
    End If
    End Sub

    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.

    Help please

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •