help checking to see if a table is open

[USMA99]

This is so simple that I'm embarassed to post it, but I can't seem to solve this. I check to see if a table is in use, if it is I select it otherwise I open it. The following code will run through to the USE command and then error out with 'File is in use.' This seems so illogical because, by definition, it cannot be open as it just failed the if test checking to see if it is open.

		IF USED(cTempNo)
			SELECT cTempNo
			APPEND BLANK
		ELSE
			SELECT 0
			USE (cTempNo)
			APPEND BLANK
		ENDIF

Can someone see something that I do not?

 

[GriffMG]

I have had the same problem, but to do with whether the file is closed.

If Type("MyFile.MyField") <> "U"
  Select MyFile
  Use
EndIf

Regards

Griff
Keep [Smile]ing

 

[USMA99]

Thanks. I think that seems a little strange not to be able to use the USED() function with a variable. I finally got it to work, I had to bury quotes in with the variable and use the & operator for some macro substitution:

	cTempNo = "'" + "tablename" + "'"
	IF USED(&cTempNo)
		SELECT (&cTempNo)
                APPEND BLANK
	ELSE
		SELECT 0
		USE (&cTempNo)
                APPEND BLANK
	ENDIF

 

[USMA99]

NM, doesn't work. Ugh.

This has to be a very common thing to do. It seems odd to check the data type of a field in a table that doesn't exist. Is this the best way to see if a table is open already?

 

[brigmar]

The way I do it is back-to-front to your check...

cTempNo = 'mytable'
IF !USED(cTempNo)
  USE (cTempNo) IN 0
ENDIF
SELECT (cTempNo)
APPEND BLANK

 

[USMA99]

I tried this and the IF test doesn't work. It tries to do the USE statement and crashes with 'file in use.' I tried it at the command line and it works (that's how I got fooled last time) but not when running in a program.


Is this a clue for anyone?

 

[alvechurchdata]

This is most peculiar. Your original code:

[TT]IF USED(cTempNo)[/TT]

works for me and I'm wondering what you have got stored in the variable cTempNo. Are there spaces in the name or path of the table?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Mike Lewis]

... then error out with 'File is in use.'

Keep in mind that you also get that error if the file is already open exclusively.

It might be open by another user, or in another data session. Either way, USED() will still return .F., but if the file is open exclusively, you'll get the error when you try to open it again.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[USMA99]

Good thoughts, unfortunately not it either.

cTempNo contains a table name, an alias actually. For example, a table name could be 'stub0.dbf' and cTempNo would contain 'cstub0' without quotes. There will never be any spaces and no other users will have the table open. (these are temporary files on a local hard-drive and not shared between installations of the app and CLOS DATABASES ALL is used at certain points such as startup, do reboots, etc) I always simply say "USE" and therefore open the table exclusively.

The thing that is bizarre to me is USED() is .F. and the very next line of code that uses the table it crashes with 'in use.' This isn't leaving any chance for something to change. I'm not sure if I ever had something work at the command line but not in a program. Is it working in a program for you Geoff or is it only working at the command line? Any other ideas anyone?

 

[baltman]

Does table name include the extension? What is the cTempNo variable exactly?

CREATE TABLE test (f1 c(1))
lcTable="test.dbf"
?USED(lcTable)
?USED(JUSTSTEM(lcTable))

Or is the table name something that starts with a number? Then the table name and alias will not be the same.

CREATE TABLE 9test (f1 c(1))
lcTable="9test.dbf"
?USED(lcTable)
?USED(JUSTSTEM(lcTable))
?ALIAS()

Make sure it is open either way?

CREATE TABLE test (f1 c(1))
lcTable="test"
CLOSE TABLES 
SELECT * FROM (lcTable) INTO CURSOR curTemp WHERE .f.
SELECT (lcTable)

Brian

 

[alvechurchdata]

This works

Close Databases all

USE Spares

cTempNo = "spares"

If Used(cTempNo)
  Select (cTempNo)
  Append Blank
Else
  Select 0
  Use (cTempNo)
  Append Blank
Endif

and it still works if I rem out the first USE statement.

But if I give the full path then I get your error:

Close Databases all

USE d:\projects\directai.919\dev\data\spares

cTempNo = "d:\projects\directai.919\dev\data\spares"

If Used(cTempNo)
  Select (cTempNo)
  Append Blank
Else
  Select 0
  Use (cTempNo)
  Append Blank
Endif

The USED() returns .F. and so I try to open the table again in the Else clause.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[baltman]

Try

Close Databases all
USE d:\projects\directai.919\dev\data\spares

cTempNo = "d:\projects\directai.919\dev\data\spares"

If !Used(JUSTSTEM(cTempNo))
  Use (cTempNo) in 0 alias (JUSTSTEM(cTempNo))
Endif

SELECT (JUSTSTEM(cTempNo))
APPEND BLANK

 

[pk4217]

The only reason I can think of, is the table has been opened using other alias, that'll cause your used() statement to return a false.

good luck finding bugs...

 

[mm0000]

From what I can see in your code [cTempNo = "'" + "tablename" + "'"]- you are checking to see whether a specific table is used, using its table name. However, you have already used it earlier using an alias. Therefore, the used() function will not find the table open since it has been opened earlier as an alias. Change cTempno to the alias (cstub0 instead of stubj0) rather than the table name - and then the used() funtion will work.

 

[USMA99]

Geoff & Brian get the gold star. Thanks!