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

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


"Deanna" <(E-Mail Removed)> wrote in message
news:06FD5D02-2181-4FE1-B178-(E-Mail Removed)...
> 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
>
>