473,320 Members | 1,870 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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 ) 
  6.  
  7. SELECT tblOutingDetails.[Outing ID],
  8.               tblPersonalDetails.[Child ID],
  9.               tblBookingsAppend.[Amount Paid],
  10.               tblBookingsAppend.[Deposit Paid],
  11.               tblBookingsAppend.Transport 
  12.  
  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] 
  18.  
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]) 
  4.  
  5. SELECT tblOutingDetails.[Outing ID],
  6.               tblPersonalDetails.[Child ID],
  7.               tblWaitingListAppend.[Date Added]
  8.  
  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] 
  14.  

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?

NDayave
Mar 13 '07 #1
2 1659
NeoPa
32,556 Expert Mod 16PB
See Autonumbers making booking backups impossible as the OP has found an answer.
Mar 15 '07 #2
NeoPa
32,556 Expert Mod 16PB
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
Posted in other thread.
Mar 15 '07 #3

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

Similar topics

242
by: James Cameron | last post by:
Hi I'm developing a program and the client is worried about future reuse of the code. Say 5, 10, 15 years down the road. This will be a major factor in selecting the development language. Any...
3
by: Reuben Pearse | last post by:
Hi all, I've just converted the tables in a big database (approx 27 million records) from MyISAM to InnoDB. When I was using MyISAM I backed up the database by stopping MySQL and then copying...
53
by: Cardman | last post by:
Greetings, I am trying to solve a problem that has been inflicting my self created Order Forms for a long time, where the problem is that as I cannot reproduce this error myself, then it is...
19
by: Vinod | last post by:
Hi, I have got a peculiar requirement. I want to distinquish between the color codes. I have got two text fields and i will enter the color codes there. The first text field will have ...
3
by: NeilAnderson | last post by:
I'm a fairly new user of access & I've never had any training, so I'm wondering if I'm doing the right thing here, or if it matter at all. I'm building a database for room booking purposes and I'm...
18
by: Dixie | last post by:
Can I set the Format property in a date/time field in code? Can I set the Input Mask in a date/time field in code? Can I set the Format of a Yes/No field to Checkbox in code? I am working on...
17
by: tshad | last post by:
Many (if not most) have said that code-behind is best if working in teams - which does seem logical. How do you deal with the flow of the work? I have someone who is good at designing, but...
4
by: lesperancer | last post by:
it looks like this will save many versions of a relationship window, but based on the fact that the same tables are displayed in the relationship window and it will restore versions of what was...
6
by: javelin | last post by:
I'm having difficulty searching for an answer to this challenge. Can someone give me a clue on the right keywords to use to find a discussion on this subject? All of the ones I saw appear to touch...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.