Access 97 ADO vs DAO

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

LVL 2
rthomsenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alan WarrenApplications DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
inoxCommented:

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
rthomsenAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.