Access question

[Jay-Aim]

Here is the situation

I have an access database (let's call it db1) with a table containing let's say customer data and it has 10 customers. I want to set up a second database (named db2) which will have the same table with the same data to start with. Then I want to change some of that data against each customer. Easy so far

And here is the problem
Moving forward in time, there are now 15 customers in db1.
I then want to re-import that table (or refresh if such a thing exist) from db1 into db2 without alteration for the first 10 records so that it only imports the new records, in other words import from index 11 onwards.


Possible?

[Jinx]

Probably not understaning the question properly. Can't you just highlight the records in the customer table in DB1 that you wish to copy; copy and paste into the the customer table in DB2?

[scruffy]

Don't you want the oik reading DB2 to see the other record, or is it on another computer, lets call it PC2...
Either way, yes there are ways!
Even on Mac's!

[miniman]

INSERT INTO tblTable1 ( idCustID, strCustomer )
SELECT tblTable2.idCustID, tblTable2.strCustomer
FROM tblTable2 LEFT JOIN tblTable1 ON tblTable2.idCustID = tblTable1.idCustID
WHERE (((tblTable1.idCustID) Is Null));

[Plotloss]

So each time you lift you only want new records in the next lift?

Just store the last read key in another table in DB1

Then when the process lifts to DB2 modify that to start from the last used key + 1

Assuming the primary key of table 1 in DB1 is a enumerated field.

[miniman]

You would need to use linked tables to make my syntax work, BTW.

[Jay-Aim]

miniman said:

You would need to use linked tables to make my syntax work, BTW.

can't have linked tables as it would modify the tables in db1

This database has over 250 tables so it needs a quick easy to use solution.

[Jay-Aim]

scruffy said:

Don't you want the oik reading DB2 to see the other record, or is it on another computer, lets call it PC2... Either way, yes there are ways! Even on Mac's!

both would be on a company server

[Jay-Aim]

Basically I'm trying to create a copy of a database so that its static info may be changed in a different language as different users will have different nationality yet working from the same software.

So the reports in English would point to one database and the reports in language 2 to the second database


As the dynamic data grows or new static data gets entered, then I only those new bits to come accross

>> Edited by Jay-Aim on Thursday 21st April 10:35

[pdV6]

Jay-Aim said:

can't have linked tables as it would modify the tables in db1

Only if you tell it to!

Jay-Aim said:

This database has over 250 tables so it needs a quick easy to use solution.

If its quick & easy you're after, then linked tables and an appropriate query (as suggested above) are the way to go.

[Plotloss]

Are you not putting the cart before the horse here?

[Jay-Aim]

pdV6 said:

Jay-Aim said: can't have linked tables as it would modify the tables in db1 Only if you tell it to! Jay-Aim said: This database has over 250 tables so it needs a quick easy to use solution. If its quick & easy you're after, then linked tables and an appropriate query (as suggested above) are the way to go.

How do I stop it then please?

[Jay-Aim]

Plotloss said:

Are you not putting the cart before the horse here?

Hence the 'possible?' in my original post

[Plotloss]

I am more commenting on creating an entirely new DB for presentation reasons.

Presumably if you can change the information record by record in the other national language DB then you can do that work in the report post data read?

[pdV6]

Jay-Aim said:

How do I stop it then please?

The query as suggested earlier only selects data from the source table. Nothing is written back to it unless you were to decide to write another query that did so. Unless there's something I'm missing here?

[markbarton]

Jay, I think you're missing the fact that you'll need a copy of the original table as well as the linked table.

db1 would have tblTable1, and db2 would have tblTable2 (local table containing the translated data) and a link to tblTable1 in db1. Miniman's query (which you would have in db2) would then append any data that appears in tblTable1 but not in tblTable2 to tblTable2 where it can be edited without any impact on tblTable1.

Email me and I'll reply with an example if you like.

Edited to remove email address

>> Edited by markbarton on Thursday 21st April 12:38

[Jay-Aim]

Right

Thanks all

might need a re-think....