fixnix
asked on
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!!
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!!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
ASKER
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?
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?
ASKER
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?
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?
ASKER
Might have figured it out...used machine data source instead of file data source this time....seems to have worked
ASKER
Sweet! I should be able to take it from here...
THANKS MUCH!!!!
THANKS MUCH!!!!
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
God bless!
Sam
just get external data -> linked tables in access and select file type .dbf