Trying to calculate new column and append it to existing table 2

[ariansa]

I have two tables (free tables) that are exactly the same except for 1 column. I need to calculate this difference and append the column of differences to one of the original files. I'm doing this from Excel VBA.
Here's what I'm trying to do:

SELECT table1.id, (table1.val - table2.val) AS [hf]
FROM table1, table2
WHERE table1.id = table2id; 'works fine for the first 3 lines
ALTER TABLE table3 ADD hf 'table3 is a copy of table1

I've been able to get the differences, but have been unsuccessful in adding it to an existing table.

The error that I'm getting when I add the ALTER line is "[Microsoft][ODBC Visual FoxPro Driver]File must be opened exclusively".

I've played around with USE and SET EXCLUSIVE ON, but can't seem to get it to work. I've also tried INTO instead of ALTER. That ran, but table3 was unchanged.

I'm guessing I need to do some kind of SET EXCLUSIVE and PACK. Any help?

 

[Mike Lewis]

Seems to me you've got a couple of choices.

Either: do the ALTER TABLE first. But before you do that, you've got to open the table exclusively (with the EXCLUSIVE keyword). Once you've done the ALTER TABLE, the extra column will be permanent.

Then do an UPDATE on table 1. Something like this:

UPDATE Table1 SET Val = Table1.Val - Table2.Val ;
  FROM Table2 WHERE Table2.ID = Table1.ID

This is not tested; it's just to give the general idea.

Or, do a SELECT INTO Table1, in which case Table11 will overwrite the existing table. (But you will still need exclusive use.)

Is there any reason why you need to add the new column every time you run this? If not, just add it once, and use the UPDATE shown above.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Dan Freeman]

Mike hints at this, but it's really quite unusual to add a permanent column to some other application's table on the fly as a matter of routine. It'll usually crash that other application.

You posted this:

SELECT table1.id, (table1.val - table2.val) AS [hf]
FROM table1, table2
WHERE table1.id = table2id; 'works fine for the first 3 lines

(What does the comment mean?)

I'd change it to this:

SELECT *, (table1.val - table2.val) AS [hf]
FROM table1, table2
WHERE table1.id = table2id into table TABLE3
[code]

But that assumes you have exclusive use of table3 in the first place.

The ODBC error is exactly correct. You can't ad-hoc add a column without exclusive use:

[code]
USE table3 EXCLUSIVE

But I'd be willing to bet that if you tell us what you're trying to accomplish (instead of how), you'll get better suggestions.

 

[JRB-Bldr]

" I'm doing this from Excel VBA"

I agree with Dan's comments above.

Another way you might consider doing this would be to purchase a copy of VFP and make the structural changes to the data able through it.

From then on, you would have a tool to use to make similar changes if needed.

Plus if it crashed the other FP/VFP application, you would have a means of putting things back.

Good Luck,
JRB-Bldr

 

[ariansa]

Thanks for the comments. I need to create an Excel tool that the user can input the name of free tables. The tool takes values from both tables in order to create a new field or values. The new field needs to be paired up with the correct record and the new file needs to be in the same format, so I'm having a macro copy the file first, then add in the new field.

This works:
SET EXCLUSIVE ON;
ALTER TABLE table3 ADD COLUMN hf n(6) NULL;
UPDATE table3;
SET hf=1
PACK;

This doesn't work (replacing "1" with query):
SET EXCLUSIVE ON;
ALTER TABLE table3 ADD COLUMN hf n(6) NULL;
UPDATE table3;
SET hf=(SELECT table2.val
FROM table1, table2
WHERE table1.id = table2.id)
PACK;

Error: Function name is missing ).

The actual calculation is much more complicated, but I've simplified it for my testing.

 

[ariansa]

Better example of what's not working:

SET EXCLUSIVE ON;
ALTER TABLE table3 ADD COLUMN hf n(6) NULL;
UPDATE table3;
SET hf=(SELECT table2.val
FROM table2
WHERE id = table2.id)
PACK;

 

[Mike Lewis]

Well, provided you understand that it will attempt add a new column every time it's run - even if there is already a column named hf - then it will do what you want.

By the way, the PACK is irrelevant to the problem that you described. There might or might not be a reason to pack the table, but it's got nothing to do with the basic issue.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Dan Freeman]

What you're posting isn't valid VBA syntax and it isn't valid VFP syntax (all those line continuation characters make it all one big command). What is it?

I don't understand what you're doing with the subquery. It isn't necessary:

UPDATE table3 SET hf = table2.val FROM table2 WHERE table3.id = table2.id

But, honestly, the whole "copy and alter" strategy isn't necessary either:

SELECT table1.*, table2.val AS hf From table1, table2 Where table1.id = table2.id into table Table3

And, as Mike says, the PACK doesn't add anything.

 

[ariansa]

Here's what the code is exactly:

"
Sub CreateHFfile()

Dim Conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Dim sql As String
Dim shock As Double
Dim path As String
Dim basefile As String
Dim shockfile As String
Dim HFfile As String
Dim BaseFileName As String
Dim ShockFileName As String
Dim HFFileName As String

shock = Range("shock").Value
path = Range("path")
basefile = Range("basefile")
shockfile = Range("shockfile")
HFfile = Range("HFfile")
BaseFileName = path & "\" & basefile
ShockFileName = path & "\" & shockfile
HFFileName = path & "\" & HFfile

SQLbase = Left(basefile, Len(basefile) - 4)
SQLshock = Left(shockfile, Len(shockfile) - 4)
SQLnew = Left(HFfile, Len(HFfile) - 4)

sql = "SELECT ((" & SQLshock & ".total_rsv - " & SQLbase & ".total_rsv) / " & _
SQLbase & ".av / " & shock & ") AS [hf] " & _
"FROM " & SQLbase & ", " & SQLshock & _
" WHERE " & SQLbase & ".pol_number = " & SQLshock & ".pol_number " & _
"INTO TABLE " & SQLnew

Conn.ConnectionString = "DSN=Visual FoxPro Tables;SourceDB=" & path & "\"
Conn.Open

rs.Source = sql
rs.ActiveConnection = Conn
rs.Open

Conn.Close
Set Conn = Nothing

End Sub
"

No errors occur, but no file is being created in the location. It does run successfully in foxpro.

 

[ariansa]

Correction

Previous:
"INTO TABLE " & SQLnew

Actual:
"INTO TABLE " & HFFilname

No errors. No file created in location.

 

[Dan Freeman]

After all these posts, this is the first time you've mentioned using ADODB. It's proof that asking a good question gets a good answer.

You're messing with a recordset object which will never beget a DBF.

Instead try this:

Conn.Execute(sql)

You want the remote app to execute a command. You do NOT want the recordset object interpreting the SQL command you're sending and presenting you with a transient cursor.

 

[Dan Freeman]

Oh, and don't forget Conn.Execute("Close All") when you're done!