opening ole db databases in access

  • Thread starter Thread starter Mike Wilson
  • Start date Start date
M

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
 
you might try to link to the tables. with linked tables it
would amount to the same as opening the database in access.
you would be able to use access features on it's data.
 
I have looked at that alternative and it only works for
ODBC connections, not OLE DB, and thus doesn't solve my
problem...

Mike
 
To the best of my knowledge, you can only create linked tables using either
Jet or ODBC. If your data source is not supported by Jet and there is no
ODBC driver, then the only way to access the data will be via ADO, as in the
examples you have seen. To make that data available in Access, you could try
binding forms and reports to ADO recordsets, or using unbound forms, or
importing the data into local Jet tables and binding the forms and reports
to those.

--
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.
 
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
 
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.
 
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
 
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
 
Back
Top