By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,745 Members | 1,873 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,745 IT Pros & Developers. It's quick & easy.

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

P: 92
How do,

A while ago i had the problem of backing up a Access 2002 table with unique data that changed in some tables but not others, resulting in restore failure. This was sorted by NeoPa with the (Modified) SQL code:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblBookings ([Outing ID],
  2.                             [Child ID],
  3.                             [Amount Paid],
  4.                             [Deposit Paid],
  5.                             Transport ) 
  7. SELECT tblOutingDetails.[Outing ID],
  8.               tblPersonalDetails.[Child ID],
  9.               tblBookingsAppend.[Amount Paid],
  10.               tblBookingsAppend.[Deposit Paid],
  11.               tblBookingsAppend.Transport 
  13. FROM (tblBookingsAppend INNER JOIN (tblOutingDetailsAppend INNER JOIN tblOutingDetails 
  14. ON tblOutingDetailsAppend.[Outing Title] = tblOutingDetails.[Outing Title]) 
  15. ON tblBookingsAppend.[Outing ID] = tblOutingDetailsAppend.[Outing ID]) INNER JOIN (tblPersonalDetailsAppend INNER JOIN tblPersonalDetails 
  16. ON (tblPersonalDetailsAppend.Forename = tblPersonalDetails.Forename) AND (tblPersonalDetailsAppend.Surname = tblPersonalDetails.Surname) AND (tblPersonalDetailsAppend.[Date of Birth] = tblPersonalDetails.[Date of Birth]) AND (tblPersonalDetailsAppend.[Post Code] = tblPersonalDetails.[Post Code]))
  17. ON tblBookingsAppend.[Child ID] = tblPersonalDetailsAppend.[Child ID] 
which worked and was very much appreciated. I later altered this to help in the restore of another table in the same situation, and again it worked a treat. i now try to implement it on a third table and disaster strikes.

The Table tblWaitingList is a list of all people on the waiting list for an Outing, consisting of:

Waiting List ID - Autonumber - PK
Child ID - Text - FK
Outing ID - Text - FK
Date Added - Date/Time

which will be restored from a backup table tblWaitingListAppend, with the Child IDs and Outing IDs coming from tblPersonalDetails and tblOutingDetails as in the code above.

The current code im trying to use looks like:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblWaitingList ([Outing ID],
  2.                             [Child ID],
  3.                             [Date Added]) 
  5. SELECT tblOutingDetails.[Outing ID],
  6.               tblPersonalDetails.[Child ID],
  7.               tblWaitingListAppend.[Date Added]
  9. FROM (tblOutingDetailsAppend INNER JOIN tblOutingDetails
  10. ON tblOutingDetailsAppend.[Outing Title] = tblOutingDetails.[Outing Title]) INNER JOIN ((tblPersonalDetails INNER JOIN tblPersonalDetailsAppend
  11. ON (tblPersonalDetails.[Post Code] = tblPersonalDetailsAppend.[Post Code]) AND (tblPersonalDetails.[Date of Birth] = tblPersonalDetailsAppend.[Date of Birth]) AND (tblPersonalDetailsAppend.Forename = tblPersonalDetails.Forename) AND (tblPersonalDetails.Surname = tblPersonalDetailsAppend.Surname)) INNER JOIN tblWaitingListAppend
  12. ON tblPersonalDetailsAppend.[Child ID] = tblWaitingListAppend.[Child ID])
  13. ON tblOutingDetailsAppend.[Outing ID] = tblWaitingListAppend.[Outing ID] 

and although, to me, exactly the same as the previous SQL produces a "Type Missmatch in Expression" error.

The HELP button wasnt too much help, saying that this meant that two related fields were of a different data type, for example: and AutoNumber and Text.

This is what confuses me, as there was no problem when Autonumber and text fields were linked in the other restores.

What on earth is going on?

Mar 13 '07 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 15k+
P: 31,470
See Autonumbers making booking backups impossible as the OP has found an answer.
Mar 15 '07 #2

Expert Mod 15k+
P: 31,470
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


Posted in other thread.
Mar 15 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.