use an external table to the database multiple times 1

[castgb]

I have to open a table repeatedly in VFP9
I tried in every way but returns me the error "table already open"
what is the best way to solve the problem?


USE &mscheda AGAIN IN 0
APPEND BLANK
REPLACE codiceid WITH ALLTRIM(thisform.text2.Value)
REPLACE DESCART WITH "MANODOPERA "+ALLTRIM(thisform.text1.Value)
REPLACE QUANTITA WITH MNORE

if I use the same open table returns error
table already open

help my please

 

[Mike Lewis]

You appear to have already solved the problem. The answer is to add the keyword AGAIN to the USE command. That should do it. If you are still getting the same error, it must be because the table is already open in another part of your program.

But I have to ask: Why do you need to open the same table repeatedly? There's nothing in the code you posted that suggests a good reason for doing that.

Also, the code you posted might not do what you expect. When you open a table IN 0, it is not necessarily opened in the current work area. That means that your subsequent APPEND and REPLACE commands might be operating on a different table. That wouldn't explain the error you are seeing, but it is something you need to fix nevertheless.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[castgb]

good mike
Do you understand the problem
I put the full code
how can I fix it?
thanks a lot

excuse my terrible English are Italian

SELECT DIPEND
STORE RECNO() TO nrec

SELECT tmp_dip
SET FILTER to
SET FILTER TO in_lav=" "
GOTO top
INDEX ON DATA TO MGG
SET INDEX TO MGG
GOTO TOP
DO WHILE .NOT. EOF()
STORE RECNO() TO NRECQUALE
STORE DATA TO MDATA
STORE N_ORE TO MNORE
STORE N_LAV TO MNLAV

IF MNLAV>0
mSCHEDA=ALLTRIM(CURDIR())+"tabelle\"+"LAV"+ALLTRIM(STR(MNLAV))+".dbf"

USE &mscheda AGAIN IN 0
APPEND BLANK
REPLACE codiceid WITH ALLTRIM(thisform.text2.Value)
REPLACE DESCART WITH "MANODOPERA "+ALLTRIM(thisform.text1.Value)
REPLACE QUANTITA WITH MNORE
*!* REPLACE PACQ WITH MPCALC
*!* REPLACE UNMIS WITH MUNMIS
REPLACE DATA WITH MDATA
REPLACE IMPORTO WITH QUANTITA*PACQ
GOTO TOP
sum importo to mimp
replace all totale with mimp


ENDIF

SELECT tmp_dip

GOTO NRECQUALE
SKIP
ENDDO
REPLACE ALL IN_LAV WITH "S

 

[Olaf Doschke]

There are two related errors, but none literally says "table already open"
Error 3: File is in use, can be overcome by using the AGAIN keyword
Error 24: Alias name is already in use.

Even when specifying AGAIN you need a seperate alias for a table file opened twice.

Error 3 is actually just a warning, as it's usually not needed to open a table twice.
Error 24 is about the uniqueness of alias names. Aliases are normally 1:1 table names, but actually name workareas. Just for convenience by default the workarea alias name is the table name.

But you can't give two different workareas the same name.

If you need the table open in a second workarea, to be able to position on another record than in the first workarea, that's a good reason, but normally you put a table in DE and autoclose at the end, therefore a table is just opened once. Or you even have the need for seperate workareas for starting a new form, then go for private datasessiosn.

If opening a table by code you can safely do so without errors by:

IF NOT USED("table")
   USE table IN 0
ENDIF
SELECT table

That still has two prerequisite to never error: 1. Always only open a table once with the default aliasname and 2. Check for that default alias name in the IF USED() if statement before using the table, otherwise just SELECT the workarea to "activate" it for further processing via LOCATE, SEEK, etc.

If you rather go for SQL you won't need to care for USING tables. SQL will find tables in either the active Database or current directory or along SET("PATH"). Also the visual DE will handle this on it's own error free.

Bye, Olaf.

 

[Mike Lewis]

Castgb,

I suggest you do the following:

1. When you open the table, give it an explicit alias. For example:

USE &mscheda AGAIN IN 0 ALIAS MyTable

2. Immediately after opening it, SELECT it.

3. At the bottom of the loop, explictly close the table (and release the alias):

USE IN SELECT("MyTable")

So, your code will look something like this (I've left out a few lines to make it clearer):

DO WHILE .NOT. EOF()

.....

IF MNLAV>0
mSCHEDA=ALLTRIM(CURDIR())+"tabelle\"+"LAV"+ALLTRIM(STR(MNLAV))+".dbf"

USE &mscheda AGAIN IN 0 [b]ALIAS MyTable[/b]
[b]SELECT MyTable[/b]
  APPEND BLANK
  REPLACE codiceid WITH ALLTRIM(thisform.text2.Value)
  REPLACE DESCART WITH "MANODOPERA "+ALLTRIM(thisform.text1.Value)

  .....

  [b]USE IN SELECT("MyTable")[/b]

ENDIF

SELECT tmp_dip

GOTO NRECQUALE
SKIP
ENDDO

Try that next, and let us know what happens.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

That would be okay, too. It would aslo close the sepcific table once it's processed.
Next question would be, why one such DBF for each "mnlav". Whatever that number means, you could add it as field, or would that break the 2GB limit?

Bye, Olaf.

 

[TamarGranor]

If someone already mentioned this, I'm not seeing it. You should never use the & for the name of a file. If there's a space in the path, it'll fail.

Change:

USE &mscheda AGAIN IN 0 ALIAS MyTable

to:

USE (mscheda) AGAIN IN 0 ALIAS MyTable

Tamar

 

[castgb]

thanks Olaf
everything is ok, now I understand how to use commands [... Use... Select .. and Alias​​].
Thanks to Tamara did not know that
Use & mscheda ..... Use (mscheda) were different
it is an honor for me to learn from you MikeLewis OlafDoschke TamarGranor
I see your name often in the forums and sites, I'm a programmer in C # and Java and I have many problems with the logic VFP 9 sp2
Microsoft has done wrong to abandon VFP is the best language for database

Thanks for Everything
Gianni Berna Italia

 

[Mike Lewis]

Gianni,

A couple of other points you might like to keep in mind.

First, you probably don't need to create the index every time you run your code. Once you create the index, it will be stay on disk until you delete it. Furthermore, if you create it as a "tag" in a compound index (CDX) file, it will always be updated when you update the table.

So, instead of this:

INDEX ON DATA TO MGG

You should do this, but do it once only, the first time you run your program:

INDEX ON DATA TAG MGG

Then, in your program, after you open the table, do this (instead of SET INDEX):

SET ORDER TO MGG

This will speed up your program because you won't need to create the index every time.

My second point is that you should consider using SCAN / ENDSCAN in place of DO WHILE NOT EOF() / ENDDO. This won't make the program run faster, but it is a simpler construct and requires slightly less code. Check the Help file for more information.

Finally, be sure to come back if you have any more questions.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[TamarGranor]

Actually, Mike, last time I tested, SCAN was 1.5 to 2 times faster than DO WHILE.

Not quite the improvement FOR gives you over DO WHILE for counted loops (an order of magnitude, in my tests), but not to sneeze at.

Tamar

 

[Mike Lewis]

last time I tested, SCAN was 1.5 to 2 times faster than DO WHILE

That's good to know.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy