ODBC link to Visual Foxpro leaves some tables not visible

[Guest]

When I attempt to set up an ODBC link in Access 2000 to another application
which uses Visual Foxpro, some of the tables are visible in the dialog box
where I can select to which table I want to link, but others are not. Why
can I not see all the tables?

I am using MDAC v2.8 SP1 and a Machine DSN. When I attempt to use a file
DSN, I get reserved error -7778 as described in kb212886.

 

[Cindy Winegarden]

Hi Brian,

Which ODBC driver are you using? The latest VFP ODBC driver is no longer
included in MDAC; it's downloadable from
msdn.microsoft.com/vfoxpro/downloads/updates/odbc.

Is the "other application" an Access application or a FoxPro or Visual
FoxPro application?

Do you know which version of FoxPro the DBFs were created with? DBFs created
with FoxPro 2.6 and earlier are natively accessible by Access, DBFs created
with VFP6 and earlier are accessible via the FoxPro and Visual FoxPro ODBC
driver (mentioned above) and DBFs using any of the new data features added
in VFP7 and later are only accessible via OLE DB and ADO. However, DBFs
created with VFP7 and later that do not use any of the new data features are
ODBC compatible.

 

[Guest]

I think I have the latest ODBC driver. The URL you mentioned downloads file
vfpodbc.msi. When I launch it, I get the message "The VFPODBC driver is not
supported on Windows 2000 since it is already installed with the operating
system". I find that ODBC drivers for VFP are contained in
<windows>\system32\vfpodbc.dll. I have version 6.1.8630.1 of this file dated
2003-6-19.

The other application is a Visual Foxpro Application and was generated with
version 8 of VFP. I think you have identified the problem when you state
that DBF's using new features added in v7 or later are not accessible from
ODBC. For example, one table I cannot see contains memo fields which an
earlier version of the same application did not.

I guess I will have to learn about OLE DB and ADO which I have never used
before. I see the help file for Access 2000 contains a description of them.
Do these tools give me what I need to link to VFP?

 

[Cindy Winegarden]

Hi Brian,

The other application is a Visual Foxpro Application and was generated
with
version 8 of VFP. I think you have identified the problem when you state
that DBF's using new features added in v7 or later are not accessible from
ODBC. For example, one table I cannot see contains memo fields which an
earlier version of the same application did not.

Memo fields have been the same through all versions of FoxPro so they should
not affect your ability to link to the DBFs.


Is there a DBC file present in the directory where the DBFs are? FoxPro
tables can be "free" tables or they can be associated with a Database
Container or DBC file. If there is a DBC file present you should link to it,
otherwise use the free table option in the ODBC data source setup. Also,
it's possible that there are both free tables and DBC-associated tables in
the directory.

I guess I will have to learn about OLE DB and ADO which I have never used
before. I see the help file for Access 2000 contains a description of
them.
Do these tools give me what I need to link to VFP?

You cannot link tables via OLE DB and ADO - only via ODBC. You can read
tables but this has to be done in program code.

 

[Guest]

Thanks again. The other application does not have a .dbc file. I already
use the free table option in my ODBC setup. As I mentioned before, I have to
create a Machine DSN because if I attempt to create a File DSN, I get
reserved error -7778. After I select the free table option, a dialog box is
displayed showing a list of the available .dbf files. It is this list which
is missing some of the tables which I know exist, as I can see them in
Windows Explorer.

It was the vendor of the VFP application who suggested that the use of memo
fields was the problem. I do not myself use VFP, so was unaware that they
always existed and I was sceptical of his explanation, as some of the tables
that do not appear do not have memo fields.

Can you point me to a description of the new features added at v7 of VFP?
Maybe he has used some other feature which is causing the problem.

I am trying to become familiar with OLE DB and ADO. So far, I have
installed MSDE on my machine, but am having difficulty making further
progress. Can you suggest a resource that I should use?

 

[Cindy Winegarden]

Hi Brian,

... After I select the free table option, a dialog box is
displayed showing a list of the available .dbf files. It is this list
which
is missing some of the tables which I know exist, as I can see them in
Windows Explorer.

Can you point me to a description of the new features added at v7 of VFP?
Maybe he has used some other feature which is causing the problem.

There are some data features that are dependent on having a Database
Container but since a DBF file is not present you don't need to worry about
those features. One that affects free tables also is an auto-incrememting
field type, added in VFP8. You can read about new data features in the Help
of each version which is in the MSDN Library under "Development Tools and
Languages." Look for a "What's New" section for each VFP version.

I am trying to become familiar with OLE DB and ADO. So far, I have
installed MSDE on my machine, but am having difficulty making further
progress. Can you suggest a resource that I should use?

MSDE is not OLE DB or ADO. OLE DB and ADO are data access technologies. MSDE
is "SQL Server Lite" which is a back end database like DBFs are the native
back end database for FoxPro.

Using OLE DB and ADO in Access is slightly different in Access than it is in
Visual FoxPro. I'd post this as a separate question and hopefully one of the
Access experts will see your question.

 

[Guest]

You have found it! The tables that are not visible are using the
autoincrementing data type. An article in the MSDN library states that, if
this feature is used, the byte at offset 0 in the dbf header is changed from
0x30 to 0x31. I made a copy of one of the files and changed that byte back
to 0x30. Now the table is visible!

Since I cannot expect my users to do that, I think I should continue to look
into OLE DB and ADO. If I understand correctly, I should be able to create a
database using MSDE and upload the data in the VFP tables. Then I can
download or link to the data in the MSDE database from Access. Is that
correct?

I have not yet seen the question you say you will post concerning OLE DB and
ADO. Will it be in the Import/Export Data section of the Access Database
discussion group?

Thanks for your help.

 

[Cindy Winegarden]

Hi Brian,

If you're going to go the SQL Server route you should look into SQL Server
2005 Express which is the successor to MSDE and is also free of charge.
However, if you're going to upload data to MSDE think about whether this
will be a one-time operation or will your users be able to do it
successfully themselves? Do you need real-time access to the data or will an
upload that is refreshed often fill your need?

Please post your own questions about using ADO with Access in an appropriate
newsgroup. When I said "I'd" I meant "if I were you." Sorry for the
misunderstanding.

 

[Guest]

Thanks again. Your responses have been very helpful.