opening tables twice

[HowardHammerman]

I am working with a legacy database using free *.dbf files and *.idx type index files.

I need to open the same table twice in two different workspaces. I use the following code from the command window and it works:

sele a
use in_res index in-date shared
sele b
use in_res index in-rnum shared again
sele a

However when I put it into my program it does not work. I get no error message. Here is a code fragment:

sele a
use in_res index in-date.idx shared
sele b
use in_res index in-rnum.idx shared again
sele a
do while !eof()
   sele b
   seek a.recno
   <<do various things>>
   sele a
   skip
enddo

The problem is that when I get to the "skip" command the in-rnum.idx is active not the in-date.idx.

I am able to work around it by noting the record number and then re-using the in_res table with the in-date.idx.

However this slows down the program.

Any ideas?

Using visual foxpro 6.


Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[GriffMG]

Hi

Shortly you will receive advice that says you might do better with a SCAN...ENDSCAN rather than your DO WHILE loop, but leaving that aside for a moment - I would suggest that you rewrite the opening and loop to look like this:

select 0
use in_res shared alias indate
set index to in-date.idx 
go top

select 0
use in_res shared again alias inrnum
set index to in-rnum.idx 
go top

select indate
do while !eof()
   select inrnum
   seek (indate.recno)
   <<do various things>>
   select indate
   skip
enddo

I am making the assumption that indate.recno is a field and not meant to be recno() and that <<do various things>> does not then change the index for indate.

You would be better off with a compound index (.cdx) rather than the .idx files - as there is a chance that a change in a record might not be reflected in the index.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[HowardHammerman]

Thanks Griff,

I did not create the database and it has been in use since the late 1980's. So I can't use the *.cdx
You are right that recno is a field.

I will make you changes and see what happens.

Thanks,

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[HowardHammerman]

No Joy. Here is my actual code:

sele 0
use h:\hostplus\in_res shared alias a
set index to h:\hostplus\in-rnum.idx 
sele 0
use h:\hostplus\in_res shared again alias c
set index to h:\hostplus\in-date.idx

At this point I get the message "alias already in use".

I am using the single letters since I have a ton of code that references fiels using the letters. For example:
a.debit

Thanks again.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[GriffMG]

How odd

CLOSE ALL
CLEAR
SELECT 0
USE c:\test SHARED ALIAS a
SET INDEX TO c:\test1, c:\test2
SET ORDER TO test1
SELECT 0
USE c:\test SHARED again ALIAS b
SET INDEX TO c:\test2, c:\test2
SET ORDER TO test2

I just did this and it was fine

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[GriffMG]

Incidentally, opening both index files on the same line
gets over the problem of accidental updates.

Just a thought, you don't already have the table open?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[DSummZZZ]

Just as an FYI Howard, use the SET command with no parameters, or DISPLAY STATUS to see your open tables.
You have one alias as 'a', one as 'b', then one as 'c'. Does 'b' already have the table open?


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Olaf Doschke]

single letter alias names are bad, because a-i are also the names of the first few workareas. Therefore and as you say you have tons of code accessing fields via a,b, etc. stay with not using Select 0.

Select 0 is selecting the first empty work area, so opening a table in that workare doesn't close another table at the same time.

I'd go for scan...endscan, this is the best to skip in the correct workarea anyway. Second best is to SKIP IN aliasname.

Despite all of that, the skip you do in a, as aou select a right before the skip, should skip in the order of the index set for workarea a. It must be something in the code you omitted via "<<do various things>>", that changes the index of workarea a, or there can only be one index set even in two work areas for one table, but I don't believe the second.

Bye, Olaf.

 

[HowardHammerman]

Dear Friends,

I finally got it to work.

Here is the code:

use in_res index in-rnum.idx, in-date.idx shared alias cc in 0 order 1
use in_res shared again alias aa in 0 order 2
sele aa

The trick is to open both indices during the first USE command.

Thanks to all!


Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[JRB-Bldr]

The problem with using single letter alias is that the data table workspace is also often referenced by a single letter alias.

So, as you appear to have encountered, if you should happen to have a table open in workspace 3 which, unless otherwise defined, would default to an alias of 'c' and you attempted to specify an alias 'c' for another table you would encounter the problem.

Personally I would have used something somewhat more easily recognizable like:

use in_res index in-rnum.idx, in-date.idx shared alias [B]In_ResA[/B] in 0 order 1
use in_res shared again alias [B]In_ResB[/B] in 0 order 2

SELECT [B]In_ResA[/B]
* -- or --
SELECT [B]In_ResB[/B]

Good Luck,
JRB-Bldr

 

[NasibKalsi]

thread182-1374227