import data from FoxPro database (ISIS)

[Amanda Byrne]

I work for a non-profit wildcat sanctuary. In the past they used a program
called ARKs to track their animal records. ARKs is a the front end for an
ISIS database- which is a zoo-industry standard. Our facility backed up this
database in 2001, and it hasn't been touched since.

I recently restored these files in an effort to make that information
available to us again, but can't figure out how to import the data into
Access or Excel. We no longer have the ARKS software to open the data and
export it. The database appears to be FoxPro based, and I even found a text
document that gives a breakdown of the table structure, so I know exactly
which files contain the necessary data.

The files typically have .dbc, .dct, .dcx extensions. The files that
contain the data I need are .dbf. I have no idea what version of FoxPro this
might be- but have had no luck trying to import the dbf files using any of
file types available in the file type dropdown (dbase5, dbaseIII, dbaseIV).
I get an error that reads "External table is not in the expected format."

How else might I go about getting into this data?

Thanks, Amanda

 

[Cindy Winegarden]

Hi Amanda,

Yes, you've got Visual FoxPro files. The DBC table is a "database container"
where things like stored procedures and view definitions are stored. The DCX
is the index for the DBC table and the DCT is the contents of any memo
fields. In addition to the DBC you should have DBFs which are the actual
FoxPro tables; CDX is the optional indexand FPT is the optional memo,
although you need to have both present since the table knows whether it has
them or not.

Over time new data features have been added to FoxPro tables, making them to
be no longer compatible with Jet. However, you can probably use the FoxPro
and Visual Foxpro ODBC driver to link the tables to your MDB. However, with
VFP7 and above, even more data features were added making the tables only
accessible via OLE DB and ADO. However, tables created with VFP7 and above
that don't have the new features are still compatible with ODBC. If you use
ODBC, the connection string should point to the DBC itself (if there is no
DBC present you would use the "free" table option and point only to the
directory where the DBFs are stored. A typical OLE DB connection string is
"Provider=VFPOLEDB.1;Data Source=MyPath\MyDBC.dbc;" or for free tables:
"Provider=VFPOLEDB.1;Data Source=MyPath;"

The ODBC driver and OLE DB data provider are downloadable from the links
below my signature. The OLE DB data provider works with all versions of
FoxPro tables and is totally backward compatible.

With OLE DB your SQL statement should refer to the table itself such as
"Select * From MyTable" without the DBF extension and without referencing
the DBC.

--
Cindy Winegarden
(e-mail address removed)


VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx

 

[Amanda Byrne]

I have the ODBC driver on my computer. I right click in the Tables window of
a blank Access database, and select Link Tables. I change the file type to
odbc and set up the DSN. If I set up the dsn as a Machine system connection,
I see the list of tables I need, and the link is created, but when I try to
open the link I get this error:

ODBC-call failed.
[Microsoft]. [ODBC Visual FoxPro driver] Cannot resolve backlink. (#976)

I'm a little confused about setting up the OLE DB stuff, do you have to
create the ODBC link and thien configure the OLE DB connection? How do I do
this?

- A

 

[Amanda Byrne]

Well, I tried importing the table as opposed to linking it with the same
steps, and this time it worked. Thanks!

 

[Cindy Winegarden]

Hi Amanda,

This error usually occurs when a table is part of a Database Container and
the table or DBC has moved relative to the other one. Is there a DBC file
present with the DBFs? Were you using the DBC option or free table option in
your connection string?

--
Cindy Winegarden
(e-mail address removed)


VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx

I have the ODBC driver on my computer. I right click in the Tables window
of
a blank Access database, and select Link Tables. I change the file type
to
odbc and set up the DSN. If I set up the dsn as a Machine system
connection,
I see the list of tables I need, and the link is created, but when I try
to
open the link I get this error:

ODBC-call failed.
[Microsoft]. [ODBC Visual FoxPro driver] Cannot resolve backlink. (#976)

I'm a little confused about setting up the OLE DB stuff, do you have to
create the ODBC link and thien configure the OLE DB connection? How do I
do
this?

- A

Hi Amanda,

Yes, you've got Visual FoxPro files. The DBC table is a "database
container"
where things like stored procedures and view definitions are stored. The
DCX
is the index for the DBC table and the DCT is the contents of any memo
fields. In addition to the DBC you should have DBFs which are the actual
FoxPro tables; CDX is the optional indexand FPT is the optional memo,
although you need to have both present since the table knows whether it
has
them or not.

Over time new data features have been added to FoxPro tables, making them
to
be no longer compatible with Jet. However, you can probably use the
FoxPro
and Visual Foxpro ODBC driver to link the tables to your MDB. However,
with
VFP7 and above, even more data features were added making the tables only
accessible via OLE DB and ADO. However, tables created with VFP7 and
above
that don't have the new features are still compatible with ODBC. If you
use
ODBC, the connection string should point to the DBC itself (if there is
no
DBC present you would use the "free" table option and point only to the
directory where the DBFs are stored. A typical OLE DB connection string
is
"Provider=VFPOLEDB.1;Data Source=MyPath\MyDBC.dbc;" or for free tables:
"Provider=VFPOLEDB.1;Data Source=MyPath;"

The ODBC driver and OLE DB data provider are downloadable from the links
below my signature. The OLE DB data provider works with all versions of
FoxPro tables and is totally backward compatible.

With OLE DB your SQL statement should refer to the table itself such as
"Select * From MyTable" without the DBF extension and without referencing
the DBC.

--
Cindy Winegarden
(e-mail address removed)


VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx