Restore Data from Backup Database

[Alastair MacFarlane]

Dear all,

I have 2 Job logging databases, one live and one backup. The Backup is
nearly a copy of the live database but contains 'posted' Jobs (i.e. jobs
appended to the backup database and deleted from the live.) The client now
states that they sometimes wish to reinstate these jobs (sometimes years
after they have been added to the backup database).

I can use a mixture of Append and delete queries to move the data back to
the 'Live' database. The problem is that the Primary Key of the table on the
one side will change so that Referential Integrity will not allow me to add
a record in the many side without an associated record in the one side. As
far as I can see the only way I can achieve my goal is to find out what the
newly created PK is and alter the records on the backup database prior to
appending them to the live database. This is messy and I wonder if I am
missing something really simple here.

Thanks again for the group's help.

Alastair MacFarlane

 

[Joseph Meehan]

Dear all,

I have 2 Job logging databases, one live and one backup. The Backup is
nearly a copy of the live database but contains 'posted' Jobs (i.e.
jobs appended to the backup database and deleted from the live.) The
client now states that they sometimes wish to reinstate these jobs
(sometimes years after they have been added to the backup database).

I can use a mixture of Append and delete queries to move the data
back to the 'Live' database. The problem is that the Primary Key of
the table on the one side will change so that Referential Integrity
will not allow me to add a record in the many side without an
associated record in the one side. As far as I can see the only way I
can achieve my goal is to find out what the newly created PK is and
alter the records on the backup database prior to appending them to
the live database. This is messy and I wonder if I am missing
something really simple here.
Thanks again for the group's help.

Alastair MacFarlane

I am not sure of the best way to solve your problem, but I do have a
suggestion to prevent it in the future.

Don't delete the old data, rather mark it inactive and use a filter to
eliminate it from queries, forms etc. Then just use that check box (or any
binary type) to indicate it is inactive.

Unless you have huge amounts of data you don't need to delete data that
might under even unexpected situations may be needed.

Then use your backup database as just that a total back up of all your
data.

 

[Alastair MacFarlane]

Dear all,

Is there any way in access that I can switch of the PrimaryKey constraint
Append data from an action query and start the PrimaryKey constraint again.

If I have a table that has:

ID (PK) - Autonumber
Field1

with values of :

ID: 1; Field1: A
ID: 2; Field1: B
ID: 4; Field1: C

How can insert an ID of 3 and any filed value into the Field1 column? If you
add another record you will get the next autonumber (5 in this case) but I
want it to be 3. Can this be achieved? You can remove the PrimaryKey
constraint, add the data but you then can't save the table.

Thanks again for any thoughts.

Alastair

 

[Alastair MacFarlane]

Joseph,

Thanks for your comments. The design approach is necessitated by volume of
data, the cost implications of going to SQL, and the speed of the system
when large recordsets are being processed.

I may have to go with this approach if the preferred method is too
cumbersome to programme.

Thanks again.

Alastair MacFarlane