Autonumbers making booking backups impossible

How do,

I have a access 2000 booking database with Personal Details, Outing Details and the Bookings in three tables:

tblBookings -- [Booking ID]-Autonumber, [Outing ID]-Number, [Child ID]-Number, [Amount Paid]-Currency, [Deposit Paid]-Yes/No, [Transport]-Yes/No

tblPersonaldetails -- [Child ID]-Autonumber, etc

tblOutingDetails -- [Outing ID]-Autonumber, etc

I am trying to restore backups made, but cannot import the bookings into the database as the [Child ID] and [Outing ID] are different than those in the imported tables due to the autonumber data type.

I use this code to firstly import the tables from the backup with the name "[Table Name]Append", delete all the data in the current tables, insert the data from the append tables into the main tables and then delete the append tables.

Expand|Select|Wrap|Line Numbers

1. Private Sub cmdImport_Click()
2. On Error GoTo DeleteAppends
3. Dim strDst, strDstPath, SQLBackuptblBookings, SQLBackuptblOutingDetails, SQLBackuptblPersonalDetails, SQLDeletetblBookingsData, SQLDeletetblOutingDetailsData, SQLDeletetblPersonalDetailsData As String
4.  
5. strDst = lstRestore
6. strDstPath = CurrentProject.Path & "\Backup\" & strDst
7.  
8.  
9. 'Import tblBookings from Selected backup and rename it tblBookingsAppend
10. If strDst <> "" Then
11. DoCmd.TransferDatabase acImport, "Microsoft Access", strDstPath, acTable, "tblBookingsBackup", "tblBookingsAppend"
12. DoCmd.TransferDatabase acImport, "Microsoft Access", strDstPath, acTable, "tblOutingDetailsBackup", "tblOutingDetailsAppend"
13. DoCmd.TransferDatabase acImport, "Microsoft Access", strDstPath, acTable, "tblPersonalDetailsBackup", "tblPersonalDetailsAppend"
14. Else
15. msgbox "Please select a Backup to Restore from", , "Select a Backup"
16. Exit Sub
17. End If
18.  
19. If msgbox("Do you want to restore", vbYesNo, "Restore?") = vbYes Then
20. DoCmd.SetWarnings (False)
21. SQLDeletetblBookingsData = "Delete * FROM tblBookings"
22. SQLDeletetblOutingDetailsData = "Delete * FROM tblOutingDetails"
23. SQLDeletetblPersonalDetailsData = "Delete * FROM tblPersonalDetails"
24.  
25. SQLBackuptblBookings = "INSERT INTO tblBookings ([Outing ID], [Child ID], [Amount Paid], [Deposit Paid], [Transport]) SELECT [Outing ID], [Child ID], [Amount Paid], [Deposit Paid], [Transport] FROM tblBookingsAppend"
26. SQLBackuptblOutingDetails = "INSERT INTO tblOutingDetails ([Outing Title], [Description], [Age Range], [Date From], [Date To], [Available Places], [Telephone], [E-mail Address], [Address], [Post Code], [Method of Transport], [Cost], [Deposit], [Booking Deadline], [Payment Deadline]) SELECT [Outing Title], [Description], [Age Range], [Date From], [Date To], [Available Places], [Telephone], [E-mail Address], [Address], [Post Code], [Method of Transport], [Cost], [Deposit], [Booking Deadline], [Payment Deadline] FROM tblOutingDetailsAppend"
27. SQLBackuptblPersonalDetails = "INSERT INTO tblPersonalDetails (Surname, Forename, [Date of Birth], Address, [Post Code], [Home Number], [Mobile Number], [E-Mail Address], [Medical Issues], [Dietary Issues]) SELECT Surname, Forename, [Date of Birth], Address, [Post Code], [Home Number], [Mobile Number], [E-Mail Address], [Medical Issues], [Dietary Issues] FROM tblPersonalDetailsAppend"
28.  
29.  
30. DoCmd.RunSQL SQLDeletetblBookingsData
31. DoCmd.RunSQL SQLDeletetblOutingDetailsData
32. DoCmd.RunSQL SQLDeletetblPersonalDetailsData
33.  
34. DoCmd.RunSQL SQLBackuptblOutingDetails
35. DoCmd.RunSQL SQLBackuptblPersonalDetails
36. DoCmd.RunSQL SQLBackuptblBookings
37.  
38. DoCmd.SetWarnings (True)
39. msgbox "Restore Complete", , "Restore Complete"
40. End If
41.  
42. 'Delete tblBookingsAppend
43. DoCmd.DeleteObject acTable, "tblBookingsAppend"
44. DoCmd.DeleteObject acTable, "tblOutingDetailsAppend"
45. DoCmd.DeleteObject acTable, "tblPersonalDetailsAppend"
46.  
47. lstRestore.Requery
48. Exit Sub
49.  
50. DeleteAppends:
51. msgbox Err.Description
52. DoCmd.DeleteObject acTable, "tblBookingsAppend"
53. DoCmd.DeleteObject acTable, "tblOutingDetailsAppend"
54. DoCmd.DeleteObject acTable, "tblPersonalDetailsAppend"
55. lstRestore.Requery
56. Exit Sub
57.  
58. End Sub

My problem is that once the backup data has been copied over, the autonumber ID fields dont match the ID fields used in the Booking table, so they are not copied over due to Key Violations.

I have thought about this for a while now and have no idea how to resolve the situation.

All help is appreciated,

NDayave

What are the exact Relationships among the 3 Tables? It seems like:
tblPersonalDetails.[Child ID](1) ==> tblBookings.[Child ID](Many)
tblOutgoingDetails.[Outing ID](1) ==> tblBookings.[Outing ID](Many)
The problem may be with the Append Sequence as opposed to different IDs.

Yeah, those are all the relationships there are.

whats happening is:

restoring old data with a Child ID of 3
in bookings table the booking is therefore under Child ID of 3

Because you cannot (to my knowledge) change autonumbers, the imported Data is given a new Child ID: 5
The imported booking table however, still holds a Child ID of 3, which is now either non-existant or just wrong. This then stops the import of the booking due to key violations.


On a side note, do you know how to set a text box with an input mask to always start at the left no matter where you click in the text box. Its a little problem but it annoys me.

Thanks,

NDayave

if this is a split database you can have the back up replace the entire backend as a whole rather than a table at a time. that way would not disturb the relationships or autonumbers.

if this is a split database you can have the back up replace the entire backend as a whole rather than a table at a time. that way would not disturb the relationships or autonumbers.

By split database i have no idea what you mean. The backups are held in seperate dated databases, and imported in to the main database when restored.

How would you go about replacing the entire 'Backend'?

Cheers

NDayave

Yeah, those are all the relationships there are.

whats happening is:

restoring old data with a Child ID of 3
in bookings table the booking is therefore under Child ID of 3

Because you cannot (to my knowledge) change autonumbers, the imported Data is given a new Child ID: 5
The imported booking table however, still holds a Child ID of 3, which is now either non-existant or just wrong. This then stops the import of the booking due to key violations.


On a side note, do you know how to set a text box with an input mask to always start at the left no matter where you click in the text box. Its a little problem but it annoys me.

Thanks,

NDayave

On the side note:
__1 Set the Text Alignment of the Field to Left via: Format ==> Text Align ==> Left.
__2 You can also set an Exclamation Point (!) in the Format Property to force Left Alignment of values.
__3 The other issue is more complicated, but I am working on it. Please be patient.

On the side note:
__1 Set the Text Alignment of the Field to Left via: Format ==> Text Align ==> Left.
__2 You can also set an Exclamation Point (!) in the Format Property to force Left Alignment of values.
__3 The other issue is more complicated, but I am working on it. Please be patient.

I think I have a solution for your Primary Problem but I'm afraid it's a little confusing. I will keep an eye on this Post and see if someone comes up with a better idea.

MSeda touched on this point, but you cannot restore half a set of linked data and hope for it to make sense.
You need to make clear why you are trying to restore the booking data without the related (Child) data being restored at the same time.
Aside from that, when restoring linked data, it is necessary to rebuild the data which links to the related table(s). In this case, it is not enough simply to restore the tblBooking table. You must rebuild it from the existing data. It seems you need to re-engineer your backup process if you need to save simply the bookings. It is a restriction of AutoNumber fields that you cannot put a value into them so, thinking about it you'll probably need to re-engineer your backup AND restore procedures anyway.
When Backing up, you must include all relevant data with the booking. Any links to AutoNumber fields must be padded out with data uniquely identifying the item without reliance on the autonumber field. This can be done without successive data overhead if they are stored in a separate table in the backup too, but they must be saved somehow.
On restore, the linked item (Child) should be determined from the backup data prior to creating a new link in the live database.

I think I have a solution for your Primary Problem but I'm afraid it's a little confusing. I will keep an eye on this Post and see if someone comes up with a better idea.

Hit me with it, all help is appreciated.

Provided it works, i dont care if it takes an age to understand, I believe they call it learning...

Thanks,

NDayave

MSeda touched on this point, but you cannot restore half a set of linked data and hope for it to make sense.
You need to make clear why you are trying to restore the booking data without the related (Child) data being restored at the same time.
Aside from that, when restoring linked data, it is necessary to rebuild the data which links to the related table(s). In this case, it is not enough simply to restore the tblBooking table. You must rebuild it from the existing data. It seems you need to re-engineer your backup process if you need to save simply the bookings. It is a restriction of AutoNumber fields that you cannot put a value into them so, thinking about it you'll probably need to re-engineer your backup AND restore procedures anyway.
When Backing up, you must include all relevant data with the booking. Any links to AutoNumber fields must be padded out with data uniquely identifying the item without reliance on the autonumber field. This can be done without successive data overhead if they are stored in a separate table in the backup too, but they must be saved somehow.
On restore, the linked item (Child) should be determined from the backup data prior to creating a new link in the live database.

Sorry to cause confusion, all the data in all 3 tables is being restored from a backup. The problem is that the auto number asigns the imported Child ID in the Personal Details table a new number, so the imported value in the Bookings table no longer corresponds.

On the side note:
__1 Set the Text Alignment of the Field to Left via: Format ==> Text Align ==> Left.
__2 You can also set an Exclamation Point (!) in the Format Property to force Left Alignment of values.
__3 The other issue is more complicated, but I am working on it. Please be patient.

What i mean on this issue is that when you click in the text box, because of the input mask the cursar stays where you clicked:

eg: __/_|_/__ where ' | ' is the cursar

what i want is that on clicking the box, the cursar is moved to the left automatically:

eg: |__/__/__ where ' | ' is the cursar (still)

when i showed the database to my client, he said that it is one of those little things that would annoy him a lot, as it does with me.

Thanks,

NDayave

Sorry to cause confusion, all the data in all 3 tables is being restored from a backup. The problem is that the auto number asigns the imported Child ID in the Personal Details table a new number, so the imported value in the Bookings table no longer corresponds.

Did you read through (and understand) my whole post (#8)? It touches on the fundamental problem.
I'm pleased to hear you're backing up the whole (consistent) set of data. However, dealing with AutoNumber fields has to be handled as I specified.
As you have the whole set available in your backup, you should have everything you need to hand. It's more than a simple set of append queries though, I'm afraid.

Did you read through (and understand) my whole post (#8)? It touches on the fundamental problem.
I'm pleased to hear you're backing up the whole (consistent) set of data. However, dealing with AutoNumber fields has to be handled as I specified.
As you have the whole set available in your backup, you should have everything you need to hand. It's more than a simple set of append queries though, I'm afraid.

How do i go about 'Padding Out' with unique data though? I understand what youre saying, i just dont know how to do any of it.

NDayave

How do i go about 'Padding Out' with unique data though? I understand what youre saying, i just dont know how to do any of it.

NDayave

The backup data, of which you have all, has valid links within itself.
Using these valid links, find the data that the record should be linking to. When you know this, find the matching record of the restored database and place the AutoNumber PK of this record in the restored FK field.
Does that help you to understand. It's not very straightforward, I know, but the concept is important to understand if you're to go forward with this.

You could, of course, just use VBA code to duplicate a complete Copy / Paste of the tables which would probably work, but if you want to learn, I'd take the more controlled and flexible approach outlined in the previous paragraph.

How do,

I have a access 2000 booking database with Personal Details, Outing Details and the Bookings in three tables:

tblBookings -- [Booking ID]-Autonumber, [Outing ID]-Number, [Child ID]-Number, [Amount Paid]-Currency, [Deposit Paid]-Yes/No, [Transport]-Yes/No

tblPersonaldetails -- [Child ID]-Autonumber, etc

tblOutingDetails -- [Outing ID]-Autonumber, etc

I am trying to restore backups made, but cannot import the bookings into the database as the [Child ID] and [Outing ID] are different than those in the imported tables due to the autonumber data type.

I use this code to firstly import the tables from the backup with the name "[Table Name]Append", delete all the data in the current tables, insert the data from the append tables into the main tables and then delete the append tables.

Expand|Select|Wrap|Line Numbers

1. Private Sub cmdImport_Click()
2. On Error GoTo DeleteAppends
3. Dim strDst, strDstPath, SQLBackuptblBookings, SQLBackuptblOutingDetails, SQLBackuptblPersonalDetails, SQLDeletetblBookingsData, SQLDeletetblOutingDetailsData, SQLDeletetblPersonalDetailsData As String
4.  
5. strDst = lstRestore
6. strDstPath = CurrentProject.Path & "\Backup\" & strDst
7.  
8.  
9. 'Import tblBookings from Selected backup and rename it tblBookingsAppend
10. If strDst <> "" Then
11. DoCmd.TransferDatabase acImport, "Microsoft Access", strDstPath, acTable, "tblBookingsBackup", "tblBookingsAppend"
12. DoCmd.TransferDatabase acImport, "Microsoft Access", strDstPath, acTable, "tblOutingDetailsBackup", "tblOutingDetailsAppend"
13. DoCmd.TransferDatabase acImport, "Microsoft Access", strDstPath, acTable, "tblPersonalDetailsBackup", "tblPersonalDetailsAppend"
14. Else
15. msgbox "Please select a Backup to Restore from", , "Select a Backup"
16. Exit Sub
17. End If
18.  
19. If msgbox("Do you want to restore", vbYesNo, "Restore?") = vbYes Then
20. DoCmd.SetWarnings (False)
21. SQLDeletetblBookingsData = "Delete * FROM tblBookings"
22. SQLDeletetblOutingDetailsData = "Delete * FROM tblOutingDetails"
23. SQLDeletetblPersonalDetailsData = "Delete * FROM tblPersonalDetails"
24.  
25. SQLBackuptblBookings = "INSERT INTO tblBookings ([Outing ID], [Child ID], [Amount Paid], [Deposit Paid], [Transport]) SELECT [Outing ID], [Child ID], [Amount Paid], [Deposit Paid], [Transport] FROM tblBookingsAppend"
26. SQLBackuptblOutingDetails = "INSERT INTO tblOutingDetails ([Outing Title], [Description], [Age Range], [Date From], [Date To], [Available Places], [Telephone], [E-mail Address], [Address], [Post Code], [Method of Transport], [Cost], [Deposit], [Booking Deadline], [Payment Deadline]) SELECT [Outing Title], [Description], [Age Range], [Date From], [Date To], [Available Places], [Telephone], [E-mail Address], [Address], [Post Code], [Method of Transport], [Cost], [Deposit], [Booking Deadline], [Payment Deadline] FROM tblOutingDetailsAppend"
27. SQLBackuptblPersonalDetails = "INSERT INTO tblPersonalDetails (Surname, Forename, [Date of Birth], Address, [Post Code], [Home Number], [Mobile Number], [E-Mail Address], [Medical Issues], [Dietary Issues]) SELECT Surname, Forename, [Date of Birth], Address, [Post Code], [Home Number], [Mobile Number], [E-Mail Address], [Medical Issues], [Dietary Issues] FROM tblPersonalDetailsAppend"
28.  
29.  
30. DoCmd.RunSQL SQLDeletetblBookingsData
31. DoCmd.RunSQL SQLDeletetblOutingDetailsData
32. DoCmd.RunSQL SQLDeletetblPersonalDetailsData
33.  
34. DoCmd.RunSQL SQLBackuptblOutingDetails
35. DoCmd.RunSQL SQLBackuptblPersonalDetails
36. DoCmd.RunSQL SQLBackuptblBookings
37.  
38. DoCmd.SetWarnings (True)
39. msgbox "Restore Complete", , "Restore Complete"
40. End If
41.  
42. 'Delete tblBookingsAppend
43. DoCmd.DeleteObject acTable, "tblBookingsAppend"
44. DoCmd.DeleteObject acTable, "tblOutingDetailsAppend"
45. DoCmd.DeleteObject acTable, "tblPersonalDetailsAppend"
46.  
47. lstRestore.Requery
48. Exit Sub
49.  
50. DeleteAppends:
51. msgbox Err.Description
52. DoCmd.DeleteObject acTable, "tblBookingsAppend"
53. DoCmd.DeleteObject acTable, "tblOutingDetailsAppend"
54. DoCmd.DeleteObject acTable, "tblPersonalDetailsAppend"
55. lstRestore.Requery
56. Exit Sub
57.  
58. End Sub

My problem is that once the backup data has been copied over, the autonumber ID fields dont match the ID fields used in the Booking table, so they are not copied over due to Key Violations.

I have thought about this for a while now and have no idea how to resolve the situation.

All help is appreciated,

NDayave

Before starting, I must explicitly state 2 items up front:
__1 NeoPa's approach to this dilemma is the correct one, this is just a very confusing work-a-round.
__2 This explanation does not come with a money-back-guarantee. I did not have time to thoroughly test the logic, but in reality it should work. Hold on to your hat:
_____A. Take note of the last [Booking ID] in tblBookings. This is a critical step and will be used later on. It can be automated but let's keep everything manual until we see if it all works out.
_____B. Prior to Appending the data, create a new Field in tblPersonalDetails called [Child ID Append](LONG). This will retain the original Child ID for the Linking and Update process. Append all Records from tblPersonalDetailsAppend to tblPersonalDetails. Append tblPersonalDetailsAppend.[Child ID] to tblPersonalDetails.[Child ID Append]. The AutoNumbers will automatically generate themselves and later establish the new Link to the appended data in tblBookings.
_____C. Create a new Field in tblBookings called [Child ID Booking](LONG). Append all Records in tblBookingsAppend to tblBookings. Append tblBookingsAppend.[Child ID] to tblBookings.[Child ID Booking]. Again, the AutoNumbers([Booking ID]) will generate themselves.
_____D. Create a Query with tblBookings and tblPersonalDetails.
_____E. Drop the [Booking ID] Field from tblBookings into the Grid and set the Criteria of this Field to the last Booking ID obtained from Step A.
_____F. Delete the automatically created Link between the 2 Tables if it exists.
_____G. Manually create a Link between the [Child ID Append] Field in tblPersonalDetails to the [Child ID Booking] Field in tblBookings.All data should have successfully been appendd to both Tables up to this point and the old Relationships are still in tack via the newly created Fields.
_____H. All that remains is to Update the [Child ID] Field in tblBookings to that of the AutoNumbers in tblPersonalDetails..
_____I. Drop the [Child ID] Field from tblBookings and the [Child ID] Field from tblPersonalDetails into the Query Grid. Change from a SELECT to an UPDATE Query and Update [Child ID] in tblBookings to the [Child ID] Field in tblPersonalDetails. In the Update To cell of the [Child ID] Field in tblBookings the criteria should be: tblPersonalDetails.[Child ID].
_____J. Repeate the exact process with similar Field Names for the tblOutgoiongDetails Table. Good Luck and let me know how you make out.

To answer you question about a split database.
A split data base is when the tables are saved in one database and the forms, queries,reports and modules are in a separate database. The tables are accessed via 'linked tables'. because the data is in a database by itself backups include only the data and not the forms and queries etc. That will not change frequently post-development. Additionally this backend database can be replaced in entirity when need be so records will not be changed at all and relationships that may prevent you from cutting and pasting tables will not be disturbed.
Anyway heres a link I found in another thread that goes into way more depth on the topic. http://www.members.shaw.ca/AlbertKal...plit/index.htm

I think NeoPa's explanation of how the FKs remain the same while the PKs change after the restores pretty much clarify the cause of the problem.
As for a solution, ADezii's looks like a good temporary solution, however you probably want to consider designing you database that allows for restores to be performed easily by users when neccesary.

The cursor issue would probably be better to post in a separate thread.

Table Name=tblBookings
Table Name=tblPersonalDetails
Table Name=tblOutingDetails
If your existing tables are as described in post #1, then you will need new tables for your Backup set (We will call them tblBBup; tblPDBup & tblODBup respectively). You should also have data in the reference tables (tblPersonalDetails and tblOutingDetails), that uniquely identifies each record (Other than the PKs). I've guessed at some fields for these, but yours may well be different.

1. Clear existing data from your main tables (In order shown).
   
   Expand|Select|Wrap|Line Numbers

   1. DELETE
   2. FROM tblBookings;
   3. DELETE
   4. FROM tblPersonalDetails;
   5. DELETE
   6. FROM tblOutingDetails;
2. Rebuild 'Reference' tables.
   
   Expand|Select|Wrap|Line Numbers

   1. INSERT INTO tblPersonalDetails([ChildName],...)
   2. SELECT [ChildName],...
   3. FROM tblPDBup;
   4. INSERT INTO tblOutingDetails([OutingName],...)
   5. SELECT [OutingName],...
   6. FROM tblODBup;
   You will need to fill out the field names in both cases. * cannot be used as the AutoNumber field cannot be written to explicitly.
3. The complicated part of the restore.
   Using the [ChildName] and [OutingName] fields from the backups, link in the new reference tables and pick up their (new) AutoNumber PK fields to put the the FK fields of tblBookings.
   
   Expand|Select|Wrap|Line Numbers

   1. INSERT INTO tblBookings([Outing ID],
   2.                         [Child ID],
   3.                         [Amount Paid],
   4.                         [Deposit Paid],
   5.                         [Transport])
   6. SELECT tblOutingDetails.[Outing ID],
   7.        tblPersonalDetails.[Child ID],
   8.        tblBBup.[Amount Paid],
   9.        tblBBup.[Deposit Paid],
   10.        tblBBup.[Transport]
   11. FROM (tblBBup INNER JOIN
   12.      (tblOBup INNER JOIN tblOutingDetails
   13.      ON tblOBup.OutingName=tblOutingDetails.OutingName)
   14.      ON tblBBup.[Outing ID]=tblOBup.[Outing ID]) INNER JOIN
   15.      (tblPBup INNER JOIN tblPersonalDetails
   16.      ON tblPBup.ChildName=tblPersonalDetails.ChildName)
   17.      ON tblBBup.[Child ID]=tblPBup.[Child ID];

This should do the whole job for you, but you need to study it carefully to understand exactly why it's working the way it is.

Please let me know if this (last) post clears up any misunderstandings and explains clearly enough what you need to do?

Please let me know if this (last) post clears up any misunderstandings and explains clearly enough what you need to do?

I understand completely, it makes so much sense.

Once again you guys have got me out of a bit oof a pickle, you cant imagine how gtateful i am.

Thanks so much,

NDayave

I'm very pleased that's cleared things up for you.
Good luck with your project :)

I'm very pleased that's cleared things up for you.
Good luck with your project :)

I appreciate the help a lot, and the code has worked not only on this table, but also another. The third time i tried this however, produced an error, despite doing the same thing. I posted the problem a few days ago with no replies, so I was wondering if you could have a look at it for me?

SQL Restore code wont work in different tables, despite doing the same thing

Thanks anyway,

NDayave

Ignore that, its all figured out

NDayave

I had a quick look, but your last message stopped me putting too much time into it :)
If you get a second, can you post in the other thread the resolution you found (Even if it was simply to fix a typo or something), that way any members will know not to waste time posting.

Don't worry about posting in the other one unless you wish to.
I've added a quick note with a link to here which should do the trick.

Thanks for looking anyway.

Yeah, the problem was that the Child ID and Outing ID fields were text fields rather than number fields.

Which would explain the type mismatch error

Thanks,


NDayave