Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi,

    I have a query that creates a new new table which will be used to store old data. When the user clicks the button that has the code to run the query they are prompted that the exisiting table will be deleted. My goal is that rather than having the existing table be replaced I'd like a new one to be added with a sequential name. IE tblbackup, tblebackup1, tablebackup2, tablebacku3 etc. I'm not concerned that the database will get too large with all of the backups as this is not something that will happen often. I'm more concerned as to what will happen if this accountant decides he needs old information after he's made a change. I have no idea what the code would look like to do this or if it is even possible.

    Thanks!
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Perhaps you could use an append query instead of a make-table query? You could set aside a field to distinguish "current" records from older ones - for example a date field, or a sequence number.

    Otherwise you could assemble the SQL string of a make-table query in code and execute it.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    As Hans suggests, I would use an append query to do that - and you might find Periodically Archive Records useful.

    I have a more basic question however. Are you wanting to just keep records in case one gets deleted or mangled in some way? Or are you really wanting to record changes that are made to records? If it's the latter, then you might want to think seriously about moving the back-end (data) to SQL Server where you can use triggers to automatically archive a record anytime it changes. And you can prevent records from ever being deleted. It's not very difficult to do it you use ODBC linked tables. If you want more info about how it works, post back and we will do our best to guide you through the process.
    Wendell

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi,

    The data is already on a backend server so that piece is all set. The reason that the data needs to be saved is because there are the occassional data allocations that from time to time are assigned to a different catagory. When this happens, all the previously calculated data is recalculated which works fine. The problem is that they need to keep the history of the individual row sources as they were prior to the change in catagories. I'm not sure if that makes sense. I had thought of using an append query however the date field that presently exists is used to calculate the totals for the month. Each month when new data is imported, the append query that does that looks to make sure the date has not been previously imported. If the date is there then the query stops. Due to those, I wouldn't be able to append the new data to the table. That is why I thought of creating a new table as backup. After the table is created, a delete query runs to delete the exisiting information and then an new append query runs to import the new data. The whole thing works other than the replacing of the exisiting backup table. I'm not sure how to write the sql to have it create a new table with a sequential name.

    Leesha

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could create a table tblSeqNo with a single number field SeqNo. Enter 0 in this field initially.

    The following code will increment this number, and use it to create tables tblBackup1, tblBackup2, etc.:

    Code:
    Sub MakeNewTable()
      ' Declare the variables we use
      Dim dbs As DAO.Database
      Dim rst As DAO.Recordset
      Dim lngSeqNo As Long
      Dim strTable As String
      Dim strSQL As String
    
      ' Reference to the current database
      Set dbs = CurrentDb
      ' Open recordset on sequence number table
      Set rst = dbs.OpenRecordset("tblSeqNo", dbOpenDynaset)
      ' Place it in edit mode
      rst.Edit
      ' Increment Seqno
      rst!SeqNo = rst!SeqNo + 1
      ' Store new value in variable
      lngSeqNo = rst!SeqNo
      ' Save record
      rst.Update
      ' Close recordset
      rst.Close
      ' Release memory
      Set rst = Nothing
      ' New table name
      strTable = "tblBackup" & lngSeqNo
      ' SQL string for make-table query
      strSQL = "SELECT * INTO " & strTable & " FROM tblData"
      ' Run the SQL
      dbs.Execute strSQL, dbFailOnError
      ' Release memory
      Set dbs = Nothing
    End Sub
    In this example, records are taken from tblData, you can change that of course.

    See the attached sample database; the code is in a module named basCode.

    Note: the code requires a reference to the Microsoft DAO 3.6 Object Library.
    Attached Files Attached Files

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='Leesha' post='769233' date='04-Apr-2009 19:17']The data is already on a backend server so that piece is all set. ...[/quote]
    What do you mean by backend server? I see by some of your previous posts that you've worked with SQL Server 2005 Express. If your backend is SQL Server, the archiving process could easily be implemented on a record by record basis with triggers. An archiving trigger looks something like this example for an Address table"
    Code:
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go
    
    ALTER TRIGGER [upd_tAddresses] ON [dbo].[tAddresses] 
    FOR UPDATE AS
    --Capture the AddressID so we can do the WhoDunItWhen
    DECLARE @AddressID as int
    SELECT @AddressID = del.lngAddressID
    FROM deleted del
    --Archive the old record
    INSERT INTO arcAddresses
    	([lngAddressID],
    	[lngPersonID],
    	[intAddressTypeID],
    	[strAddress],
    	[strAddress2],
    	[strCity],
    	[strState],
    	[strCountry],
    	[strPostCode],
    	[dtBadAddress],
    	[strBadAddressReason],
    	[blnPreferred],
    	[dtEdited],
    	[strWhoEdited])
    SELECT del.[lngAddressID],
    	del.[lngPersonID],
    	del.[intAddressTypeID],
    	del.[strAddress],
    	del.[strAddress2],
    	del.[strCity],
    	del.[strState],
    	del.[strCountry],
    	del.[strPostCode],
    	del.[dtBadAddress],
    	del.[strBadAddressReason],
    	del.[blnPreferred],
    	del.[dtEdited],
    	del.[strWhoEdited]
    FROM deleted del, inserted ins
    UPDATE tAddresses
    	SET	[dtEdited]=getdate(),
    		[strWhoEdited]=suser_sname()
    	WHERE lngAddressID=@AddressID
    The advantage of this approach is that all changes get recorded automatically as a record is changed, and this particular example also tracks who made the change and when it was made. And it works whether a change is made using forms, or if you are making changes directly in the table. A somewhat different approach, but quite powerful.
    Wendell

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks Hans, I will study this.

    Leesha

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Wendell,

    Sorry to confuse you. I'm not using sql tables with this. The Access tables are split and stored on their server. The user works locally on his PC and all data is saved to the tables on the server. He needs a history of the data "just in case" someone asks him what it was prior to the change in category allocation.

    Leesha

Posting Permissions

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