Having trouble with 2 database merge

[Regression]

Hello All,

I have a database at my organization wich is used to evaluate individuals to determine if they qualify for another one of our programs.

The database which stores the information is a multi table database wich resides on our server in the office.

As it stands now clients are interviewed in their homes and information is captured on paper. It is then brought back to the office and entered.

This will no longer work as our care workers no longer have enough time to do this.

I have been ask to create a way for them to enter this information on laptops when in the homes and then come back and merge this information into the main database.

I can successfuly do this with clients. By dropping the unique ID made on the laptop DB and by appending the info to the server table. The problem I am haveing is that subsequent data from additional tables is then orphaned.

Do you all have any idea how I could accomplish this merge.

If you have done anything like this before and require more information please let me know.

Thank You

 

[BBaggins]

If the total number of clients is relatively small (a few thousand, perhaps) you could allocate a range of client IDs for each workstation. That way, each laptop would generate keys that wouldn't conflict with anyone else, and could be later imported into the central db directly, without any munging.

Of course, this would require that you not use counter fields, but instead use a long integer field in the laptop's database that the program gets client IDs from, then increments. When you set up each laptop, seed the counter on that machine's database with its starting ID.

 

[Regression]

BBaggins,

Thanks for your reply. I thought about this approach myself but I think it will be a little too limited. I am now looking at haveing the users create a record on the main database then write down the ClientID and EvalID assigned by this database. They could then manually enter this info on the laptop database.

This brings me to my next questions. Is it possible to update a record in another database from one database the problem I am having now is with an append.

If I try to append the information input into the laptop DB to the main database I can not becuase the value of the keys already exists there.

 

[Lightning]

You might want to look at Replication of your database.
Have a look at the Access Help topic Create a replicated database and do a keyword search here on "Replication"

From what you have said in your post, your situation is what Access replication is designed for.

HTH
Lightning