Autonumbers making booking backups impossible

[NDayave]

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

tblPersonaldeta ils -- [Child ID]-Autonumber, etc

tblOutingDetail s -- [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.

Private Sub cmdImport_Click()
On Error GoTo DeleteAppends
Dim strDst, strDstPath, SQLBackuptblBookings, SQLBackuptblOutingDetails, SQLBackuptblPersonalDetails, SQLDeletetblBookingsData, SQLDeletetblOutingDetailsData, SQLDeletetblPersonalDetailsData As String
 
strDst = lstRestore
strDstPath = CurrentProject.Path & "\Backup\" & strDst
 
 
'Import tblBookings from Selected backup and rename it tblBookingsAppend
If strDst <> "" Then
DoCmd.TransferDatabase acImport, "Microsoft Access", strDstPath, acTable, "tblBookingsBackup", "tblBookingsAppend"
DoCmd.TransferDatabase acImport, "Microsoft Access", strDstPath, acTable, "tblOutingDetailsBackup", "tblOutingDetailsAppend"
DoCmd.TransferDatabase acImport, "Microsoft Access", strDstPath, acTable, "tblPersonalDetailsBackup", "tblPersonalDetailsAppend"
Else
msgbox "Please select a Backup to Restore from", , "Select a Backup"
Exit Sub
End If
 
If msgbox("Do you want to restore", vbYesNo, "Restore?") = vbYes Then
DoCmd.SetWarnings (False)
SQLDeletetblBookingsData = "Delete * FROM tblBookings"
SQLDeletetblOutingDetailsData = "Delete * FROM tblOutingDetails"
SQLDeletetblPersonalDetailsData = "Delete * FROM tblPersonalDetails"
 
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"
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"
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"
 
 
DoCmd.RunSQL SQLDeletetblBookingsData
DoCmd.RunSQL SQLDeletetblOutingDetailsData
DoCmd.RunSQL SQLDeletetblPersonalDetailsData
 
DoCmd.RunSQL SQLBackuptblOutingDetails
DoCmd.RunSQL SQLBackuptblPersonalDetails
DoCmd.RunSQL SQLBackuptblBookings
 
DoCmd.SetWarnings (True)
msgbox "Restore Complete", , "Restore Complete"
End If
 
'Delete tblBookingsAppend
DoCmd.DeleteObject acTable, "tblBookingsAppend"
DoCmd.DeleteObject acTable, "tblOutingDetailsAppend"
DoCmd.DeleteObject acTable, "tblPersonalDetailsAppend"
 
lstRestore.Requery
Exit Sub
 
DeleteAppends:
msgbox Err.Description
DoCmd.DeleteObject acTable, "tblBookingsAppend"
DoCmd.DeleteObject acTable, "tblOutingDetailsAppend"
DoCmd.DeleteObject acTable, "tblPersonalDetailsAppend"
lstRestore.Requery
Exit Sub
 
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

[ADezii]

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

tblPersonaldeta ils -- [Child ID]-Autonumber, etc

tblOutingDetail s -- [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.

Private Sub cmdImport_Click()
On Error GoTo DeleteAppends
Dim strDst, strDstPath, SQLBackuptblBookings, SQLBackuptblOutingDetails, SQLBackuptblPersonalDetails, SQLDeletetblBookingsData, SQLDeletetblOutingDetailsData, SQLDeletetblPersonalDetailsData As String
 
strDst = lstRestore
strDstPath = CurrentProject.Path & "\Backup\" & strDst
 
 
'Import tblBookings from Selected backup and rename it tblBookingsAppend
If strDst <> "" Then
DoCmd.TransferDatabase acImport, "Microsoft Access", strDstPath, acTable, "tblBookingsBackup", "tblBookingsAppend"
DoCmd.TransferDatabase acImport, "Microsoft Access", strDstPath, acTable, "tblOutingDetailsBackup", "tblOutingDetailsAppend"
DoCmd.TransferDatabase acImport, "Microsoft Access", strDstPath, acTable, "tblPersonalDetailsBackup", "tblPersonalDetailsAppend"
Else
msgbox "Please select a Backup to Restore from", , "Select a Backup"
Exit Sub
End If
 
If msgbox("Do you want to restore", vbYesNo, "Restore?") = vbYes Then
DoCmd.SetWarnings (False)
SQLDeletetblBookingsData = "Delete * FROM tblBookings"
SQLDeletetblOutingDetailsData = "Delete * FROM tblOutingDetails"
SQLDeletetblPersonalDetailsData = "Delete * FROM tblPersonalDetails"
 
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"
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"
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"
 
 
DoCmd.RunSQL SQLDeletetblBookingsData
DoCmd.RunSQL SQLDeletetblOutingDetailsData
DoCmd.RunSQL SQLDeletetblPersonalDetailsData
 
DoCmd.RunSQL SQLBackuptblOutingDetails
DoCmd.RunSQL SQLBackuptblPersonalDetails
DoCmd.RunSQL SQLBackuptblBookings
 
DoCmd.SetWarnings (True)
msgbox "Restore Complete", , "Restore Complete"
End If
 
'Delete tblBookingsAppend
DoCmd.DeleteObject acTable, "tblBookingsAppend"
DoCmd.DeleteObject acTable, "tblOutingDetailsAppend"
DoCmd.DeleteObject acTable, "tblPersonalDetailsAppend"
 
lstRestore.Requery
Exit Sub
 
DeleteAppends:
msgbox Err.Description
DoCmd.DeleteObject acTable, "tblBookingsAppend"
DoCmd.DeleteObject acTable, "tblOutingDetailsAppend"
DoCmd.DeleteObject acTable, "tblPersonalDetailsAppend"
lstRestore.Requery
Exit Sub
 
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:
tblPersonalDeta ils.[Child ID](1) ==> tblBookings.[Child ID](Many)
tblOutgoingDeta ils.[Outing ID](1) ==> tblBookings.[Outing ID](Many)
The problem may be with the Append Sequence as opposed to different IDs.

[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

[MSeda]

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.

[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.

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

[ADezii]

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.

[ADezii]

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.

[NeoPa]

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.

[NDayave]

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

[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.