What's the *correct* Data Source Type for Access?

[SJMac]

Hello,

In my expirments with Word Mail Merge with data from Access, I've found at
least 2 ways to link my Word documents with data from Access.

Starting the Data Connection Wizard, I can either choose
ODBC DSN
->MS Access Database
or
Other/Advanced
->Data Link Properties
-->Microsoft Jet4.0 OLE DB Provider

Both of these options seem to work fine. Can anyone tell me why I might
prefer to use one over the other in any circumstances?

Thanks,
Steven

 

[Peter Jamieson]

There isn't a whole lot to choose between ODBC and OLEDB when connecting to
Access. ODBC is based on an older standard that was not Microsoft-only and
there are a lot more ODBC drivers for third party products out there than
there are OLEDB providers. OLEDB is a more recent Microsoft-defined
standard. As far as I can remember, Microsoft's ODBC "Desktop database
drivers" are no longer "supported". Microsoft has, of course, moved on as
well and OLEDB is theoretically being replaced by ADO.NET, which is fine for
..NET programmers but has had no impact on the Office suite yet.

OLEDB can only be used from Word 2002 and later. ODBC works with earlier
versions - at least back to 97.

Off the top of my head, ODBC probably does not work properly with non-ANSI
Unicode data whereas the OLEDB provider probably does. The Jet ODBC driver
supports things called "ODBC escapes" which you probably don't need, wehreas
I think the OLEDB provider does not. ODBC may let you use some types of
query (partiicularly UNION queries) that OLEDB won't let you use - not for
any good reason as far as I know.

When issuing OpenDataSource calls, you may find that OLEDB restricts you to
a 255-character SQL string because of an error in Word, whereas ODBC will
probably allow the full 511 or whatever it is.

There's a third method, DDE, which needs to start Access to get its data but
allows you to use any table or query - including parameter queries
(ODBC/OLEDB can't use them), queries that reference user-defined functions,
queries that use certain functions such as those financial series functions,
and queries that use the old-style Jet SQL wildcards (* and ?). DDE is also
regarded as "deprecated" and insecure by Microsoft.

Peter Jamieson

 

[SJMac]

Hmm. I guess a long-lived app/suite is going to pick up some cruft along the
way. It's odd that the Jet 4.0 option is less prominant in the wizard than
the 'deprecated' ODBC driver, but oddly it is the one that I found first --
I'll stick with it!

Cheers,
Steven

 

[Peter Jamieson]

Both the ODBC driver and OLEDB provider work via jet 4.0 as far as I am
aware.

I don't think Access has ever really provided the ability to link to other
databases using OLEDB (you may know better!) - e.g. when you create a linked
table, you can link to another .mdb or supported format (such as .dbf) using
Jet and its "IISAMs", or you can link to external databases using ODBC and
at least one other "special" (i.e. you can link to a Sharepoint list).

So I guess there is a preference for ODBC within the Access camp.

Peter Jamieson