Using a program to open multiple tables

[Vatchunter]

This is the first program I have ever written. I am writing this to open several tables that I work with on a daily basis. When I run the program table A is the only one visible until I click on the screen, table A disappears and then table B will open. if I click on the screen again both tables disappear. After running the program, if I type select A and Browse last in the command window, and likewise for table B, both tables open, the index and relationship have been set. How do I get the tables to stay open after the program runs?

This is the code for my program:

SELECT a
USE "c:\users\fox tables\example1.dbf" EXCLUSIVE
BROWSE last
SELECT b
USE "c:\users\fox tables\example2.dbf" EXCLUSIVE
BROWSE last
select b
INDEX on ABC TAG ABC
SELECT a
SET RELATION TO ABC INTO b

Thanks for your help!

 

[Olaf Doschke]

Don't BROWSE last.

What you're describing is wrong, you don't close and reopen table a or table b, you'r just seeing the BROWSE window appear, but the BROWSE window is not the table, just the display of it's data.

Table is opened once and stays open after the first USE, table b is open and stays open, after the second USE. And both tables stay open.

A Table being open is being listed in the datasession window, a browse window opened is merly alsso displaying it, but the table is still open, if the browse closes.

If you want to show the browse windows after you set the relations, remove both BROWSE LAST and instead after setting the relation do two BROWSE with NOWAIT.

Also, don't index every time you open a table, you only need to index a table once. As that surely has been done already several times, the CDX file exists and you simply can SET ORDER to that index tag. You also better don't use specific work areas, but open tables in whatever unused workare, which can be done by SELECT 0 at any time, or by USE ... IN 0. Overall this means:

USE "c:\users\fox tables\example1.dbf" IN 0 EXCLUSIVE
USE "c:\users\fox tables\example2.dbf" IN 0 EXCLUSIVE
SELECT example1
SET RELATION TO ABC INTO example2
BROWSE NOWAIT
SELECT example2
BROWSE NOWAIT

It's up to you, if you need the DBFs in exclusive use, you could also drop that option and the tables will be used exclusive or shared depending in SET EXCLUSIVE ON or OFF. In the normal case for the end use you open tables nonexclusive, shared, because you want to collaborate on the data. In single user applications using tables EXCLUSIVE has a performance advantage in writes, eg TABLUEUPDATE, INSERT, UPDATE, etc.

Bye, Olaf.

 

[Vatchunter]

Olaf,

Thanks for the Fox Pro lesson tonight. My program is opening the tables as needed.

Appreciate the help!

 

[JRB-Bldr]

To carry the explanation just a little bit further - you can add an optional ALIAS to the USE command so that your code might be more 'readable'.

For Example:

USE "c:\users\fox tables\example1.dbf" IN 0 EXCLUSIVE [b][u]ALIAS Customers[/u][/b]
USE "c:\users\fox tables\example2.dbf" IN 0 EXCLUSIVE [b][u]ALIAS Orders[/u][/b]

* --- Instead of accessing table by Workarea Name (such as 'a' or 'b') use Alias instead for readability ---
SELECT Customers  && This will 'activate' access to the table 'example1' <- Aliased as 'Customers'
*<do whatever>
SELECT Orders  && This will 'activate' access to the table 'example2' <- Aliased as 'Orders'
*<do whatever>

NOTE - if you do not specifically assign an ALIAS to the table within the USE command, the Alias will default to the actual table name (such as 'example1' or 'example2').

Good Luck,
JRB-Bldr

 

[Vatchunter]

Thanks JRB, I can see how this would help to keep track of the tables using the Alias. I appreciate all the great info, I am now writing the several programs
to open different groups of tables and setup relationships for many different tasks I perform through out my day.
What a time saver!

The IN 0 command is that used to set up the workspace? How do you assign table to go to other workspaces?

Thanks,

vatchunter

 

[mmerlinn]

The IN 0 command is that used to set up the workspace?

IN 0 tells FoxPro to open a table in the lowest available workarea. If you always use an alias to access that table, you never need to know where the table is actually located.

For more info click this link: USE command

mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[Olaf Doschke]

Yes, mmerlin has answered that. I also already said you better don't use specific work areas, because in the long run, if you want to combine several PRGs you can't use a certain workarea twice. Opening tables in a specific workarea letter or number is bad because of that. You always have the alias name with or without using the ALIAS clause of use, the workarea has a name, which is much easier to understand than a letter or number, it makes code better readable, understandable, maintainable, even after years of not looking into it.

I recommend to not use the ALIAS clause, unless you need a table open twice, not using it you get the table name as alias name, as long as the DBF file doesn't start with a digit and does not contain spaces. You can use more than the pure latin 26 letters, but I recommend not to. Just using the 26 letters, underscores and only using digits at the end of a name makes it a perfect table and alias name. USE tablename.dbf IN 0 then results the table to be opened in an unused workarea (that also means, it doesn't close any table) with the name (more exact the stem name = file name without path and DBF extension) of the dbf file and code working on alias=table name is easy to understand, extend, etc.

Just as a side note: USE is not only a command opening a table, it also closes tables. USE without any further options, names, etc simply closes what is open in the current workarea. Not specifyind IN 0 or IN a, IN 1, etc, USE works in the currently selected workarea. That means the following code opens and closes tables, in the end only table3 is open in the current workarea:

USE table1
USE table2
USE table3

In contrast the following code opens the three tables in whatever free workareas available:

USE table1 IN 0
USE table2 IN 0
USE table3 IN 0

0 is not a workarea number, 00 is telling VFP to use the next free workarea, therefore this doesn't tr to open the three tables in the same workarea, which would close previous opened tables like the first example.

Also notice: If you already have run code openeing other tables and then afterwards run the last code with IN 0, the three tables are opened without close any previously opened table. You're more flexible with this, as you don't depend on the free slots. You don't hard wire the workarea numbers this way. You still have alias names = table names you can always refer to. SELECT table2 selects the workarea having table2 opened in it, no matter if that's workarea 2 or 6 or 300 because other workareas are already used. If you think of workareas as slots, making use of the specific nature of 0 is, you let VFP manage workarea numbers, you don't need to take care of it. Doing your programs with different relations in that manner, you can run several of them, you only need two or three monitors to display all browse window you need and then can have multiple relations. The only thing you need to take care of is specifying alternate alias names for tables you need to open more than once, because alias names need to be unique to not get an ambiguous alias name.

If still don't understand, that will come later. Just do it that way and you won't get into trouble. Just don't forget you need to address the workareas by the table names then, not by a,b,c or 1,2,3.

Bye, Olaf.

 

[JRB-Bldr]

I quite often use Alias when accessing my data - maybe because I can be lazy.

For example, if I have tables:
AR Master.dbf
AR Detail.dbf

I would likely Alias them as:
ARMstr
ARDtl

So that I can:
1. readily identify them
2. type less (remember that I can be lazy)
3. and, since there are spaces in the table names, their default Aliases would be AR_Master & AR_Detail anyway, so again, its less to remember (did I remember to include the underscore or not?)
4. and, as Olaf has explained, you can open the same table(s) more than once in your application as long as they are opened into different Workareas and have different Aliases.
* Example:

USE Table1 [u]IN 0[/u] [u]ALIAS Tbl1[/u]   
USE Table1 [u]IN 0[/u] [u]ALIAS Tbl1-2[/u]

But you can make your own decisions regarding their use.

How do I get the tables to stay open after the program runs?
Olaf already addressed this pretty well.
But as long as you have opened your data table(s) into different Workareas (basically you DID use USE <whatever> IN 0) then they will stay Open until you close them unless you use the command CLOSE DATABASES ALL which will close everything.

One VFP development 'tool' that you might want to get familiar with is the Data Session Window (VFP Top Menu - Window - Data Session or in the Command Window type SET or SET VIEW ON).
That will give you an overview visibility of the tables that are open and relations that are established at any point in time.
This might help you during your learning period (DOES IT EVER END??? I THINK NOT!!!) to understand what is going on with your tables.

Good Luck,
JRB-Bldr