Solved

Access 97 ADO vs DAO

Posted on 2003-10-31
3
1,471 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
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

809 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