DAO & ADO within the Same Application?

[Pierre Doré]

Can I connect to an Access 2007 .mdb using DAO then establish an Oracle
connection using ADO? I need to update an Oracle 10G database once
extracting the information from an Access database. This is due to the fact
that I'm migrating an Access 2002 application to Access 2007. But no ODBC
connection is permitted in this new version of Access. I've got the Oracle
connection working as well to the Access 2007 database. It seems to work,
but is slow. Here is the lines of code to establish both connections:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim sCN As String

Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset

cn.ConnectionString = "Data Source='ACIIS'; User
ID='aciis_admin';Password='xxxxxxxxx';"
Set gWorkspace = CreateWorkspace("NewAccessWorkspace", "aciis", "xxxxxxx",
dbUseJet)
Set db_ACIIS_ORACLE = gWorkspace.OpenDatabase("ACIIS", dbDriverComplete,
False, "ODBC;UID=" & "ACIIS_ADMIN" & ";PWD=" & "xxxxxxxxx" & ";DSN=ACIIS")

Also, how do I update the Oracle table using ADO coding? Previously it was
updated using DAO coding because of the ODBC connection as follows:

db_ACIIS_ORACLE.Execute ("commit")

 

[Douglas J. Steele]

It should be no problem. I've often used DAO to work with Jet, and ADO to
work with SQL Server, in the same application.

BeginTrans, CommitTrans and RollbackTrans are all methods of the ADO
Connection object.
http://msdn.microsoft.com/en-us/library/ms680895(VS.85).aspx