opening ole db databases in access

[Mike Wilson]

Is there a way to open an OLE DB database from within Access?
I would like to use the Access GUI with its table and query
explorer to examine a database only available through an OLEDB
provider interface.

I have seen several references on how to open an OLE DB
database through VBA/ADO code but I wouldn't know how to go
from there to actually have the database available in the
Access GUI...

Background: the database is of Visual Foxpro 8 "free table"
format and I only have the set of files comprising the db.
Apparently the Foxpro ODBC driver only supports up to the 6.x
version of Foxpro, and from there you have to use the OLE DB
provider interface. I have succeeded in opening tables into
Excel by using the Foxpro OLE DB provider, but have yet not
found a way to open them into Access.

Cheers / Mike

 

[Mike Wilson]

Ok Brendan, thanks.
It seems using Access will not help my very much with what I
want to do - in an easy way examine the structure of the
database I have at hand (and which is unknown to me).

It looks like all the options I have require me to already
have learned the things I wanted to know in the first place
(database schema etc) through ADO coding, so then there is
no reason to involve Access.

But it is a bit suprising, isn't it, that Access 2003
doesn't natively support OLE DB providers when this is the
"chosen technology of the future"?

Mike

 

[Brendan Reynolds]

Not really, no. VB doesn't. VB.NET doesn't. C# doesn't. They all access
OLEDB providers via ADO or ADO.NET. The only people I ever heard of working
directly with OLEDB is C++ programmers, and if you think learning to work
with OLEDB via ADO is a pain, I hear working with OLEDB directly is a real
nightmare.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Mike Wilson]

Well, I don't think the interesting comparison is which
particular API is used to access the database in this case,
as long as it gets the job done.
I don't care if Access would use ADO or OLE DB directly
when letting me work with my OLE DB provided database in
the same way it lets me work with my ODBC sources.
The problem is that it uses neither.

And I still think it is very surprising that Access will not
import tables from an OLE DB provider, while Excel and Word
can do it... ;-)

Mike

 

[Madeiras]

It can be done, but it's not pretty.

- Create a project that connects to MSDE2000 (has to be local).
- Menu File -> Get External Data -> Link Tables
- "Create Transact SQL" [Next ->]
- Select "+Connect To New Data Source.odc" [Open]
- Select "Other/Advanced" [Next ->]
- Select OLE DB Provider [Next ->]
- Select Database [Next ->]
- Link Tables [Next ->]

Should Work, I guess MS does not want Access to use other OLE DB Providers.

Felipe