Sql statements in access

[TM2]

I am trying to remove the information in one table and place it in an archive table.&nbsp;&nbsp;I am using the following code,<br>&quot;Insert into ArchiveTable Select * from Table;&quot;<br>The problem&nbsp;&nbsp;I am getting is that if there is more than one record it only inserts the first record and then stops, even though I have selected *, which means I should get all records in the table.<br>any suggestions??<br>

 

[xtreme1]

That is definetely strange, I tried it here and it worked OK.&nbsp;&nbsp;Maybe an error in one of the source databases fields is causing it to halt early?&nbsp;&nbsp;Not really sure why that would happen, but if you can let me know what was the cause when you figure it out.&nbsp;&nbsp;Good luck! =] <p>xtreme1<br><a href=mailto:rbio@excite.com>rbio@excite.com</a><br><a href= > </a><br>

 

[TM2]

I think that the problem is that when I created the archive table, I added the field of archive date, which is set to the default value of now().&nbsp;&nbsp;When I removed this, all records from the original table where inserted into the archive table. The problem I have is that I need the archive date, so that the archive table can be used a number of times without writing over the data that was already in the archive table.

 

[xtreme1]

&nbsp;&nbsp;&nbsp;&nbsp;That's an interesting problem.&nbsp;&nbsp;One way of solving it might be to create a recordset of the archive table and actually do one record at a time, one field at a time, UPDATE's to it.&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;In other words, set each of the archive tables fields equal to it's counterpart in the live table manually in, say, a While loop.&nbsp;&nbsp;Each time through the loop you would do an .AddNew to the archive tables recordset, and a .MoveNext to the live tables recordset.&nbsp;&nbsp;I think this would allow you to keep your archive date field, and solve the problem of only one record being appended.&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If you decide to give this a shot and have problems, send me an e-mail and I'll see if I can't help you out some more.&nbsp;&nbsp;=] <p>xtreme1<br><a href=mailto:rbio@excite.com>rbio@excite.com</a><br><a href= > </a><br>

 

[PaulF]

Assuming that you are starting from scratch, or the previous records have an entry in the archive date, you could add an additional step and run an Update query after the Append query.&nbsp;&nbsp;This query would update the archive date field for those records where archive date IsNull.&nbsp;&nbsp;<br><br>PaulF