"No table is open in the current work area" error when Replace
"No table is open in the current work area" error when Replace
(OP)
Hi!
I get an error "No table is open in the current work area" in highlighted part.
Region and RegionBK have the same columns but RegionBK has another column named "deleted" it contains the date when the program ran.
The program deletes the duplicate old records in the region table and then inserts them into a backup table (regionbk).
Thank you!
I get an error "No table is open in the current work area" in highlighted part.
CODE
SET DELETED ON
SET SAFETY OFF
LOCAL pcCurrDir,pcScrPath,pcBckPath,pcRegion,pcAsOf,pcBkDate,pnCtr,lnRegCnt,lnDupCnt,lnDelCnt,lnDupCnt,lnTotCnt
*----------------------------------
pcCurrDir = "C:\BACKUP_FILES\Region"
pcScrPath = pcCurrDir+"\230308\region.dbf"
pcBckPath = pcCurrDir+"\230308\regionbk.dbf"
pcRegion = 'NCR' &®ION
pcAsOf = GOMONTH(DATE(2023,02,01),1)-.1 &&ASOF
pcBkDate = DATE(2023,02,28)
pnCtr = 0
*----------------------------------
IF ATC("REGION.DBF",pcScrPath) = 0
MESSAGEBOX("Invalid File Name!",48)
ELSE
USE (pcScrPath) IN 0 SHARED
SELECT region
COUNT TO lnRegCnt
WAIT WINDOW NOWAIT "Checking for duplicate records....."
**To Check Duplicate Records by Region
SELECT * ;
FROM region ;
WHERE RG+DIV+STA+EMPNO+FNAME+MI+LNAME IN ( ;
SELECT RG+DIV+STA+EMPNO+FNAME+MI+LNAME ;
FROM region ;
WHERE RG = pcRegion ;
GROUP BY 1 ;
HAVING COUNT(*)>1 ;
) ORDER BY EMPNO ;
INTO CURSOR curDup READWRITE
llWithDup = reccount("curDup") > 0
IF llWithDup
MESSAGEBOX("With Duplicate Record Found!")
**Duplicates that are less than asof date
IF asof < pcAsOf
**get count of duplicate
SELECT curDup
COUNT TO lnDupCnt
WAIT WINDOW NOWAIT "No. of Duplicate Records : " + TRANSFORM(lnDupCnt,"###,###,###.##")
**to insert duplicate record to regionbk for history
WAIT WINDOW NOWAIT "....Backup Records...."
=regBackUp()
MESSAGEBOX("Successfully Inserted Records!")
**delete duplicate records where asof less than pcAsOf
DELETE FROM region WHERE RG+DIV+STA+EMPNO IN (SELECT RG+DIV+STA+EMPNO FROM curDup WHERE asof < pcAsOf)
MESSAGEBOX("Successfully Deleted!")
ENDIF
ENDIF
**count of deleted records
SELECT region
COUNT TO lnDelCnt
WAIT WINDOW NOWAIT "No. of Records Deleted : " + TRANSFORM(lnDupCnt-lnDelCnt,"###,###,###.##")
**check if there's still duplicate records
SELECT * ;
FROM region ;
WHERE RG+DIV+STA+EMPNO+FNAME+MI+LNAME IN ( ;
SELECT RG+DIV+STA+EMPNO+FNAME+MI+LNAME ;
FROM region ;
WHERE RG = pcRegion ;
GROUP BY 1 ;
HAVING COUNT(*)>1 ;
) ORDER BY EMPNO ;
INTO CURSOR curDups
llWithDups = reccount("curDups") > 0
IF llWithDups
MESSAGEBOX("WITH DUPLICATE")
IF asof < pcAsOf
SELECT curDups
COUNT TO lnDupCnt
WAIT WINDOW NOWAIT "No. of Duplicate Records : " + TRANSFORM(lnDupCnt,"###,###,###.##")
ENDIF
ENDIF
SELECT region
COUNT TO lnTotCnt
PACK
MESSAGEBOX("Total Count : " + TRANSFORM(lnTotCnt,"###,###,###.##"))
ENDIF
**To backup deleted records from region to regionbk
PROCEDURE regBackUp
LOCAL lnCurDup
CLOSE ALL
USE (pcBckPath) ALIAS RegionBk
SELECT 0
USE (pcScrPath) IN 0 SHARED ALIAS Region
SELECT * ;
FROM region ;
WHERE RG+DIV+STA+EMPNO IN ( ;
SELECT RG+DIV+STA+EMPNO ;
FROM region ;
WHERE RG = pcRegion ;
GROUP BY 1 ;
HAVING COUNT(*)>1 ;
) AND asof < pcBkDate ;
INTO CURSOR curDup
COUNT TO lnCurDup
WAIT WINDOW NOWAIT "No. of Records Backup : " + TRANSFORM(lnCurDup,"###,###,###.##")
**Insert Duplicate Records in RegionBK (old records)
SELECT Regionbk
APPEND FROM DBF('curDup')
**Replace blank deleted date to SYSDATE
USE (pcBckPath) ALIAS RegionBk
SELECT 0
REPLACE RegionBK.Deleted WITH DATE() FOR EMPTY(RegionBK.Deleted) IN (pcBckPath)
END PROC
Region and RegionBK have the same columns but RegionBK has another column named "deleted" it contains the date when the program ran.
The program deletes the duplicate old records in the region table and then inserts them into a backup table (regionbk).
Thank you!
RE: "No table is open in the current work area" error when Replace
MartinaJ
mJindrova
RE: "No table is open in the current work area" error when Replace
You may want to invert the commands; you SELECT 0 but do not open any table in the newly selected WA.
From the Help File)
Hence
CODE -->
hth
MarK
RE: "No table is open in the current work area" error when Replace
That you have IN RegionBK doesn't change that this is unimportant, even if RegionBK is already used, the in clause of replace doesn't make that workarea the current workarea and REPLACE depends upon not being at EOF in the current workarea, not in the work area by IN clause. On top of the error you make by remark of mJindrova. So you shot in both your feet.
So you USE that table as MarK suggests and better also SELECT RegionBK, despite having the IN clause. You can use the IN clause to not have a change of the active workarea, but then still need to assure you're not at EOF in the current workarea.
The perhaps simpler solution is to use Update SQL, as you have the filename of the DBF in pcBckPath:
CODE
Chriss
RE: "No table is open in the current work area" error when Replace
Is it because I already USE it before that's why it's open?
RE: "No table is open in the current work area" error when Replace
Simply
CODE -->
hth
MarK
RE: "No table is open in the current work area" error when Replace
@Chriss, you're mistaken. Using the IN clause changes the active workarea for the execution of the command and it's exactly the solution to the REPLACE at EOF issue.
Tamar
RE: "No table is open in the current work area" error when Replace
Tamar
RE: "No table is open in the current work area" error when Replace
But then this REPLACE won't be the source of the error "No table is open in the current work area".
Or would it, if the alias/workarea RegionBK isn't used?
in any case the UPDATE I posted should work as an alternative.
Chriss
RE: "No table is open in the current work area" error when Replace