Solved

Access 97 ADO vs DAO

Posted on 2003-10-31
3
1,478 Views
Last Modified: 2007-12-19
Hi

I have an Access 97 Project that has several tables but also needs to download some data from an Oracle database.  I use an ADO connection to connect to the Oracle database and download the data but I would also like to use a transaction when manipulating the access tables.

The begintrans, committrans etc. are members of the workspace object which is part of the DAO library.  Can you use both ADO and DAO within the same project?  What I did was use an ADO connection to the current database to use the transaction methods of the connection object but this seems like it should not be necessary.  Any ideas?

Set Conn1 = New ADODB.Connection    
Conn1.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
           "Dbq=" & CurrentDb.Name & _
           "Uid=admin;" & _
           "Pwd="

Thanks

rthomsen

0
Comment
Question by:rthomsen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 26

Accepted Solution

by:
Alan Warren earned 80 total points
ID: 9662516
Hi rthomsen,

The connection string you hve posted connects to the current database, have you established a connection to the oracle database yet?

OLE DB Provider for Oracle (from Microsoft)
oConn.Open "Provider=msdaora;" & _
           "Data Source=MyOracleDB;" & _
           "User Id=myUsername;" & _
           "Password=myPassword"

OLE DB Provider for Oracle (from Oracle)
For Standard Security

oConn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=MyOracleDB;" & _
           "User Id=myUsername;" & _
           "Password=myPassword"
 
For a Trusted Connection

oConn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=MyOracleDB;" & _
           "User Id=/;" & _
           "Password="
' Or
oConn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=MyOracleDB;" & _
           "OSAuthent=1"
Note: "Data Source=" must be set to the appropriate Net8 name which is known to the naming method in use. For example, for Local Naming, it is the alias in the tnsnames.ora file; for Oracle Names, it is the Net8 Service Name.

Source: http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForOracleFromMicrosoft

Alan
0
 
LVL 4

Assisted Solution

by:inox
inox earned 45 total points
ID: 9663552

yes, you can use ADO and DAO simultanously, but there are equal names, so you have to use full qualifiers i.e:

Dim Con As ADODB.Connection
Dim ADORs As ADODB.Recordset
Dim DAORs As DAO.Recordset

of course ADO supports transactions also, they are a method of Connection object:

  Con.BeginTrans



0
 
LVL 2

Author Comment

by:rthomsen
ID: 9715675
Thanks Guys.  I did get it all worked out but your answers helped me clear up some questions so I split the points between you.

rthomsen
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question