Updating ADODB recordset with excel

[GGROD]

I'm setting up a Recordset to an Oracle connection.

And in my VBA code I'm doing:

With rs
.AddNew
.Fields("OPENHOURS") = 4.7
.Update ' stores the new record
End With

if I set my OPENHOURS field to any value with a decimal then I get this error in MS Access: "WRITE CONFLICT"

if I set my OPENHOURS field to any value without a decimal then everything is fine.

The datatype of the oracle field is set to Double.

Any ideas?

Thanks,

Rod

 

[SkipVought]

Hi,

Your application is Excel.

You are updating an Oracle table.

Your error is MS Access: "WRITE CONFLICT"

MS Access???


Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[GGROD]

My VBA is in Excel. The VBA updates an Oracle table. I use Access to view the data in the table...(Linked table).

Hope this helps.

I think the fact that it is Excel is irrelevant, I should not have put that in the title of the post.

Sorry,

Rod

 

[GGROD]

For some reason when I use a decimal value there is record locking...when I use a whole number there is no record locking.

 

[xlbo]

Are you updating the linked table in Access or directly into the Oracle table ?

Take a look at the design of the linked table in Access - what data type does Access believe the field to be ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GGROD]

I am updating directly to the Oracle table. I only use the link table in Access to view the data and sometimes make a quick change to the data.

Rod

 

[xlbo]

If that is the case, why are you getting a "Write Conflict" in MSAccess.....

Please post the connection code you are using

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GGROD]

Here is my code for the connection:

Set cn = New ADODB.Connection

cn.Provider = "OraOLEDB.Oracle"

cn.Open "A015PROD", "USERNAME", "PASSWORD"

Set rs = New ADODB.Recordset

rs.Open "OWDB.TBLOPENWORK", cn, adOpenKeyset, adLockOptimistic, 512

Thanks,
Rod

 

[xlbo]

In which case how on earth are you getting a write conflict in Access ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GGROD]

That's what I'd like to know.

 

[xlbo]

WHEN do you get the error? when executing the code? or when viewing the results in Access ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GGROD]

The error only occurs when viewing/editing the results in Access.

 

[xlbo]

Right - so there is no issue with the code...

As I asked on teh 6th November

Take a look at the design of the linked table in Access - what data type does Access believe the field to be ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GGROD]

Access shows:

data type - Number
Size - Double
Decimal Places - Auto
Required - No
Indexed - Yes (Duplicates OK)

Rod

 

[xlbo]

Try setting "Decimal Places" to at least 1


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question