DAO to ADO

[BPlenge]

In Access 2007, when I attempt to read from an Oracle database, I get the following message: Error 3847 - odbcDirect is no longer supported. Change your code to use ADO instead of DBO.

I am attempting to change the code from:
Dim sql as string
Dim rsProv As Recordset
Dim OraD As Database
Dim wksOracle As Workspace
Set wksOracle = CreateWorkspace("ODBCWorkSpace", "admin", "", dbUseODBC)
Set OraD = wksOracle.OpenDatabase("Oracledatabase", , True, ConnString)
sql = "(select certain fields from filename)"
Set rsProv = OraD.OpenRecordset(sql)

To:
Dim sql as string
Dim rsProv As ADODB.Recordset
Dim OraD As ADODB.Connection
Dim wksOracle As Workspace
sql = "(select certain fields from filename)"
Set rsProv = New ADODB.Recordset
rsProv.Open sql, OraD, adOpenStatic, adlockbatchoptimistic

When I try to run it, I get the following message: Error 3709 - The connection cannot be used to perform this operation. It is either closed or invalid in this context.

If anyone has any tips on how to get it to run, I will appreciate it very much.

 

[KeithG]

you need to set the connectionstring of you connection.

 

[Uncle Gizmo]

I thought MS Access 2007 supported DAO?

 

[BPlenge]

I've seem references to DAO, but it will no longer allow the use of ODBCdirect. "dbuseODBC" is the statement refering to ODBCdirect. I've seen suggestions to make a reference to MSDAO objects under tools References, but I'm unable to find anything pertaining to MADAO.

Does anyone have anymore tips on the issue?

 

[BPlenge]

I have found that 2007 does not have ADO as a default reference which causes problems elsewhere. Does 2007 have a form of DAO that it supports. Is there a better method of opening an Oracle file?

Thanks so much.

 

[boblarson]

I have found that 2007 does not have ADO as a default reference which causes problems elsewhere. Does 2007 have a form of DAO that it supports. Is there a better method of opening an Oracle file?

Thanks so much.

Access 2007 references DAO by default. However, I'm wondering if you just need an updated ORACLE ODBC driver.

 

[BPlenge]

From what I've learned, Access 2007 does not allow you to use ODBCDirect anymore. Our system has Oracle 10-g installed, which is what we've had for a while. I have not had any errors telling me to install a new driver.