use table where table is pulled from another table 1

[Judi201]

Hi!
I can't believe I have spent the day on this. If it has been answered before, I can't find it.

I have a table that contains all of the tables in my project
in field tblname.

I am trying to loop through the table and use each table to copy structure extended to a file using another field in the table, tblstru.

Simply, I need to :

FOR 1 TO lnrecs
&& get field from opentable - tblname
&& use tblname
&& copy struc extended to tblstru
&& back to opentable
&& to get next record
ENDFOR

Nothing that I have tried has worked. I read one thread about using macro substitution correctly and using .. but I can't seem to get FOX to see the field data as a table.
ANY help would be appreciated.

Judi

 

[alvechurchdata]

What syntax are you using?

USE (MyTable.tblName) should open the table and COPY STRUCTURE EXTENDED TO (MyTable.tblstru) should copy the structure.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[mspratt]

Hi Judi,

You can also use macro expansion.

FOR 1 TO lnrecs
   && get field from opentable - tblname
   myVar = tblname
   && use tblname
   select 0 && otherwise we close the table of tables
   use &myVar.
   && copy struc extended to tblstru   
   && back to opentable
   && to get next record
ENDFOR

I would also consider using SCAN - ENDSCAN.

Regards,

Mike

 

[Mike Lewis]

Hi Judi,

You should definitely be using SCAN / ENDSCAN rather than a FOR loop. If you do, the rest of what you are doing should fall into place.

The only remaining problem will be in opening the table, when you don't know in advance the table name. Geoff has given you the answer to that -- you place parentheses around the variable that holds the name.

Macro substition would also work, but using parentheses in this way is faster and more reliable.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Judi201]

Thanks Geoff , Mike.
You are telling me what I thought but for some reason I can't get it right. I will play with it some more.

Thanks for the quick response. Just seeing your answers tell me I am on the right track and I will work it out.

Judi

It's 'gonna' be a long night

 

[Judi201]

Mike L,
Again, I did not see your response when I sent the last post. Sorry.

You should definitely be using SCAN / ENDSCAN rather than a FOR loop. If you do, the rest of what you are doing should fall into place.

I tried this and it worked perfectly, but I surely don't understand the difference. Could you add to what you said?

lcTable = "pjtTables"  &&this is coming in from othe code
CLEAR
SET DEFAULT TO C:\MYPROJECTS\DEVELOPMENT\TABLES
USE (lcTable)      
SCAN ALL		
	lctbl = tblname
	lcstr = tblstru
	SELECT 0
	USE (lctbl)
	COPY STRUCTURE EXTENDED TO  (lcstr)
	USE
	SELECT (lcTable)
ENDSCAN

How could I write this without the use of lctbl and lcstr?

Thanks again for all help.

Judi

 

[alvechurchdata]

How could I write this without the use of lctbl and lcstr?

I suspect that your problem is in the COPY TO command. You have to be in the table from which you're copying so you have use the table and field name to get the name of the file to which you're copying. My example assumed that I could quote the table name directly but you need to substitute for that as well. Something like:

Copy Structure Extended to &lcTable..tblStru

But I wouldn't bother. I'd keep the code as it is because it's clearer and easier to debug.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Judi201]

Geoff,

Thanks, I will leave it as is. It is just a part of a little maintenance routine. But I am amazed at how many little things I could not make work. I am too much into forms and such I guess. I have one more question for now and it probably should be a new thread but here goes.

How can I delete all files in a dir (known dir) that have the skeleton stru*.dbf. I need to do something like:

IF FILE('stru*.dbf')
DELETE FILE('stru*.dbf')
ENDIF

Many Thanks.

Judi

 

[alvechurchdata]

How about:

ERASE Stru*.dbf

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[mspratt]

Hi Judi,

SCAN by itself will default to SCAN ALL. Also SCAN will automagically select it's starting work area, so SELECT (lcTable) isn't necessary before ENDSCAN.

You may want to wrap the code to test for a blank record, though.

lcTable = "pjtTables"  &&this is coming in from othe code
CLEAR
SET DEFAULT TO C:\MYPROJECTS\DEVELOPMENT\TABLES
USE (lcTable)      
SCAN
   IF ! EMPTY(tblname) && or "" != ALLTRIM(tblname)
      lctbl = tblname
      lcstr = tblstru
      SELECT 0
      USE (lctbl)
      COPY STRUCTURE EXTENDED TO  (lcstr)
      USE
      *SELECT (lcTable) && This isn't needed
   ENDIF
ENDSCAN

Regards,

Mike

 

[Judi201]

Mike,

Thaks for this. It helps me to see why SCAN is better than my way. I have never used SCAN but will start now.

Thanks to all. I'm on to other 'issues'

Judi

 

[Judi201]

Mike,
Thanks for the code. I know I am going hate I asked this but...

Please explain:

<< SELECT (SELECT("SourceTable")) >>

As I have said many times before, it is usually the obvious that I miss.

Judi

 

[Judi201]

OK... maybe it should have been obvious, but not for me. It is a cool piece of code and I am sticking it in!!

Thanks for the lesson. Have a star!

Judi