Make Table Query

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Thanks Hans, I will study this.

Leesha

Reply | Quote

The data is already on a backend server so that piece is all set. …

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.

Reply | Quote

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

Reply | Quote