If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Synchronising Tables Within a Database
The organisation I am in is looking at a new database system, which must be
Access. It has many remote users that can not, due to many reasons, ever hook up to the home office network (or any variation of an internet network). Nor can we place our database on a web server or equivalent system. So, I have come up with a way to export the remote database information into excel and then import it into the Main Database in several tables called “Transfer In-Out (Name)” table. Essentially, these tables are exactly the same structure as the Main Database tables that they correspond with. I also know how to added modification dates to the tables in order to identify when a record was last modified (thanks to this forum and the MS website). New records are easy to deal with, I just use an amend query and add them to the Main Database Tables. The problem is modification to existing records. Is there a way, and how would you do it, to synchronise two tables WITHIN one database? NOTE - this is a follow on from a question I am asking about Database Design. I am muddling through the problem, but hopefully there will be light soon! -- Nick B |
#2
|
|||
|
|||
Synchronising Tables Within a Database
You can update records in table1 from records in Table 2, but how do you
know the records in table 1 are not more current than the records in table 2? Are you going to using the modification date to determine the winner? The simplest method would be to DELETE records from the MasterTable and replace them with records from the remote table if the ModificationDate in the MasterTable is earlier than the ModificationDate in the RemoteTable DELETE FROM MasterTable WHERE MasterTable.PrimaryKey IN (SELECT M1.PrimaryKey FROM MasterTable as M1 INNER JOIN RemoteTable On M1.PrimaryKey = RemoteTable.PrimaryKey WHERE M1.ModificationDate RemoteTable.ModificationDate) Then just run the routine you are using to add new records. Otherwise, you will need to use something like UPDATE MasterTable INNER JOIN RemoteTable ON MasterTable.PrimaryKey = RemoteTable.PrimaryKey SET MasterTable.Field1 =[RemoteTable].[Field1] , MasterTable.Field2 =[RemoteTable].[Field2] , MasterTable.Field3=[RemoteTable].[Field3] , ... ///Repeat for all the fields except the primary key/// WHERE MasterTable.ModificationDate RemoteTable.ModificationDate If you wanted to do individual fields you could, but you would require many queries (one for each field) that would look like the following. UPDATE MasterTable INNER JOIN RemoteTable ON MasterTable.PrimaryKey = RemoteTable.PrimaryKey SET MasterTable.Field1 =[RemoteTable].[Field1] WHERE MasterTable.ModificationDate RemoteTable.ModificationDate AND (MasterTable.Field1 RemoteTable.Field1 OR (MasterTable.Field1 is Null and RemoteTable.Field1 is Not Null) OR (Mastertable.Field1 is Not Null and RemoteTable.Field1 is Null)) '================================================= === John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County '================================================= === Nick wrote: The organisation I am in is looking at a new database system, which must be Access. It has many remote users that can not, due to many reasons, ever hook up to the home office network (or any variation of an internet network). Nor can we place our database on a web server or equivalent system. So, I have come up with a way to export the remote database information into excel and then import it into the Main Database in several tables called “Transfer In-Out (Name)” table. Essentially, these tables are exactly the same structure as the Main Database tables that they correspond with. I also know how to added modification dates to the tables in order to identify when a record was last modified (thanks to this forum and the MS website). New records are easy to deal with, I just use an amend query and add them to the Main Database Tables. The problem is modification to existing records. Is there a way, and how would you do it, to synchronise two tables WITHIN one database? NOTE - this is a follow on from a question I am asking about Database Design. I am muddling through the problem, but hopefully there will be light soon! |
#3
|
|||
|
|||
Synchronising Tables Within a Database
Alternatively, see my November, 2003 "Access Answers" column in Pinnacle
Publication's "Smart Access" for how to create a single query that will update matching rows and insert new rows. You can download the column (and sample database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "John Spencer" wrote in message ... You can update records in table1 from records in Table 2, but how do you know the records in table 1 are not more current than the records in table 2? Are you going to using the modification date to determine the winner? The simplest method would be to DELETE records from the MasterTable and replace them with records from the remote table if the ModificationDate in the MasterTable is earlier than the ModificationDate in the RemoteTable DELETE FROM MasterTable WHERE MasterTable.PrimaryKey IN (SELECT M1.PrimaryKey FROM MasterTable as M1 INNER JOIN RemoteTable On M1.PrimaryKey = RemoteTable.PrimaryKey WHERE M1.ModificationDate RemoteTable.ModificationDate) Then just run the routine you are using to add new records. Otherwise, you will need to use something like UPDATE MasterTable INNER JOIN RemoteTable ON MasterTable.PrimaryKey = RemoteTable.PrimaryKey SET MasterTable.Field1 =[RemoteTable].[Field1] , MasterTable.Field2 =[RemoteTable].[Field2] , MasterTable.Field3=[RemoteTable].[Field3] , ... ///Repeat for all the fields except the primary key/// WHERE MasterTable.ModificationDate RemoteTable.ModificationDate If you wanted to do individual fields you could, but you would require many queries (one for each field) that would look like the following. UPDATE MasterTable INNER JOIN RemoteTable ON MasterTable.PrimaryKey = RemoteTable.PrimaryKey SET MasterTable.Field1 =[RemoteTable].[Field1] WHERE MasterTable.ModificationDate RemoteTable.ModificationDate AND (MasterTable.Field1 RemoteTable.Field1 OR (MasterTable.Field1 is Null and RemoteTable.Field1 is Not Null) OR (Mastertable.Field1 is Not Null and RemoteTable.Field1 is Null)) '================================================= === John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County '================================================= === Nick wrote: The organisation I am in is looking at a new database system, which must be Access. It has many remote users that can not, due to many reasons, ever hook up to the home office network (or any variation of an internet network). Nor can we place our database on a web server or equivalent system. So, I have come up with a way to export the remote database information into excel and then import it into the Main Database in several tables called "Transfer In-Out (Name)" table. Essentially, these tables are exactly the same structure as the Main Database tables that they correspond with. I also know how to added modification dates to the tables in order to identify when a record was last modified (thanks to this forum and the MS website). New records are easy to deal with, I just use an amend query and add them to the Main Database Tables. The problem is modification to existing records. Is there a way, and how would you do it, to synchronise two tables WITHIN one database? NOTE - this is a follow on from a question I am asking about Database Design. I am muddling through the problem, but hopefully there will be light soon! |
#4
|
|||
|
|||
Synchronising Tables Within a Database
=?Utf-8?B?Tmljaw==?= wrote in
: The organisation I am in is looking at a new database system, which must be Access. It has many remote users that can not, due to many reasons, ever hook up to the home office network (or any variation of an internet network). Nor can we place our database on a web server or equivalent system. I have posted many times in many different forums about how to synchronize two databases in code. There are two easy parts to the process: 1. unmatched records in the source database: this means the records were added, and should be appended to the destination database. 2. unmatched records in the target database: this means the records were deleted, and should be deleted in the destination database. These are very easy to figure out using a simple outer join. The hard part is efficiently updating records that have changed. You don't want to write SQL that updates every single field, but you also don't want to walk through a recordset. The efficient way to do this is to run a SQL update for every field in the two tables where the values are not equal. This is accomplished by walking through the Fields collection and writing a SQL string with an appropriate WHERE clause, which would result in something like this: UPDATE Table1 SET Table1.Field1=Table.Field1 WHERE Nz(Table1.Field1,"")Nz(Table2.Field1,"") Obviously, you need some logic to test the underlying field type, since you want the WHERE clause for numeric fields to look like this: WHERE Nz(Table1.Field1,0)Nz(Table2.Field1,0) But that's the basic idea: 1. run the two outer join queries, one to append new records, one to delete old records. 2. walk through the Fields collection and issue a SQL Update for each column. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
Thread Tools | |
Display Modes | |
|
|