Using an MS Access Project with an OLE DB Data Source

[Guest]

I am using Access 2002 with SP2.

Is it possible to utilize a custom built OLE DB driver from within an MS
Access Project(.ADP) file?
I would like to be able to use the query and form development features that
are available from within an Access project, however, the data source is not
SQL Server in this case.
Since the "Providers" tab is absent in the Data Link properties dialog, it
appears that a project can only work with SQL Server.

If this is not possible, would it be possible to create link tables within a
standard .MDB file from an OLE DB data source instead of ODBC?

 

[Mary Chipman]

No and no. An ADP is limited to a connection to a single SQL Server
database. Linked tables in an mdb use ODBC. What exactly are you
trying to do? Somethings there's other ways to go about things. For
example, you can create views or stored procedures in a SQL Server
database that make use of linked servers, and you can write VBA code
in either an ADP or mdb to use an OLE DB provider.

--Mary

 

[Guest]

We have a Win32 application that stores its underlying data within an SQL
Server or Oracle database. This application uses a database schema that is
not easily understood and this makes direct connection to the database and
querying difficult for the average user wishing to develop reports.
We have developed an OLE DB driver that will basically give a report
developer a normalized view of this schema. Many users will use Crystal
Reports to do reporting development. We were hoping that MS Access might be
a viable development plaform as well. We are trying to open up our tool as
many of these report development environments as possible.
One solution we may consider is the creation of an ODBC driver in addition
to our OLE DB driver.

Thanks for your feedback.

 

[Mary Chipman]

In the scenario you have outlines you *definitely* want to stick with
the mdb format, which is a lot better at working with heterogeneous
data than SQL Server is. I don't have much experience with Oracle, but
a lot of people are using Access as a FE to Oracle. As far as SQL
Server goes, Access can link easily to tables and views, and work with
stored procedures through pass-through queries (good for reports). You
don't need to create your own drivers, the Microsoft ones do the job
quite well. One advantage is that if users are already familiar with
Access, they can create their own queries and reports. Another is that
you can link to both Oracle and SQL Server databases in the same mdb
if you need to perform heterogeneous joins. Performance won't be any
great shakes since all the data will be processed locally, but as long
as the volumes aren't huge it should be doable.

--Mary