For Camelot 2.x, follow these steps to delete orders from your database based on their date.
IMPORTANT: Realize that you will be deleting these orders from your database. The first step here is to make a good backup of your current database, and it is recommended that you make more than one. Once these orders are deleted, you cannot get them back into your current database. These steps are very detailed, and must be followed exactly. BE SURE YOU HAVE A GOOD BACKUP AND THAT YOU CAN RESTORE FROM THAT BACKUP BEFORE TRYING THESE STEPS. WE ARE NOT RESPONSIBLE FOR ANY DATA CORRUPTION OR LOSS FROM ATTEMPTING THIS PROCESS.
1. Close everyone out of the program and make a good backup of your current database.
2. From Misc/Setup pull-down, choose Company/Misc Info
3. Hold down [Ctrl] and [Shift] on your keyboard and type S Q L
4. Click OK on the warning box that appears
5. Click and drag the window that appears after that so that you can see all of the window (you should be able to see an OK button at the bottom right
corner)
6. There will be a large text box labeled SQL Text that takes up most of this window, and a smaller text box at the bottom left corner as well (no label for it). In that smaller text box, you will need to type a special number that is different for each day according to your system date. Click here to find out what that number is for today's date. So to further explain that, whichever date it is when you decide to do all of this, put the corresponding number for that date into that little text box at the bottom left of that screen.
7. Now, you will copy and paste the code below and click the Execute button for them one a time. Each statement is separated with two blank lines. So you need to do each separate statement one at a time (copy the first statement, paste it into the SQL Text box, and click Execute, then do the next statement - there are 7 statements in all). Also, in the first 3 statements here, there is a date with pound signs on either side and you need to put the cutoff date there you want to use (I have 1/1/2001, meaning it would delete all orders before that date).
DELETE * FROM ORDERS
WHERE (((ORDERS.REPORTDATETIME)<#1/1/2001#));
DELETE ACTIVITIES.*
FROM ACTIVITIES LEFT JOIN ORDERS ON ACTIVITIES.ACTIVITYID = ORDERS.ACTIVITYID
WHERE (ORDERS.ACTIVITYID Is Null) AND (ACTIVITIES.SCHEDSTARTDATETIME < #1/1/2001#);
DELETE PHONECALLSMAILOUTS.*
FROM PHONECALLSMAILOUTS
WHERE SCHEDSTARTDATETIME < #1/1/2001#;
DELETE ORDERSERVICES.*
FROM ORDERSERVICES LEFT JOIN ORDERS ON ORDERSERVICES.ORDERID = ORDERS.ORDERID
WHERE ORDERS.ORDERID Is Null;
DELETE ORDERAREAS.*
FROM ORDERAREAS LEFT JOIN ORDERS ON ORDERAREAS.ORDERID = ORDERS.ORDERID
WHERE ORDERS.ORDERID Is Null;
DELETE ORDERPACKAGES.*
FROM ORDERPACKAGES LEFT JOIN ORDERS ON ORDERPACKAGES.ORDERID = ORDERS.ORDERID
WHERE ORDERS.ORDERID Is Null;
DELETE TRANSACTIONS.*
FROM TRANSACTIONS LEFT JOIN ORDERS ON TRANSACTIONS.ORDERID = ORDERS.ORDERID
WHERE ORDERS.ORDERID Is Null;
That's it. Be sure to compact your data once you have finished (windows Start > Programs > Camelot > Compact Main Database).
Add to favorites