×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

thread184-1820806

"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.

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' &&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
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



* 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



MartinaJ

mJindrova

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

Hi,

Quote:


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)

Quote:


Specifies a work area to activate. If nWorkArea is 0, the lowest-numbered unused work area is activated.

Hence

CODE --> 

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

hth

MarK

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

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:

CODE

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

Chriss

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

(OP)
I get an error



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

Hi

Simply

CODE --> 

SELECT RegionBK && since it is already open

hth

MarK

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

Quote (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.

@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

@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

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

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

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close