473,320 Members | 2,097 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.

Those backups just have no consistency

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]
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]
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 12 '07 #1
3 1192
Rabbit
12,516 Expert Mod 8TB
The joins could be the cause. You may be joining a text field on a numeric field.
Mar 17 '07 #2
Denburt
1,356 Expert 1GB
Rabbit is right AutoNumber is just that a number field, number fields joined to text fields just don't work and if somehow you have managed to make it work like this then CHANGE it quickly to prevent any data loss.

Good luck :)
Mar 19 '07 #3
NeoPa
32,556 Expert Mod 16PB
Is this still an outstanding problem.
I know you posted another one recently which you managed to resolve yourself. Is this another of those or is an answer still required?
Mar 20 '07 #4

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

Similar topics

0
by: Bill Gates | last post by:
i have two servers this is happenning on. each has a maintenance plan with the same options selected... General Tab - applies to all databases Optimizations Tab - reorganize data and index...
0
by: JJ | last post by:
I'm trying to create a backup set which maintains only a fixed number of days. As such, I've got the following script: BACKUP DATABASE to WITH NOINIT, NOUNLOAD, NAME=N'My Database Backup',...
1
by: Sgt. Sausage | last post by:
I've got a server (SQL 2K, Win2K) where the backups have started running long. The database is a bit largish -- 150GB or so. Up until last month, the backups were taking on the order of 4 to 5...
1
by: Loopy | last post by:
I'm trying to create a backup set which maintains only a fixed number of days. As such, I've got the following script: BACKUP DATABASE to WITH NOINIT, NOUNLOAD, NAME=N'My Database Backup',...
1
by: Peter Sands | last post by:
Hi, I am right in assuming to recover a database where logretain is on. That I only need the logs that are reported in the list history.. for instance; db2 list history backup since...
3
by: db2inst2 | last post by:
Hi all, When doing a restore using control center, i see the backups that are no longer existing. That i physically deleted. Is there a specific way to deleted the backups? Should i...
21
by: Dennis Gearon | last post by:
Along with backing up of my site, what files in 'pgdata' dir should I back up? ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once...
6
by: frank78 | last post by:
Hi everyone, I am having a little bit of trouble backing up some mySQL tables. I've been trying to adapt a script I found on the internet at...
1
by: Polani | last post by:
Hi Guys , I am taking DB2 Online backups through TSM API daily. When i check the database configuration parameters , it shows me " Number of database backup verions to maintain"= 12. This is...
11
by: RoB | last post by:
Hi all, I'm coming from the Informix world and I have a customer using DB2 8.2.3 for Linux on Red Hat Enterprise ES. The customer is performing filesystem backups of the containers etc every...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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)...
1
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: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.