Made changes to a copy of the Back End, they added new records - problems!

[Sharky II]

Hi guys, sorry my title wasn't very clear.

I took a copy of the db's front and back ends. There were problems with the back end so i had to make changes (offline, on my own machine). This took 2 days. I was away for a couple of days. The DB was still running and the staff added new records (only about 12!), and made changes to some other customers' details.

I've made quite a few changes to both the front end, but mainly to the back end, as some tables must have been corrupt.

Is there any way on earth i can import the new 'structure' of the back end onto the latest (today's) version of the db (i've told them not to use it until friday) and carry on working from the one with the up to date records?

Or do i have to redo 2 days work?! I tried to go to get external data -> import -> definitions only but i'm not really sure what's happening. I want to replace the existing tables with new ones (ie new relationships and structures), but keep the records! It seems to just add the old tables.

Cheers, sorry for making a meal of the explanation!

Edd

 

[KenHigg]

It's not going to take you 2 days to just add the 12 new records to the new db is it?

 

[Sharky II]

hi,

no of course not!

but unfortunately they say they have made changes to current records, and they don't know which?

i *DO* have a copy of both. i tried just copy & pasting stuff but i got quite a few errors.

Thanks ken

 

[KenHigg]

Hum.. And I guess trying to map changes from the old tables to the new tables and the buiding append queries is unacceptable?

 

[Pat Hartman]

Start with an empty db. Import the structure from the one where you changed the structure. Make sure that the primary keys are properly defined and that the relationships are correct. Then link the tables from the one where you want to get the data. Create append queries that append data from the linked tables to the imported structures. Make sure that you run the appends in the correct order so that RI can be enforced.

 

[Sharky II]

thanks guys.

pat, i'm going to bookmark this thread and carry on making changes to the old back end, then when i'm all finished, i'll do as you said! i'll let you know how it goes

thanks guys

 

[Sharky II]

hi

can you guys please let us know what you mean by append queries? you mean INSERT INTO etc? wouldn't this take forever?

how can i import the data from the db with the unwanted structure?

i've also added new tables and stuff. The overall structure of the front and backends has grown, but the old records still remain a subset of the new db.

 

[Pat Hartman]

can you guys please let us know what you mean by append queries? you mean INSERT INTO etc? wouldn't this take forever?

Append queries take one of two forms. The form usually referenced here is the Insert .... Select form where you select rows from one table/query and append them to another table. To make one:
1. Create a new query
2. Select the source table or query
3. Select all the fields that you want from the source table or query
4. Change the query type to Append
5. Choose the target query
6. For all matching column names, Access will automatically populate the AppendTo cell. For the other source columns, you can choose the append to column name
7. Save the query
8. Run it

 

[Sharky II]

that's fantastic!

thanks a lot for that mate. now i just gotta make sure i do them in the right order! There are infact two backends i need to make into one, so i better grab a cup of coffee!

Cheers

 

[Sharky II]

Hi

Just a quick question while i install office on this new machine: If i have to 'merge' 2 database back ends, if i append 1 table, then append another, will it overwrite the data for similar records, or will it ignore it?

What i mean is, if there are two db's, database 'a' and database 'b', and there is a record for a customer, which is the same customer but has changed the address (in b), what happens when you then copy all the records from 'b' over?

I hope you guys get what i mean Does it overwrite records or does it ignore them if they are the same... what if it's the same 'record' (ie it is customer 31, john smith in both db A and db B) but with something like a different address. What happens?

Many thanks guys

 

[ReAn]

after the append, it'll report x many records werent added because of indexing errors or something like that... IF the tables have unique ID's and it's set as a key field. This is asuming one person has the same ID in both tables too.

 

[Sharky II]

hi

when i tried that on my computer at work today, i did indeed get those 'errors'

does this mean that it DOESN'T overwrite any data with the same primary key?

many thanks

 

[Sharky II]

hm, it seems it will NOT overwrite/modify records that already exist as their primary keys exist etc - like you guys said. Is there any way of getting around this?

Sorry to post so much and be so annoying

 

[Pat Hartman]

You can use an update query rather than an append query. The technique is very useful - it updates existing records and adds new ones. Look up this post:

Combination Update/Append in same query