Convert ODBCDirect Codes to ADO in Access 2007

[Deanna]

I am upgrading an Access 97 database to Access 2007. Knowing that ODBCDirect
is no longer supported in Access 2007, I modified the codes.

This database's back end is Oracle. Data source is set up using OraHome81
driver.

I am struggling how to connect to the database as I have different errors. I
am not sure if I have to have the Microsoft OLEDB provider for Oracle.
Currently I am just using MSDAORA.

Could you please help me to see if my modifed codes are correct?

Here is the old code:

'Dim wrkODBC As Workspace
'Dim cnn As Connection
'Dim qdfTemp As QueryDef

'Modified code
Dim cnn As ADODB.Connection
Dim strConnect As String
Dim cmd As Command

On Error GoTo cmdSave_Click_Err_Handler

DoCmd.Hourglass True

' Testing to see if any value is ready to Save

If txtBox1.Visible = False Then
DoCmd.Hourglass False
Exit Sub
End If

If Forms!frmAddDepartments!cmdAddM.Enabled = False Then 'Add
If txtBox1 <> "" Then

p_maindep = txtMainDepartmentCode
p_subdesc = txtBox1


'Old Codes

'Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "",
dbUseODBC)
'Set cnn = wrkODBC.OpenConnection("Connect1", , ,
"ODBC;DSN=MUNDIR;DBQ=ESD2;UID=MUNDIR;PWD=INWDIR")
'Set qdfTemp = cnn.CreateQueryDef("", "{CALL
INSERT_SUB_DEPARTMENT_INFO('" & p_maindep & "','" & p_subdesc & "')}")

'qdfTemp.Execute dbExecDirect

'cnn.Close
'wrkODBC.Close

'Modified Code

Set cnn = New ADODB.Connection

strConnect = "Provider=MSDAORA;Data Source=CWD To TEST
DB;Uid=MUNDIR;Pwd=Telly;"

cnn.ConnectionString = strConnect

cnn.Open

Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "{ CALL INSERT_SUB_DEPARTMENT_INFO('" & p_maindep & "','"
& p_subdesc & "') }"
cmd.Execute
cnn.Close

Thank you very much!

Deanna

 

[Alex Dybenko]

Hi,
here you can find how to write a connection string:
http://connectionstrings.com/oracle

Furthermore - you can use DAO and have only minimal changes, here a sample
code how to run a query:

Set qryd = dbs.CreateQueryDef("")
qryd.Connect = "ODBC;DSN=MUNDIR;DBQ=ESD2;UID=MUNDIR;PWD=INWDIR"
qryd.SQL = "{CALL INSERT_SUB_DEPARTMENT_INFO('" & p_maindep & "','" &
p_subdesc & "')}"
qryd.ReturnsRecords = false
qryd.Execute


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com