> Andy,
> How could I use ODBC with VFP database and dbf? I use VFP as front end,VFP database and dbf as back end. How could I use ODBC to link the two? What files should I install on my PC to use ODBC? Could you please teach me in details?
> Regards,
> Nilson




Hi Nilson,

The following information is my 2-cents worth and others way have various pros and
cons to your question. No answer is the correct answer, as we are dealing with
a debate that has been ongoing for some time now.

I just felt I had to jump in here as I think that what you really need to consider
is your future plans for what functionality you want to achieve and what data you
want to access now and in the future.

Here is some well know facts about the question you raised:
OLE DB and ODBC are both specifications created by Microsoft to address universal data access. Each is intended as an industry standard that will make access between one vendor’s data store and another vendor’s data consumer seamless.

Microsoft’s definition of OLE DB is (and taken from their web site):
A strategic system-level programming interface to data across the organization. OLE DB
is an open specification designed to build on the success of ODBC by providing an open
standard for accessing all kinds of data.

And the definition of ODBC is an industry standard and a component of Microsoft Windows®
Open Services Architecture (WOSA). The ODBC interface makes it possible for applications to access data from a variety of database management systems (DBMSs). ODBC permits
maximum interoperability—an application can access data in diverse DBMSs through a
single interface. Furthermore, that application will be independent of any DBMS from
which it accesses data. Users of the application can add software components called
drivers, which create an interface between an application and a specific DBMS.

The two primary differences between the two are:
1. OLE DB is a component based specification and ODBC is a procedural based
specification. SQL is the core of accessing data using ODBC but just one of the
means of data access through OLE DB.
2. ODBC is constrained to relational data stores; OLE DB supports all forms of data stores (relational, hierarchical, etc)

In general, OLE DB provides a richer and more flexible interface for data access because it is not tightly bound to a command syntax (like SQL in the case of ODBC). As Microsoft points out:
Whereas ODBC was created to access relational databases, OLE DB is designed for
relational and non-relational information sources, including mainframe ISAM/VSAM
and hierarchical databases; e-mail and file system stores; text, graphical, and
geographical data; custom business objects; and more.

ODBC on the other hand is a more established interface because it has been around longer; there are proven drivers and applications available in the market place. OLE DB is creating a better known presence, but consumer support is probably still considered limited. With the release of Windows 2000 Professional, OLE DB will be installed as part
of the operating system. In Microsoft Office2000 all member applications will be OLE DB consumers. Thomas Cox does a good job of outlining ODBC’s limitations in his SUGI paper "What’s up with OLE DB?". In recap they are:
- tightly bound to the SQL language and relational data
- no standard leveling of functionality (i.e. drivers do not support standard levels of functionality)
- each flavor of SQL makes each driver unique undermining the universal intent of the API
- difficult to deploy due since many software pieces must be synchronized (server, driver, OS, etc)
Microsoft created OLE DB to address these issues and better support Internet integration and multi-threading.


Political pros and cons
=======================
The debate over ODBC versus OLE DB boils down to two areas: political and technical.
In the political arena, we have the "because Microsoft said so" argument against the "Microsoft always changes their mind" counterpoint. Over the past two to three years, Microsoft has been heavily touting OLE DB as the successor to ODBC. At PDC 98 they went as far as to state that there would no longer be continuing to develop ODBC (they
would fix problems in their drivers, as needed, but not pursue future versions of the API). They plan to invest all new development into OLE DB.
So the politically correct thing to tell a user that asks which technology is to start migrating their applications to OLE DB because it is the industry direction:
OLE DB is Microsoft’s answer to universal data access in all forms (rectangular, relational, hierarchical, multi-dimensional, etc.)
With the next major release of Microsoft’s flagship products (its operating systems, and Office suite) OLE DB will be fully integrated in Microsoft’s desktop environment.
Microsoft’s RAD tools (VisualBasic, Visual C++, Visual Interdev) currently support OLE DB.
In following this line of reasoning, we anticipate that our own ODBC drivers will eventually be replaced by OLE DB providers. Users should consider when and how they may be able to migrate their applications from ODBC to OLE DB if this trend is realized.

Technical pros and cons
=======================
Given the frequent political controversies around Microsoft’s marketing decisions, it is also important (probably more so than the political motivations!) to share the technical pros and cons between ODBC and OLE DB when talking to users:
If the user wants to access data independently of the SQL language, he or she should migrate to OLE DB.
As pointed out, ODBC is bound to the SQL language. If the user’s problem can best be solved with direct table manipulation, OLE DB is a better solution.
If the user wants parallel interfaces for rectangular and multi-dimensional data, he or she should migrate to OLE DB.
At present the only open industry standard for multi-dimensional data is based on OLE DB. If the user has a problem that involves the integration of these two forms of data, OLE DB is a better solution. It will reduce the amount of code needed.
If the user wants to access data in an object server, he or she must migrate to OLE DB; there is no ODBC outlet.
There is no ODBC access to an IOM object server. The OLE DB provider that ships with this product is the only way a user has to get to SAS data sets in such a server.
If the user wants to access different data stores (local, SHARE, IOM, etc) from a single application, he or she should migrate to OLE DB.
If the user plans to access data in an object server and any other data store, he should consider migrating the entire application to OLE DB. Otherwise he will be maintaining separate code paths for each access method. This argument is further supported by the theoretical idea that OLE DB reduces the likelihood of differences between drivers, SQL dialects and DBMSs that has been problematic among ODBC configurations.
If the user wants to exploit as much of the SAS data model as possible from a thin-client application, he or she should migrate to OLE DB.
While ODBC is constrained by SQL, OLE DB is not. The OLE DB specification is much richer and more extensible than ODBC. To fully exploit this data model in a thin-client environment the user should migrate to OLE DB.
If the user needs the ability perform concurrent updates, he or she should migrate to OLE DB.
To update records using the ODBC driver the user must issue SQL UPDATE statements which are
basically batch oriented updates. There is no concept of record locking in the ODBC model. OLE DB accomodates several locking models that allow multiple concurrent updates. This feature of OLE DB enables the development of much more data management applications than with ODBC.

Now on top of the points above folks here we have a user that by his thread posted is
using VFP 9.0. We all know that using the non-updated VFP ODBC driver many of the
new field types that have been introduced are not supported using the VFP ODBC driver.
This all comes to a head as we now want to start to take advantage of such field types
as auto-incementing fields...etc... etc...

As to speed pros and cons, well lets say ODBC is a tad quicker since it is specifically
designed and optimized for passing SQL-syntax, but do I want to loose the capacity
to use new VFP 9.0 field types... "I think not". Personally, I have found that
I have not noticed any speed reductions using OLEdb.

As I mentioned in a previous thread, on a 1300-1400 corporate deployment of a new
application, do I really want the headache to ensure that a ODBC driver with a
DSN is properly installed and configured... "I think not." Can I setup the
DSN in VFP code, yes I can... do I need to added code, not me.

Do I want ODBC connections residing on 1000 workstations that any user with
a bit of expereince can use. Ok lets look at this scenario for a minute.
A user has a user account to my accounting system Invoice tables in SQL-Server
and I setup an ODBC connection locally on his/her machine that my application
uses to achieve the connection. The user loads up MS-Access 2003, picks the
ODBC driver and enters in the ID and password. The user then issues a query
against the SQL-Server backend to view all the Invoices. The user then creates
an update query to globally go in and write all the Invoice Totals to zero and
runs the query. Now would someone actually do this "No", but conceivable could
someone do this, "Yes".

Do I want the ability to access non-RDMS databases and obtain data from other Windows sources, "Yes I do." I do not what to maintain two different types of connection
methods in my applications, so I choose to go with one standard OLEdb.

So again, what is the correct choice moving down the garden path. Part depends
as stated ealier the real long term intent, and what you want and what you want
to allow.

I hope in this theard I have tried to provide all the things to look at in making
a decision. Again, my decision does not mean that I an correct and others are
wrong, tis just a point of view folks.

Pete from the Great White North. (Only in Canada, ay.) Over and Out ...