Access to Oracle

[garyd1234]

I'm putting data from a form bound to an Access table. I have a routine like this to also write it to an Oracle table for use by others using ODBC. the connection is made but when I try to add a new record I get and error : Can't update Database is read only. It isn't. I can use SQL Plus to put whatever I need into the Oracle table. It seems to be how Access is talking to Oracle. Does anyone know why? Or point me to someone who does? Thanks. Here is some code. I tried a with using .addnew and .update. Also have tried the insert directly into the oracle table
Dim wrkJet As Workspace
'Dim wrkODBC As Workspace
Dim db As DAO.Database
Set db = CurrentDb()
Dim con As Object
Dim od As Workspace

Dim rs3 As Object

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set wrkODBC = CreateWorkspace("", "admin", "", dbUseODBC)
Set con = wrkODBC.OpenConnection("", , False, _
"ODBC;DATABASE=;UID=LAW;PWD=GARY;DSN=LawDB")
' bring current data from the Oracle tables into the Access tables for searching or new record creation
'Set od = OpenDatabase(con)
Set rs3 = con.OpenRecordset("SELECT * FROM COMPLAINTS", dbOpenDynaset)
'Dim CaseYr As Integer
'Dim CaseNo As Integer

Debug.Print rs3.RecordCount



'While Not rs.EOF
With rs3
.AddNew
![CaseYr] = 2003
![CaseNo] = 84
'![HEARINGDATE] = "#" & [Hearing Date] & "#"
![HEARINGDATE] = Day([Hearing Date]) & "-" & Month([Hearing Date]) & "-" & Year([Hearing Date])

![Charge] = "'" & Charge & "'"
![FORMALCHARGE] = "'" & [Formal Charge] & "'"
![FORMALDEGREE] = "' '"
![FORMALDESC] = "' '"
'![TODAYSDATE] = "#" & [Todays Date] & "#"
![TODAYSDATE] = Day([Todays Date]) & "-" & Month([Todays Date]) & "-" & Year([Todays Date])
![INCIDENTADDRESS] = "'" & [Incident Address] & "'"
![Description] = "'" & Description & "'"
'![INCIDENTDATE] = "#" & [Incident Date] & "#"
![INCIDENTDATE] = Day([Incident Date]) & "-" & Month([Incident Date]) & "-" & Year([Incident Date])

![POLICEREPORT] = "'" & [Police Report] & "'"
![HOSPITALREPORT] = "'" & [Hospital Report] & "'"
![REPORTLOCATION] = "'" & [Report Location] & "'"
![INTAKENOTES] = "'" & [Intake Notes] & "'"
![INTAKEOFFICER] = "'" & [Intake Officer] & "'"
![HEARINGOFFICER] = "'" & [Hearing Officer] & "'"
![COURTCASEYR] = "' '"
![COURTCASETY] = "' '"
![COURTCASENO] = "' '"
![UserName] = CurrentUser

'.Execute "insert into Complaints values (" & cyr & "," & cno & "," & "04-DEC-03" & "," & chrg & "," & fchrg & "," & fdeg & "," & fdesc & "," & "04-DEC-03" & "," & incadd & "," & desc & "," & "04-DEC-03" & "," & pr & "," _
& hr & "," & rl & "," & itknotes & "," & itkoff & "," & ho & "," & ccyr & "," & ccty & "," & ccno & "," & un & "," & "' '" & "," & "' '" & "," & "' '" & "," & "' '" & "," & "' '" & ")"



.Update

' .MoveNext
End With

 

[ddeldeb]

I'm not sure on this but, when we write to Oracle tables we allways set the cursor types. I cant remember the exact syntax. but will check when I get to work and post what we use. We are not writeing data from Access, but we are using VB or Excel + VBA, so it should be the same for Access.