help opening table exclusive 3

[Judi201]

Hi!

Never had this happen to me before. I generally use MODAL forms with PRIVATE data sessions. I add the tables I need to the form's DE and SELECT when needed. This has worked but now I need EXCLUSIVE use to PACK the tables. I am using the following strategy.

*** select orders records to archive
SELECT orders
	DELETE FOR shpdate <= (ldArcDate) ;
		AND shpcomp = 1 ;
		AND !EMPTY(shpdate) 
	SELECT * ;
		FROM orders ;
		WHERE DELETED() ;
		INTO CURSOR csrOrders
		lnTally = _TALLY
			IF lnTally > 0
				*** got orders - just continue
			ELSE
				nAnswer = MESSAGEBOX('No Orders to Archive',0 , " ")
				USE IN orders
				USE IN ordtemp
				This.Parent.txtArcDate.Value = {}
				This.Parent.txtArcDate.SetFocus()
				RETURN
			ENDIF

I use the cursor csrOrders to present either in Browse or Report to let the user confirm. Then DELETE matching items from another TABLE.

I close (USE) the table when the cursor is confirmed as OK

Then after other processing I need to reopen EXCLUSIVE so I can pack. It goes through this line without error.

 USE orders IN 0 EXCLUSIVE

But instead of the 28k+ records I am expecting there are 0.

I have followed it through in DEBUG and every other step is working properly, just now it is as if I don't have any data in the orders table.

I don't even know where to begin to look for the problem.

Any help will be appreciated.

Judi

Still in VFP 6.0.

 

[GriffMG]

Hi Judi

I'm not fully following your code... but I suspect your orders table is already open/still open. If the open you are trying is meant to be just after your test for _TALLY (which is a new one on me, you live and learn) try specifically closing the orders table before reopening it

SELECT ORDERS
USE
SELECT 0
USE ORDERS EXCLUSIVE

Regards

Griff
Keep [Smile]ing

 

[Judi201]

Griff, Thanks for the speedy reply.

I am really confused now!

I had: 'USE IN orders' way back in the code, thinking I was through with it and using the cursor so I would close it until I needed to reuse it exclusive to pack.

On your suggestion I went back and did:

 SELECT orders 
       USE

and it seems to work fine.

Would you explain the difference in 'USE IN' and 'SELECT and then USE'. It seems that most of the code I look at to study uses the USE IN maybe for a reason that I am not understanding.??

My only experience before this project was in 2.6 and my practice was OPEN it- get what I needed and close it.

Thanks for your help. It is working, but I wish I understood the difference.

Thanks,

Judi

 

[Judi201]

Mike,

Thanks for the response. I see your answers in so many forums and I don't see how you do it, but am so glad you do.

Actually, I tried to use USE IN (SELECT('ORDERS')), copying it from something you had written for me earlier but it failed because I did not see the ' ' .

Thanks again for all you do.

Judi

 

[Olaf Doschke]

Hi Judi,

As most people you are puzzled why USE is the command to open a table and also to close it, right? And in what way do you need to write the USE command to do what?

It's rather simple, it's the same command to open and to close tables, because USE always opens one table and closes another one, due to the workarea concept of foxpro datasessions. Workareas are placeholders for tables, some kind of array or collection if you like, workareas can be adressed with their workarea number or with an alias name and each workarea can hold one open table.

USE works in the current work area or a workarea you determine with the IN clause. And in that workarea the table you specify is opened and the table open in that workarea is closed at the same time, because - as said - only one table can be open in a workarea, it's like a placeholder for a table. And if you don't specify which table to open, USE opens no table and so only works as a close command.

All clauses and parameters of the USE command are optional, so a pure

USE

is simply closing the table of the active workarea and opening no other table instead of that.

SELECT orders
USE

Closes the orders table, because SELECT orders first selects the workarea, where orders.dbf is open and then USE works on that workarea, closes orders and opens no table.

You can combine this to a single USE command:

USE IN orders
* or alternative
USE IN SELECT("orders")

The IN clause of the use command specifies in which workarea the USE command works. In both cases it works in the workarea, in which the orders table is open. In the first case, the workarea is specified by it's alias name "orders", in the second case the workarea is specified by it's workarea number, as SELECT("orders") is a command that returns the workarea number of the workarea named "orders".

You'd always use the simpler alternative [USE IN orders]? Not, if I tell you that this way the command raises an error, if the orders table is not open. The second version [USE IN SELECT("orders")] also works, if orders is not open, because Select("table") returns 0 if no workarea has the name "table". So in that case it's [USE IN 0], and what does that do? Nothing! It opens no table and closes no table, because 0 is by definition an empty workarea.

USE IN SELECT("table")

is the failsafe way to close a table called alias.

To open a table failsafe (without "table already open" error) you can do:

USE table IN SELECT("table")

Why? Becaus if "table" is not open, SELECT("table") returns 0 and the table is opened IN 0, in an unused workarea. If the table is open, SELECT("table") specifies the workarea, in which it is open, and so the USE command closes the table and reopens it again.

So finally you can open a table exclusive, without caring if it is already open in shared mode (at least without caring if it's open in "your" datasession) using this version:

USE orders IN SELECT("orders") EXCLUSIVE

The whole truth is a little more complex, because tables can be open with a name (Alias) differing from the table name. And the use command has some more clauses and paramters, eg you can work with the pure table name without path or dbf file extension, if a dbc is open. Or you can work with file names.

For example if I want to have exclusive access to a table I already have open and selected at design time or during debugging, I often USE DBF() EXCLUSIVE in the command window instead of closing the table and reopening it exclusive. Do you see how that works?

Bye, Olaf.

 

[Judi201]

Olaf,

So finally you can open a table exclusive, without caring if it is already open in shared mode (at least without caring if it's open in "your" datasession) using this version:

I do understand about using USE to open and close, but it is in use of alias in different workareas that I get mixed up sometime. I think maybe I should always use a different alias name and I would know exactly what I was using. I many times get 'alias not found - or something like that' when I know that it is in fact open and I go to excessive length to correct it.

I understand what both of you are telling me and will use it. Thank you so much for that explanation for it clears up somethings I did not understand. But am I understanding right that I can have a table open in one work area shared and used exclusively in another?

Mike,

To be honest Judi. I would not pack the tables like this.

That is exactly what this is. I'm thinking once a year they will do this. Possibly every 6 months if there is enough activity.

When you say "like this" do you mean you would suggest a better way or just not to do it.

Many, many thanks to you both.

Judi

 

[Judi201]

Hi!

I was mistaken when I said I had it working, or maybe it worked one time and I've changed something but it is not packing now!

  *** now orders
       USE Casting!orders IN SELECT("orders") EXCLUSIVE
       PACK

This is the line that opens an orders table with no records after I have SELECTed and used it above. This is why I'm asking if my table with deleted records is in another 'world' somewhere and I am getting another one. I read your words above and it makes perfect sense but I just can't get it to work.

Nearly a week on this one thing. Good thing I am not depending on this for my living!

ANY help will be appreciated.

Judi

 

[Judi201]

Mike,

I have tried every combination of USE and SELECT I can think of and this is how it goes.

SELECT orders && displays 27654 records
USE && clears status bar

USE orders EXCLUSIVE && orders table open with 0 records

What am I still not seeing?

Judi

 

[mm0000]

Have you checked whether there is more than 1 orders table in your path :

You can check it with :

SELECT orders
?dbf()
USE

USE orders EXCLUSIVE
?dbf()

Check the results and verify that the dbf() path results are the same.

 

[Olaf Doschke]

Hi Judi,

am I understanding right that I can have a table open in one work area shared and used exclusively in another?

No, you misunderstood. The command I gave closes the table first, if it is open and then reopens it exclusive, so after executing

USE orders IN SELECT("orders") EXCLUSIVE

orders is open once only in exclusive mode. The only thing is, you don't need to check, if orders was already open.

Towards your problem, that the orders table is empty after you opened it exclusive. Maybe you deleted all records in it? SET DELETED OFF and see if browsing the table shows you all records with a deletion mark.

Bye, Olaf.

 

[Judi201]

Olaf,

That is why I am so confused. I know how many records should be deleted in the test sample I am using. When I suspend and Browse the table (orders) the correct records are marked and if I RECALL ALL I get the correct number of records recalled. I have done this over and over.

I just put a browse statement in the proper places and I know that I have:

1) the full table when I just SELECT orders
2) the correct number of records deleted
3) 0 records any way I open it exclusive

I am sure there is something I am missing but I am surely missing it!

Thanks for any suggestion.

Also, I can pull the code into the command window and it runs fine, delete, pack and all.

mm0000: I will try that and see. Thanks for your time and suggestion. I had not worried about that because I know there is not SUPPOSED to be another table(orders) in the path.

Judi

 

[Judi201]

OK, Problem solved but I really have questions now.

mm0000, I just stuck your code in where I was trying to pack and found that there was another table. I was watching on the status line and thought they were the same but could not see enough of the path to see the difference.

My folder structure is like this for data:
D:\myprojects\casting\data
There were copies of all my tables in D:\myprojects\casting!!

So how this happened as I was working on this, I don't have a clue! I know it was not like this when I started this part because I went to a backup made immediately before starting and the tables are only where they belong, in the data folder.

So my concern is how did I cause this and how do I prevent this from happening again. I must confess that I am running this form stand alone while testing and not calling it from the startup form as it will be.

Would you think this is an issue of the form not having sufficient info on default and paths. It has not been a problem before but I might not have USED a table before, just SELECT. This was what was so confusing to me when I first started trying to use VISUAL and I suppose I still don't have it after all of the help I have received.

A suggestion for a bit of code to stick in the load of my form will be greatly appreciated.

Thanks to all for you advice.

Judi

 

[Olaf Doschke]

Well,

is this table a free table or a dbc table? If it's a free table or if you don't have the database open (OPEN DATABASE ...\mydatabase.dbc) and set (SET DATABASE To madatabase), then VFP will search what file you could mean with orders. If you really want to be sure that the same orders table is opened exclusive that is already open shared, then give USE the full path and do it this way:

USE DBF("orders") IN SELECT("orders") EXCLUSIVE

The data environment you always used so far to open tables visually also stores the paths to the tables.

Bye, Olaf.

 

[Olaf Doschke]

Hi Judi,

I think there is a much easier way to do it for you, as you use the data environment of your form: Simply set the Exclusive property of the "orders" Object of the data environment of your form to .T., then the table is opened exclusive initially and you don't need any code to change from shared to exclusive mode.

Simply open the dataenvironment, select the "orders" table, right click on it, choose properties and set Exclusive to .T. by double clicking on the line in the property window.

Bye, Olaf.

 

[Judi201]

Olaf,

If you really want to be sure that the same orders table is opened exclusive that is already open shared, then give USE the full path and do it this way:

CODE
USE DBF("orders") IN SELECT("orders") EXCLUSIVE

The data environment you always used so far to open tables visually also stores the paths to the tables.

This is exactly what I need and I would like to do it that way. I was wondering if my use of the alias orders was being confused with the table and how FOX knew which one I wanted.

I have one dbc (Casting) and all tables are included in it.
So if I may restate, am I correct that I can, for each form:

1)Add tables to DE (and the path will be known)
1-a) If I used the dbc to add tables to the DE I will NOT need to code anywhere OPEN DATABASES.

2)Use the statement above to open the orders table exclusively.(the dbf('orders')makes it the table referenced)- not the alias?

3) Closing the form completely closes the data environment as well if I have AutoOpen and AutoClose set to .T.

If this is true I will handle things like this.

If one form(modal) is started from another(not modal) am I correct that the modal form opens, closes, handles environment and returns with the receiving form in the same state as before launching the modal form??

If this is all true, I think the fog is lifting.

"Thank you" is so weak for all the help you have given me.

Judi

 

[Olaf Doschke]

I think the [Exclusive] property of the table in the data environment should solve your problems without any code.

Towards your assumptions:

1) and 1a) is correct.
I think (I'm not sure) if you have DBC tables in the DE besides those tables the DBC is opened, but not necessarily SET as the active database.

If a database is set active, USE tablename will first look in this active DBC, if there is a table named "tablename" and find out the path there, if the database is open but not set active, VFP will look for a file tablename.dbf.

So to really really make sure you open the database table orders, you could do:

USE mydcbc!orders IN SELECT("orders") EXCLUSIVE

Where "mydbc" should be the name of your dbc.

But then again, to really really really make sure you get the table you want, use the [Exclusive] property of the table in the data environment.

2) is correct
DBF("orders") will work both with a database table and a free table, as that returns the filename (including the full path) of the table open in workarea alias "orders".

Under circumstances that may not be orders.dbf, but some other dbf that has been opened with that ALIAS "orders".
But in general alias and file name are identical, so you don't need to worry.

3) is correct, especially as the table is opened by DE, only in shared mode.

Bye, Olaf.

 

[Judi201]

Olaf,

I'm acting like many of the math students I taught, just holding on to a preconceived idea, but I'm letting go.

But then again, to really really really make sure you get the table you want, use the [Exclusive] property of the table in the data environment.

Yes, I really,really,really want to make sure I get the table I want.

I was avoiding that method as I do not want orders table to be exclusive anywhere else (so far as I know now) other than this form and I feared setting up something that would bite me somewhere else.

I get it that you are telling me that for this form I need to set the exclusive property of that table and I will.

Resorting to old fox 2.6 I feel that I need to:
SCATTER 'income from this app' TO 'you guys' && for I do
GATHER && much good help

I think I have it now and I realize how, blissfully ignorant, I had been sailing along on LUCK!!

Thanks

Judi

 

[Olaf Doschke]

Hi Judi,

Ah, I see, you were afraid to set that property, because you thought it would mean the table is then opened exclusive everywhere. No, the properties you set for that objects of the data environment are set for that form only. You don't set table properties via the data environment, that are stored to the dbc.

Bye, Olaf.

 

[Olaf Doschke]

Judi,

I'm acting like many of the math students I taught.

That explains a lot. Sometimes you want to know all in such a detail level, that I fear we never end up anywhere.

I think sometimes it's sufficient to first learn something superficial, then learn it in more and more detail. For example I wouldn't teach a child particle physics of the sub atomic lession, if it asks about the nature of matter. The concept of the four elements of air, fire, water and earth may be sufficient to understand the concept. You can later teach, that the truth is more complex...

I wish you a nice weekend, don't be fooled tomorrow!

Bye, Olaf.

 

[Judi201]

Olaf,

Amen to that above. And you have a nice weekend as well. With a 12 year-old grandson, I am sure to be 'fooled' but that's nice.

Bye, Judi