Solved

Access 97 ADO vs DAO

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
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…

685 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