[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Using Access to edit a Visual Fox Pro database

Posted on 2005-04-13
11
Medium Priority
?
479 Views
Last Modified: 2009-07-08
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
Comment
Question by:fixnix
  • 6
  • 3
  • 2
11 Comments
 
LVL 36

Assisted Solution

by:SidFishes
SidFishes earned 200 total points
ID: 13773608
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 13773622
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
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 13773684
   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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Author Comment

by:fixnix
ID: 13774037
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
 
LVL 15

Accepted Solution

by:
will_scarlet7 earned 1800 total points
ID: 13774137
   *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
 
LVL 9

Author Comment

by:fixnix
ID: 13774294
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
 
LVL 9

Author Comment

by:fixnix
ID: 13775180
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
 
LVL 9

Author Comment

by:fixnix
ID: 13775194
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
 
LVL 9

Author Comment

by:fixnix
ID: 13775214
Might have figured it out...used machine data source instead of file data source this time....seems to have worked
0
 
LVL 9

Author Comment

by:fixnix
ID: 13775222
Sweet!  I should be able to take it from here...

THANKS MUCH!!!!
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 13775856
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question