I'm doing some work to integrate two databases in two different places. One is an Access db and the other SQLServer. The size of the set is really quite small, say 350 records. This could be really straightforward however the two people that 'own' the datasets insist on being able to add new fields to the databases.
Any ideas as to how i can best go about this? Cheers! ;-)
This is the poster-child for RDF. Unfortunately, such problems are 1% technical and 99% diplomacy. Good luck.
I'm making a few assumptions: 1. You and your end-users are OK with Microsoft Access; 2. The unified view doesn't need to change on the fly when the underlying columns change, i.e., you're willing and able to take some kind of action to update your view; 3. The "join" columns are not going to change.
If all three assumptions are true, I would just create a new Access database, link in the two old tables, and create a query the selects all columns (or just the ones you know you want) from both tables. When the data changes, just use the Linked Table Manager to update the tables and then make any needed changes to the queries (if you didn't select all columns).
If you're fortunate, the users just want the flexibility to add columns but have no actual plans to do so. Sometimes they just don't want to commit to a design.
I'm personally not a big fan of using Access in production scenarios, but since one of your production data sources is already Access, and since Access is so good at integrating separate data sources, it may make sense in this case.