Need help with code to copy and rename a table. 1

[beverlee]

I have an access database on a laptop that goes out into the field where records are entered. One of the tables is actually a replication of a table out of a different database to save duplicate data entry. When the laptop comes back to the office, they hook to the network and click a command button that runs this code:

Private Sub Command0_Click()
Dim D As Database, R As Recordset, Q As QueryDef

Set D = CurrentDb
Set Q = D.CreateQueryDef("")
Q.SQL = "INSERT INTO InmateLaptop ( InmateID, LName, FName,MI,DOB,SS ) SELECT Inmate.InmateID, Inmate.LName, Inmate.FName, Inmate.MI, Inmate.DOB, Inmate.SS FROM Inmate;"
Q.Execute

MsgBox "The Inmate Table has been successfully updated!"

DoCmd.Close acForm, "InmateUpdate"

End Sub


This works fine as far as pulling new records from the Inmate table to the InmateLaptop table. However, records that were updated in the Inmate table and already exist in the InmateLaptop table are not updated when the button gets clicked.

Basically I just need to pull an exact copy of Inmates and rename it InmateLaptop each time the "synch button" is clicked. This is only a one-way data exchange. The InmateLaptop table will not be changed when out in the field. Any suggestions on code to make that copy/rename process without affecting the links between tables?

Thanks for your time.

 

[Golom]

Since the Laptop is just an unmodified copy of what's in the main system, why not just clear it out before you do the insert?

Private Sub Command0_Click()
    Dim D As Database, R As Recordset, Q As QueryDef

[COLOR=red]currentDB.Execute "Delete * From InmateLaptop "[/color]

Set D = CurrentDb
Set Q = D.CreateQueryDef("")
Q.SQL = "INSERT INTO InmateLaptop ( InmateID, LName, FName,MI,DOB,SS ) SELECT Inmate.InmateID, Inmate.LName, Inmate.FName, Inmate.MI, Inmate.DOB, Inmate.SS FROM Inmate;"
Q.Execute

MsgBox "The Inmate Table has been successfully updated!"

DoCmd.Close acForm, "InmateUpdate"

End Sub

 

[beverlee]

Perfect! How easy was that! Thanks again for your time!

 

[MichaelRed]

change the query to an UPDATE query. that will add any new records and modify any existing records. many fail to realize / understand the details of "UPDATE". logically, to 'update' a table, it has to update all recrods in the destination which exist in the source, thus any/all records in the source must end up in the destination in the same form they exist in the source. try it you'll like it (a-la-mikey).




MichaelRed