×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

using other dbms to access foxpro table

using other dbms to access foxpro table

using other dbms to access foxpro table

(OP)
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.

RE: using other dbms to access foxpro table

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

David W. Grewe
Dave@internationalbid.net

RE: using other dbms to access foxpro table

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.

Jon Hawkins
jonscott8@yahoo.com

Carpe Diem! - Seize the Day!

RE: using other dbms to access foxpro table

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.

Robert Bradley

FoxDev - Visual FoxPro Development

RE: using other dbms to access foxpro table

(OP)

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!!

RE: using other dbms to access foxpro table

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:
http://www.softserv-inc.com/sscommand.html

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.

Jon Hawkins
jonscott8@yahoo.com

Carpe Diem! - Seize the Day!

RE: using other dbms to access foxpro table

(OP)
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

RE: using other dbms to access foxpro table

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

Robert Bradley

FoxDev - Visual FoxPro Development

RE: using other dbms to access foxpro table

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close