• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

Using Access to edit a Visual Fox Pro database

Hi fellow experts!  DB's aren't my forté, so here goes:

I'm on a long term project at a client setting up and entering the data into a program called Advisor's Assistant.
Advisor's Assistant uses Visual Fox Pro (I determined this from occasional errors like "Error:  Cannot Exit Visual Fox Pro").  AA's interface although fine for day to day updates, is pretty lacking for initial data conversion (most of our client data is now in Excel spreadsheets).

We do not have Visual Fox Pro installed, just the engine that AA uses.

We do have Access 2003.  In the past, I've often found it easier to work directly with Access's tables instead of using whatever "Input Forms" others had created...especially when making hundreds of identical changes to different records (like changing all the instances of "purple" in Field <x> to "violet", or when adding a new field and giving the first 300 records one value, the next 400 or so another, etc...just pasting the appropriate vales into the cells)

I would like to do the same kind of thing here, since I need to enter hundreds of the same value to certain fields after doing an import from .cvs into AA (their import process is limited in what fields can be populated).  I never used Visual Fox Pro, and am not familiar with it's file structure.  Can anyone give me ponters as to how to open the VFP db in Access?  Is there a plug-in that would allow this, can Access do it natively, or do I just have a case of wishful thinking?

Thanks!!
0
fixnix
Asked:
fixnix
  • 6
  • 3
  • 2
2 Solutions
 
SidFishesCommented:
you can set up Foxpro as an odbc datasource and then use get external data -> linked tables in access and select file type ODBC database...

0
 
SidFishesCommented:
and you may even be able to connect directly foxpro uses .dbf right?

just get external data -> linked tables in access and select file type .dbf

0
 
will_scarlet7Commented:
   Unfortunately Access 2003 cannot connect directly to Foxpro databases that include a memo field, so you would have to setup and use an ODBC source, however Foxpro databases are not updateable through ODBC. You would have to update the tables via ADO in code, or import them update them and then re-export...
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
fixnixAuthor Commented:
Sid: as for connecting directly, Foxpro does use .dbf but Access lists only dBase III, IV, and V files for type .dbf so apparently directly opening them isn't an option.

Sid & will:  as for using ODBC, I've never done that and don't even know what ODBC is...I was poking around and saw mention of needing an ODBC driver for the filetype.  I'm out of my league...but tend to pick things up quickly.  

will:  I have even less of a clue what "updating tables via ADO in code" means (guessing VB code and ADO="Access Data Object" but I've not even done a "hello world" in VB...only code I've dabbled in has been in 'nix shells, TK/TCL and a smidgin of python and perl and a few string changes in other's C code...oh yeah, and html but I have trouble calling that "code" ;)), but your "or" suggestion of importing, making changes, then exporting seems like a possible route (I'm thinking that can be done just using ODBC?  It's a humbling experience to be a n00b again :))

Before I go any farther, how involved and complicated do you guys think this will be?  (best guess)  I'd rather just troll along doing the updates one by one using the AA app than to spend a week learning how to get ODBC and/or updating the data objects via code and possibly still not get it right.  I'll start googling Access ODBC in the mean time, but could someone suggest some kind of tutorial link to get me started?
0
 
will_scarlet7Commented:
   *If your FoxPro tables do not have any Memo fields then it should be fairly simple to like to them directly using the dBase III driver.
    *If they contain Memo fields then the simplest (I think) would be to install the Visual FoxPro ODBC driver (see link below), then import your tables edit them and re-export them (using the Visual FoxPro driver).

http://msdn.microsoft.com/vfoxpro/downloads/updates/odbc/default.aspx

If you need to work with the ODBC I can walk you through the process...
0
 
fixnixAuthor Commented:
Thanks a lot!!!  It's sounding simpler now :).  I don't know if my tables are using memo fields or not...I guess I'll find out when I try to get external data for filetype dBaseIII .dbf.  If it doesn't work I'll grab the ODBC driver from your link.  Gonna play around with copies of the actual db files first and see what works or where I get stuck.  Will keep y'all posted...
0
 
fixnixAuthor Commented:
Couldn't directly open using dBase III.

I installed the Visual FoxPro OBDC driver, then tried to import an ODBC stream using that driver, named a test file, then when asked for the table filename, I pointed it to a (copy of) the .dbf table I believe to be the one I wish to manipulate data in.  I got an error box stating:

"Reserved error (-7778); there is no message for this error"


What do you think I should I try next?
0
 
fixnixAuthor Commented:
the "test file" I referred to was test.dsn (meant to edit that before posting...forgot the extension then posted before going back to check the filename)

I'm not sure what a DSN Name is or if I was supposed to sreate a new one...perhaps that's where I messed up?
0
 
fixnixAuthor Commented:
Might have figured it out...used machine data source instead of file data source this time....seems to have worked
0
 
fixnixAuthor Commented:
Sweet!  I should be able to take it from here...

THANKS MUCH!!!!
0
 
will_scarlet7Commented:
Sorry... I was out for a bit. Glad to hear you figured it out, and glad to have been able to help.
God bless!

Sam
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now