Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access 2010: Merge Database with the same structure

Posted on 2011-09-30
16
Medium Priority
?
338 Views
Last Modified: 2012-05-12
So I had to provide my staff with a copy of Access form/database (cant use serever) and merge their input from time to time. As my form contains several multiple fields I cant do that through 'ammend query'. All my tables are connected to the main one through the same key (star )
I googled a solution below however it doesnt include databases with relationships:
http://ilovecoding.co.uk/blog/ms-access/merge-multiple-ms-access-databases-into-a-single-one/

For now Im just copy pasting but that obviously takes too much time...

Can somebody provide me an example of a similar VBA solution?
0
Comment
Question by:Ianski
  • 9
  • 7
16 Comments
 
LVL 40

Expert Comment

by:als315
ID: 36891445
You can assign to every user's base some unique number, which will be added to main DB.
For example: First user will have ID =1, Second - 2 etc.
Main table should have index form this ID (user) and record ID.
Example have 2 tables and 2 append queries. You can also update records, if you like it with update queries
DB27373708.accdb
0
 

Author Comment

by:Ianski
ID: 36891523
Yup thats correct however as I wrote cant use append (or ammend :P ) queries cause I have multipple input fields. Besides Id like to join 2 separate databases not tables if possible.
Looking for VBA automation
0
 
LVL 40

Expert Comment

by:als315
ID: 36891730
May be you can show example of your Dbs? What is joining of databeses? Usually joining means adding data from "user's" databases to "central".
You can use code from your example for automation, but you should prepare data for joining.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Ianski
ID: 36891895
thats my first project in Access so sry if I didnt explain correctly.
So by prepare data for joining do you mean linking databases?
14064202.jpg
0
 
LVL 40

Expert Comment

by:als315
ID: 36892050
No, I mean adding some unique IDs for identification of source DB. If you have many tables - you should add these two fields (user ID and Record ID) to every table in main DB.
0
 
LVL 40

Expert Comment

by:als315
ID: 36892078
I can't understand how you can copy/paste data from user DB to main. You shoud also correct references i¿ you have some autonumber IDs
0
 

Author Comment

by:Ianski
ID: 36892443
right, imagine one of my engineers wants to work from home this week. As I cant get server, I send him an empty database (empty copy of master) so that he could type in another strategy in it. When he finish he send it back to me and Id merge it with master one.
So id have to copy all existing rows from all tables in his version to master (no dupes) starting from one row in master table 'textbox'
0
 
LVL 40

Expert Comment

by:als315
ID: 36895574
I think you could have too many problems with this approach.
For example:
During homework user will add some new records to table textbox and then delete one or more (let Strategy will be 10,11,13,15). In all other tables you have reference to this table. When you are adding data to main table, autonumber field will be (if your textbox table will be unchanged) 10,11,12,13. But references in other tables will be unchanged and you should have method for correcting them. And you will always have problems when home users will be more then one.
0
 

Author Comment

by:Ianski
ID: 36908654
users get a form with empty database and they add one strategy per their form/database eg one main primary key in textbox. There is no autonumber - primary key is equal to unique predefined strategy number.
Im sure there is a way to connect via ADODB or something just to copy all rows from one database to another one /no dupes/
0
 
LVL 40

Expert Comment

by:als315
ID: 36908760
It is not a problem, if references will not be changed.
Can you upload empty DB (central) with tables and another one with one strategy filled (with sample data)?
0
 

Author Comment

by:Ianski
ID: 36910595
didnt upload any pics but commented them out
login/pass: jan

cheers
2dbs.zip
0
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 36912218
Many times on EE was stated - it is not good idea to have lookup fields in tables.
Method in included example can't be used with such tables.
Problem tables:Maintenance Activities, SAP Implementation Assurance, SAP Implementation Normal. May be better will be add separate tables for fields with lookup fields.
Idea is in using one query for all tables. Tables are listed in table TablesList. Problem tables are commented with '.
Open form Form1, set name of second db (should be in current DB path) and press button.
Version-A.zip
0
 

Author Comment

by:Ianski
ID: 36918146
thanks for help. Ill analyse it tomorrow. Any idea how else could I sort out my lookup fields?
0
 
LVL 40

Expert Comment

by:als315
ID: 36919396
This is not pure lookup, but multivalued field - Failure modes covered - in table Maintenance Activities. Are you sure it is nesessary? You can't use such field in append or update query.
0
 

Author Comment

by:Ianski
ID: 36925608
OK thanks. looks good. Could you show me on one of those tables how would you do sort out lookup field using separate table? Or should I start a new question?
0
 
LVL 40

Expert Comment

by:als315
ID: 36925844
Due to EE rules you should start new question.
But, as I've mentioned in last comment, problem is in multivalued lookup fields, not in simple lookup fields.
You can sort values in form without doing it in table.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question