Solved

Access 97 ADO vs DAO

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

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
Comment Utility
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now