Is it possible to use another DBMS package to access FoxPro 5.0 tables? I have some tables that are used by the accounting program SBT. (Now ACCPAC). Due to an error, there are some fields that are bad. I have written and tested the view and update statements in SQL I need to use to fix the tables. Since the full version of FoxPro is not loaded at the customer site - it will not let me build the views. I have SQL server7, ORACLE8, Informix, Sybase, IBM DB2 that I can load and use. The tables are free-standing Foxpro 5.0 tables (there is no "database" that is being used with this application.) The SBT support people say I am way over their head. I would appreciate any ideas. Another help would be if you know of a down-loadable copy of FoxPro that I could use for just a day or so - an evaluation copy, etc..?? Or an evaluation CD I could order. I don't want to buy the thing just for this.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
using other dbms to access foxpro table
- Thread starter efassett
- Start date
To the best of my knowledge there is no evaluation version of VFP.
On a weekend when most businesses do not do accounting work try this
CanDoWork = .f.
do case
case "you have access to a computer with a CD Writer."
CanDoWork=.t.
case "you have access to a computer with a tape backup system"
CanDoWork=.t.
case "You have access to a computer with a removable storage device"
CanDoWork=.t.
case "you can transfer files over the internet"
CanDoWork=.t.
endcase
if CanDoWork
copy customer files to CD.
copy customer files to a backup location (also)
go to computer with VFP installed.
Install customer files
Alter data in tables.
rewrite new files to CD
go back to customer site.
Install new files.
endif
On a weekend when most businesses do not do accounting work try this
CanDoWork = .f.
do case
case "you have access to a computer with a CD Writer."
CanDoWork=.t.
case "you have access to a computer with a tape backup system"
CanDoWork=.t.
case "You have access to a computer with a removable storage device"
CanDoWork=.t.
case "you can transfer files over the internet"
CanDoWork=.t.
endcase
if CanDoWork
copy customer files to CD.
copy customer files to a backup location (also)
go to computer with VFP installed.
Install customer files
Alter data in tables.
rewrite new files to CD
go back to customer site.
Install new files.
endif
CanDoWork = .f.
do case
case "you have access to a computer with a CD Writer."
CanDoWork=.t.
case "you have access to a computer with a tape backup system"
CanDoWork=.t.
case "You have access to a computer with a removable storage device"
CanDoWork=.t.
case "you can transfer files over the internet"
CanDoWork=.t.
endcase
if CanDoWork
copy customer files to CD.
copy customer files to a backup location (also)
go to computer with VFP installed.
Install customer files
Alter data in tables.
rewrite new files to CD
go back to customer site.
Install new files.
endif
I'm having flashbacks to COBOL. Ick! I think I'm gonna go take some Tylenol now.
do case
case "you have access to a computer with a CD Writer."
CanDoWork=.t.
case "you have access to a computer with a tape backup system"
CanDoWork=.t.
case "You have access to a computer with a removable storage device"
CanDoWork=.t.
case "you can transfer files over the internet"
CanDoWork=.t.
endcase
if CanDoWork
copy customer files to CD.
copy customer files to a backup location (also)
go to computer with VFP installed.
Install customer files
Alter data in tables.
rewrite new files to CD
go back to customer site.
Install new files.
endif
I'm having flashbacks to COBOL. Ick! I think I'm gonna go take some Tylenol now.
Are the problems with the structure and itegrity of the tables themselves, or problems with the data?
If its data, you could use something like Access <shudder> to access (NPI) and change the data via ODBC, assuming you have a VFP ODBC driver installed on one of the machines.
If its data, you could use something like Access <shudder> to access (NPI) and change the data via ODBC, assuming you have a VFP ODBC driver installed on one of the machines.
- Thread starter
- #5
Thanks for the ideas - BUT (there's always a big but)
I don't have Access. I don't know of anybody nearby who has VFP loaded (We live in a small town). I only have access to the DB software I mentioned. (They are evaluation copies!! How sad that VFP doesn't provide the same.)
The problem is with the data. An improperly installed update to the software caused the yearly net and the yearly gross data fields to not be updated at the close of the quarters. I was hoping to use my handy-dandy update statement to fix the table rather than modifying each employee's data by hand.
Any of you VFP experts could maybe help me figure out how to do this with the parts of the VFP I have loaded. I will install it here at home and later tonight try to give you the exact error message I am receiving.
THANKS!!
I have written and tested the view and update statements in SQL I need to use to fix the tables.
With what software did you write & test the SQLs?
I don't know of anybody nearby who has VFP loaded (We live in a small town)
Does this mean that you dont own a copy of VFP?
If you know anything about VFP, check out this utility:
It's a shareware program that emulates VFP's command window and can execute any native VFP command. You can use it to modify your data. Here's a plug:
SS Command was designed to help in situations where you have a VFP application running (compiled) at a client site, and the client does not own Visual FoxPro. It emulates the VFP Command Window, allowing you to perform virtually any FoxPro command. It frees you as a developer from having to tote you notebook computer with you when you've got data maintenance or report modifications to make on site. It also makes life much easier if you use remote dial-in software for application maintenance.
FWIW, I havent used this product, so you'll have to determine it's worth. Good luck.
With what software did you write & test the SQLs?
I don't know of anybody nearby who has VFP loaded (We live in a small town)
Does this mean that you dont own a copy of VFP?
If you know anything about VFP, check out this utility:
It's a shareware program that emulates VFP's command window and can execute any native VFP command. You can use it to modify your data. Here's a plug:
SS Command was designed to help in situations where you have a VFP application running (compiled) at a client site, and the client does not own Visual FoxPro. It emulates the VFP Command Window, allowing you to perform virtually any FoxPro command. It frees you as a developer from having to tote you notebook computer with you when you've got data maintenance or report modifications to make on site. It also makes life much easier if you use remote dial-in software for application maintenance.
FWIW, I havent used this product, so you'll have to determine it's worth. Good luck.
- Thread starter
- #7
I tested the SQL using Oracle8. I have been doing a little research and I think the problem is how I am defining the update statement. Here is the SQL that works with oracle8
create view view1 as select fld1, fld2 from table where...
update table1 set(field1,field2)=(select fld1,fld2 from view1 where table.employee = view.employee)
I am creating a list of data with the view that I need to insert into a table where the employee in the table = employee in the view. This worked fine in Oracle8.
In the VFP documentation (online) it defines the update command as:
UPDATE [DatabaseName1!]TableName1
SET Column_Name1 = eExpression1
[, Column_Name2 = eExpression2 ...]
WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]
How do I set column_name1 = (select field from the view) ?
When I run the update statement I get the error:
"function name is missing)."
Could I just have a syntax problem??
Thank you for any help!!
Evelyn Fassett
create view view1 as select fld1, fld2 from table where...
update table1 set(field1,field2)=(select fld1,fld2 from view1 where table.employee = view.employee)
I am creating a list of data with the view that I need to insert into a table where the employee in the table = employee in the view. This worked fine in Oracle8.
In the VFP documentation (online) it defines the update command as:
UPDATE [DatabaseName1!]TableName1
SET Column_Name1 = eExpression1
[, Column_Name2 = eExpression2 ...]
WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]
How do I set column_name1 = (select field from the view) ?
When I run the update statement I get the error:
"function name is missing)."
Could I just have a syntax problem??
Thank you for any help!!
Evelyn Fassett
You won't be able to use this syntax:
update table1 set(field1,field2)=(select fld1,fld2 from view1 where table.employee = view.employee)
with VFP (I don't think it'll work with MS SQLS, either). You'll have to make it like this:
update table1 set field1=this, field2=that
update table1 set(field1,field2)=(select fld1,fld2 from view1 where table.employee = view.employee)
with VFP (I don't think it'll work with MS SQLS, either). You'll have to make it like this:
update table1 set field1=this, field2=that
- Thread starter
- #9
Dear Robert (and any other takers),
First, thank you for taking the time to respond.
Isn't there any way with FoxPro to update a table with the results from a select statement?? This seems like a pretty common requirement. One doesn't always have a constant to put into a field. Does anyone know if this would work in Access?? The client I am working for does have access to Access... ;-)
In case someone has a better solution - what I am trying to do is update two fields - yearly gross and yearly net with the sum of the quarterly net and quarterly gross fields. A VERY simple thing in a spreadsheet.. A miserable task so far with a database. First I am selecting these fields using the sum() function to get my quarterly totals - that is what I am putting into the view. Then I want to get these sums and put them into the yearly total field. Seems like such a simple thing and you can't believe the hours I have spent trying to make this work - finally after getting it to work perfectly in Oracle - I thought it would be a straight-forward thing to use the SQL commands in VFP.. Alas - no such luck. I am using a view because I just couldn't make it work going straight from the select with sum() into the update fields.
Thank anyone who is willing to help a poor soul in distress.
Evelyn Fassett
First, thank you for taking the time to respond.
Isn't there any way with FoxPro to update a table with the results from a select statement?? This seems like a pretty common requirement. One doesn't always have a constant to put into a field. Does anyone know if this would work in Access?? The client I am working for does have access to Access... ;-)
In case someone has a better solution - what I am trying to do is update two fields - yearly gross and yearly net with the sum of the quarterly net and quarterly gross fields. A VERY simple thing in a spreadsheet.. A miserable task so far with a database. First I am selecting these fields using the sum() function to get my quarterly totals - that is what I am putting into the view. Then I want to get these sums and put them into the yearly total field. Seems like such a simple thing and you can't believe the hours I have spent trying to make this work - finally after getting it to work perfectly in Oracle - I thought it would be a straight-forward thing to use the SQL commands in VFP.. Alas - no such luck. I am using a view because I just couldn't make it work going straight from the select with sum() into the update fields.
Thank anyone who is willing to help a poor soul in distress.
Evelyn Fassett
- Status
Similar threads
