how to auto synchronize two tables in access

  • Thread starter Thread starter Amod
  • Start date Start date
A

Amod

I am using Access 2007, can someone please help me with how to copy data to
another table in the same DB when one table gets updated?

Thanks in advance!
 
I am using Access 2007, can someone please help me with how to copy data to
another table in the same DB when one table gets updated?

Thanks in advance!
Click to expand...

why do you have two tables with the same data? You have to use forms
to do it. Unlike SQL Server, Access has no triggers, so you can't
execute code directly against the table to run an append query to
another table.
 
Actually I am importing a SharePoint list which creates a table inside
Access, now this table(being a linked table) is not been read by Infopath?
Any suggestions?
That is why I was try to create a table which can be read by Infopath.
 
I agree with you Jeff, it is data redundancy but I am really stuck into this:

Actually I am importing a SharePoint list which creates a table inside
Access, now this table(being a linked table) is not been read by Infopath?
Any suggestions?
That is why I was try to create a table which can be read by Infopath.

OR

let me put my problem into other words:
I have two tables in a Access 2007 database: 1st (Linked with Infopath
forms) & 2nd(linked with the SharePoint list). Now the problem here is,
Infopath is not able to link, actually it doesnot even detect the 2nd table
during data connection.

That is: I have two non-sync tables since Infopath enters some data which is
NOT going in 2nd table that is why I canot show my submitted data to
Sharepoint List.

--
Amod Goyal
IT System Developer
NSK Precision America


Jeff Boyce said:
Amod

I'll echo Piet's comments ... why? What business need are you attempting to
solve by having the same data in two tables in the same DB? This is
redundant, uses extra space, and risks your data integrity.

If you'll explain a bit more specifically about your particular situation,
folks here may be able to offer more specific suggestions.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
Click to expand...
 
Amod

I'll echo Piet's comments ... why? What business need are you attempting to
solve by having the same data in two tables in the same DB? This is
redundant, uses extra space, and risks your data integrity.

If you'll explain a bit more specifically about your particular situation,
folks here may be able to offer more specific suggestions.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Amod

No experience with using InfoPath against Access. Perhaps another reader
has done this...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


Amod said:
I agree with you Jeff, it is data redundancy but I am really stuck into
this:

Actually I am importing a SharePoint list which creates a table inside
Access, now this table(being a linked table) is not been read by Infopath?
Any suggestions?
That is why I was try to create a table which can be read by Infopath.

OR

let me put my problem into other words:
I have two tables in a Access 2007 database: 1st (Linked with Infopath
forms) & 2nd(linked with the SharePoint list). Now the problem here is,
Infopath is not able to link, actually it doesnot even detect the 2nd
table
during data connection.

That is: I have two non-sync tables since Infopath enters some data which
is
NOT going in 2nd table that is why I canot show my submitted data to
Sharepoint List.
Click to expand...
 
I am using Access 2007, can someone please help me with how to
copy data to another table in the same DB when one table gets
updated?
Click to expand...

There are valid reasons for needing to do this, such as when you
have to synchronize data stored in two different formats.

I've done this many times, and the key is DAO code that does three
things (but it assumes a PK in the table):

1. check for new records on each side, append to the other side.

2. check for deletes on each side, delete from the other (this
really has to be done via a DELETE flag, since otherwise, there's no
way you can tell the difference between a delete in database A and
an add in database B).

3. write on-the-fly SQL to update each column of the existing
records where they don't exist.

The first two are quite easy. The latter is more complicated. I use
a DAO recordset with the updatable fields, and then walk the .Fields
collection of the recordset to construct the SQL string that updates
the non-equal fields. You'd have something like this:

For each fld in rs.Fields
strField = fld.Name
strWhere = "Nz(" & strTable1 & "." & strField ",'')<>Nz(" _
& strTable2 & "." & strField ",'')"
strSQL = "UPDATE " & strTable2 & "." & strField _
& " SET " & strField & "=" & strTable1 & "." & strField
DBEngine(0)(0).Execute strSQL, dbFailOnError
Next fld

That's the basics. Obviously, you'd declare your variables and all
that, and you'd probably need a CASE SELECT in order to have Nz()
statements relevant to the specific data types of the fields, but
the idea here is that you issue one SQL UPDATE statement for each
column.

I've implemented this many, many times, in fact.
 
Back
Top