"No table is open in the current work area" error when Replace 3

[TheLazyPig]

Hi!

I get an error "No table is open in the current work area" in highlighted part.

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' &&REGION
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
[highlight #FCE94F]REPLACE RegionBK.Deleted WITH DATE() FOR EMPTY(RegionBK.Deleted) IN (pcBckPath)[/highlight]

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!

 

[mJindrova]

[pre]* wrong
* IN (pcBckPath) you tell vfp: use alias defined in pcBckPath variable
* but this variable contains full DBF's file name
* ======================================================
* REPLACE RegionBK.Deleted WITH DATE() FOR EMPTY(RegionBK.Deleted) IN (pcBckPath)

* right
REPLACE Deleted WITH DATE() FOR EMPTY(Deleted) IN RegionBK[/pre]



MartinaJ

mJindrova

 

[mjcmkrsr]

Hi,

USE (pcBckPath) ALIAS RegionBk
SELECT 0
REPLACE RegionBK.Deleted WITH DATE() FOR EMPTY(RegionBK.Deleted) IN (pcBckPath)

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)

Specifies a work area to activate. If nWorkArea is 0, the lowest-numbered [highlight #FCE94F]unused[/highlight] work area is activated.

Hence

SELECT 0
USE (pcBckPath) ALIAS RegionBk
REPLACE Deleted WITH DATE() FOR EMPTY(Deleted) IN RegionBK

hth

MarK

 

[Chris Miller]

Indeed with SELECT 0 you prepare the Error. It's really straight forward what it says. "No table is open in the current work area" as the current work area is by definition empty, as SELECT 0 selects an unused/empty workarea.

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:

Update (pcBckPath) SET Deleted=Date() Where Empty(Deleted)

Chriss

 

[TheLazyPig]

I get an error

Is it because I already USE it before that's why it's open?

 

[mjcmkrsr]

Hi

Simply

SELECT RegionBK && since it is already open

hth

MarK

 

[TamarGranor]

Indeed with SELECT 0 you prepare the Error. It's really straight forward what it says. "No table is open in the current work area" as the current work area is by definition empty, as SELECT 0 selects an unused/empty workarea.

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.

@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

 

[TamarGranor]

@TheLazyPig, in addition to Mark's advice that you can just select the right work area, as long as the alias is in use, you don't need to worry about what work area you're in when you the IN clause.

Tamar

 

[Chris Miller]

Okay, Tamar, fair. I tested it simply by uing a table, SELECT 0 and then REPLACE IN the table and that works.

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

 

[TheLazyPig]

Thank you so much for the replies. The program is working.