Hi all,
I have 2 computers each having a MS Access database, same set of tables with same structure. I want to match and synchronize both of those databases. I want to create some scheduled task which match all the tables in both databases, check for any new rows or updation of preexisting values, and then update the tables. Is there any way of achieving this within MS Access? if yes, how? or Do I need to write some application in some programming language?
Thanks.
You can create a series of queries to check for changes to tables matched on the key (updates) or missing the key value (additions). The queries can be run through VBA.
EDIT For an external database with a database password:
SELECT *
FROM [MS Access;PWD=password;DATABASE=C:\Docs\LTD.mdb].Companies a
INNER JOIN Companies b
ON a.ID=b.ID
WHERE a.CompanyName<>b.CompanyName
You could also use the above string without the Where statement as a recordset and loop through each field to find mismatches. You can even use VBA and TableDefs to loop through the tables collection and build the SQL strings.
New Record in Companies aliased b
SELECT *
FROM [MS Access;PWD=password;DATABASE=C:\Docs\LTD.mdb].Companies a
LEFT JOIN Companies b
ON a.ID=b.ID
WHERE b.ID Is Null
If they're on a LAN then why not share the tables between the two computers? You want to split the MDB into a Front End MDB containing the queries, forms, reports, macros and modules with just the tables and relationships in the Back End MDB. The FE is copied to each network users computer. The FE MDB is linked to the tables in the back end MDB which resides on a server. You make updates to the FE MDB and distribute them to the users, likely as an MDE.
See the "Splitting your app into a front end and back end Tips" page for more info. See the free Auto FE Updater utility to make the distribution of new FEs relatively painless. Although this utility is be overkill for two PCs I mention it for others who will read this posting in the future.