Importing data

[humer]

Hello !!

That is a very interresting thing: I made a database with 20 tables. After I made it I send it to many (15) people to fill it in. The problem is now to synchronyze all this copies of the same database, but with different content inforamtion.

Can somebody tell me how can I import all the data in one database.

Thanks ...

 

[M8KWR]

it depends on if the central database is to store the data or to add additional data as well!!!

You have caused yourself a lot a work whatever route you decide to take.

This is what i would do.

Each copy of the database needs a unique reference, so you know which copy it has come from.

The reason for this is, that there is going to be links between each table, and each using the same value, when the autonumber assigns the value. So if each table within each copy of the database had another reference number, if would not matter if you had an IDNumber of "1" 20 times, because you can assign a second IDValue to make sure the data all matches etc.

Then once you have done that, then you can just copy the data from each table of each copy of the database into the main one.

You could probably create a rountine to copy them all for you.

I hope this helps, and good luck

 

[Pat Hartman]

You could use replication. Read about it in help. If you have autonumbers that you are using for identification purposes such as OrderID or CustomerID, you will need to change these to numbers you generate yourself. When you make a database into a replica, Access changes all autonumbers to generate random numbers which range from very small negative numbers to very large positive numbers and you don't have any control of this. So unless you want -78 as a CustomerID, you'll need to create your own.

The best solution is to leave the autonumbers as the primary keys and foreign keys but to add additional fields to handle any "visible" IDs that you need to assign. Use a two part system. The first field should identify the replica copy and the second field can just be a sequential number that you assign using the DMax() function.

 

[humer]

that I did:
I made a replication of all databases and I tried to synchronize them. Of corse - no good results.

my proposed solution
after your post, I think to do the next things:
1. after all the data are completed I take each db in part and I convert the autonumber to a number field type.
2. for each table I will made an update query, where to each ID I will add a number X*db, where X is an big integer and db is the database order: to have not an interpolation between data.
3. import all data in one database.

Questions:
1. do you think is a good idea ?
2. how can i import data ? in file menu - no import options, in tables - I can import a table, but there will be two more table ?

 

[Crilen007]

Have to use an append query, or cut and paste the data into the table.


Append method:

Link each table into your current DB, then use the append query to append one at a time.

IE:


tblData = Main table. (the final one)
tblCompA \
tblCompB - Linked tables
tblCompC /


rename tblcompA to tblCompData

then create an append query to append the data to tblData

after its done, rename tblCompB to tblCompData, and run the append query again.

Keep repeating until all tables are done.

Thats usually what I do.

If I have a lot of tables to do, I usually write a script.