It is possible to connect Access to Oracle by using DAO?

[dariyoosh]

Hi,


Currently, I'm learning how to open databases by using DAO objects and retrieve Recordsets.

I would like to know whether it is possible to connect a DAO object to Oracle. I have been googling for finding this, but so far, what I have found is all about ADO and not DAO. In the case, where it is possible to connect Access to Oracle by using DAO, could anyone kindly give me an indication (code, link to a tutorial, etc.)


Thanks in advance,

Kind Regards
Dariyoosh

 

[Banana]

Sure you can. The thing is that you need to use ODBC drivers and thus create a linked table. (Actually you don't need to link tables but I tend to see little rationale for requiring more coding by doing it all in VBA.) So you'd download Oracle's drivers (note there are more than one vendors giving out Oracle ODBC drivers - Microsoft and Oracle both have their ODBC drivers - I don't know which is better so you'll need to experiment.).

If you need more instructions, let us know.

Maybe this will also help though it works at conceptual level rather than "how-to": Beginner's Guide to ODBC{/url]

 

[dariyoosh]

Sure you can. The thing is that you need to use ODBC drivers and thus create a linked table. (Actually you don't need to link tables but I tend to see little rationale for requiring more coding by doing it all in VBA.) So you'd download Oracle's drivers (note there are more than one vendors giving out Oracle ODBC drivers - Microsoft and Oracle both have their ODBC drivers - I don't know which is better so you'll need to experiment.).

If you need more instructions, let us know.

Maybe this will also help though it works at conceptual level rather than "how-to": Beginner's Guide to ODBC{/url]

Hello there,


Thanks for your help, I will read your tutorial which seems to be very informative.

I have already Microsoft ODBC driver for oracle installed and it works. Actually I had found the following page while I was googling:

http://www.connectionstrings.com/oracle#p17

It seems that it contains the correct syntax of connection strings for Oracle, I also found examples of creating these connections with ADO but I have not found any clear example for DAO.

Well, it is just for training purpose and just know how to do that, because according to what I have read in several forums, DAO is rather for Jet side (local tables) and most of the remote connections to data ressources goes with ADO.

Anyway, I continue searching. Thanks again for your help.


Kind Regards,
Dariyoosh

 

[Banana]

FWIW - in my work, 95% of time I use DAO, even against ODBC sources. in 5% of time I do use ADO (yes you can use both in same application), it's usually to address something that DAO can't do. One example is when I want to bind my form to a stored procedure. In DAO, that would be nonupdatable but in ADO, if I follow certain rules, I can do just that and still have updatable recordset so people can do a data entry against a stored procedure (or rather, the table pulled by the stored procedure, actually).

With that said, it sounds like you're actually after using DAO in VBA code, without using a linked table? If so, then you'd need to look at OpenDatabase method which can open a ODBC source. See if this get you closer, but again, I usually don't see benefit in doing this except for the special case where I want to check credentials or dynamically rebuild a list of linked tables but thereafter, I usually just refer to linked tables.

HTH.