2 Access Databases - need to combine records..how?

  Sir Radfordin 10:46 19 Jan 2004

Looking for some inspiration on this one.

Have got an access database being used in two locations (seperated by distance so network link isn't an option). Both sites are creating and accessing data in all tables.

Tables (for example) are Customer (primary key = CustID (Autonumber) and Car (primary key = CarID (Autonumber) with Forign Key = CustID (Number).

Tables are then represted in forms which are linked master/child on CustID. This bit all works fine. (There are 3 other tables that link on CustID.)

However if you want to then merge from DB1 to DB2 all Customers and Cars the Autonumbers which link them may already exist.

Had thought of creating a unique field on each table that would be Surname&RegNumber (or similar) that would then link all the cars/customers. However this will not then feed the right data into the forms on DB2 after being imported as they would still link on CustID.

Any ideas?

Hope that made some sense!

  stlucia 13:38 19 Jan 2004

Just a thought -- surname®number might not give a unique result because there might just happen to be a Smith with number 999 in each table.

If you're thinking along those lines, it would have to be "A" or "B" (depending on the table) plus regnumber. Could you then truncate the combined reference (take off the first character) when using it in existing forms?

  stlucia 13:40 19 Jan 2004

... that was meant to be surname-ampersand-number.

Surname&number -- just typed it again to see what comes out!

  Sir Radfordin 14:06 19 Jan 2004

I get your point about it not being unique, but it would have been a combination of Surname+Car Registration Number so the chance of one dealer selling the same car twice to a person of the same surname is very rare!

What I really need is to find a way to change how Autonumber works so you could run two sequences but can't see a way of doing that without doing it manually.

  stlucia 15:24 19 Jan 2004

I can't see any way to do what you want if the two versions of the database can't talk to each other, except by perhaps forcing one of them to start numbering at, say 1,000,000, by manually putting a dummy record in with an i.d. of 1,000,000.

  Sir Radfordin 15:37 19 Jan 2004

The problem with doing that is that you then can't add in previous records! It's all a bit annoying.

  stlucia 16:13 19 Jan 2004

True ... I think a cup of tea is called for!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Is this the future of VR and AR?

Best iPad buying guide 2017

Comment regarder le Bureau des L├ęgendes en ligne ?