History File

[DS]

At the end of the business day I want to hit a command button and have
all of the records from "Sales" and "SalesDetails" move from Database1
to "SalesHistory" and "SaleDetailsHistory" in Database2. I then want to
save the last "SalesID" from the "Sales" table, Delete all of the
records in "Sales" and "SalesDetails" in Database1, then finally when
the new business day starts be able to reference the last "SalesID"
number used and start atthe next number. So, how would I move the
records from one Database to another and then reference the saved number
to start the new day?
Thanks
DS

 

[Graham R Seach]

Firstly, the easiest way is to link to Database2.SalesHistory and
Database2.SalesDetailsHistory from Database1.

Then execute the following:
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim strSQL As String

On Error Goto ProcErr

'Open a transaction
Set ws = DBEngine(0)
ws.BeginTrans

'Copy the data
strSQL = "INSERT INTO SalesHistory SELECT * FROM Sales"
db.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO SalesDetailsHistory SELECT * FROM SalesDetails"
db.Execute strSQL, dbFailOnError

'Truncate the active tables (assumes Cascade Delete is enabled)
strSQL = "DELETE * FROM Sales"
db.Execute strSQL, dbFailOnError

ws.CommitTrans

Proc_Exit:
'Clean up
On Error Resume Next
Set db = Nothing
Set ws = Nothing
Exit Sub

ProcErr:
ws.Rollback
MsgBox Err.Number & vbCrLf & Err.Description
Resume Proc_Exit

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[DS]

Firstly, the easiest way is to link to Database2.SalesHistory and
Database2.SalesDetailsHistory from Database1.

Then execute the following:
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim strSQL As String

On Error Goto ProcErr

'Open a transaction
Set ws = DBEngine(0)
ws.BeginTrans

'Copy the data
strSQL = "INSERT INTO SalesHistory SELECT * FROM Sales"
db.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO SalesDetailsHistory SELECT * FROM SalesDetails"
db.Execute strSQL, dbFailOnError

'Truncate the active tables (assumes Cascade Delete is enabled)
strSQL = "DELETE * FROM Sales"
db.Execute strSQL, dbFailOnError

ws.CommitTrans

Proc_Exit:
'Clean up
On Error Resume Next
Set db = Nothing
Set ws = Nothing
Exit Sub

ProcErr:
ws.Rollback
MsgBox Err.Number & vbCrLf & Err.Description
Resume Proc_Exit

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Great! Thanks Graham. I appreciate the response. I'll give it a try
and let ou know how it works. I figure if I do this I'll be able to
keep the Daily order input tables to around 300 hundred records at most.
Then ay sales reports will be taken from the history side. this
should keep it running faster.
Thanks
DS

 

[Guest]

in other words i think you can use an append query

"DS" escreveu: