ADD EXCEL DATA RECORD TO AN ACCESS TABLE

[MALVINA]

I am trying to add records to my access db if vba didn't
find the recordset match. Following is my code (some of
my code):
***********************************************************
*
With rs
If .RecordCount > 0 Then
.Edit
!BC_calls = mycalls
![tblMONTHLY_BASELINE.talk] = mytalk
![tblMONTHLY_BASELINE.work] = mywork
.Update
Else
.AddNew
!BC_calls = mycalls
![tblMONTHLY_BASELINE.talk] = mytalk
![tblMONTHLY_BASELINE.work] = mywork
.Update
End If
.Close
End With
Next i
***********************************************************
*
When I execute my code, I get an error on > .AddNew line.
The message says : "Feild can't be updated"

[Nate Oliver]

Hello MALVINA,

I suspect the issue is related to how you're opening your
recordset, which you haven't mentioned, sounds like it's
read-only. I can get the following to fire via DAO from
Excel:

Sub AddRecords()
Dim AccObj As Object, db As Object, rs As Object
Dim mySql As String
Dim myCalls As String, myWork As String, myTalk As String
Set AccObj = GetObject(, "Access.Application")
Set db = AccObj.DBEngine(0)(0)

myCalls = Sheets(1).[a1]: myWork = Sheets(1).[b1]
myTalk = Sheets(1).[c1]

mySql = "SELECT [BC_Calls], [talk], [work] " & _
"FROM tblMONTHLY_BASELINE WHERE " & _
"[BC_Calls]='" & myCalls & "';"

Set rs = db.OpenRecordset(mySql)

With rs
If .RecordCount > 0 Then
.Edit

.fields(0) = myCalls: .fields(1) = myTalk
.fields(2) = myWork
.Update
Else
.AddNew
.fields(0) = myCalls: .fields(1) = myTalk
.fields(2) = myWork

.Update
End If
.Close
End With

Set rs = Nothing: Set db = Nothing: Set AccObj = Nothing
End Sub

I think the default edit mode varies with ado and dao.
Have a look at the following:

http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dao360/html/damthopenrecordset.asp

Note with DAO: dbPessimistic is the lockedits default

http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/ado270/htm/mdamth03_2.asp

Note with ADO: adLockReadOnly is the locktype default

So it's going to vary by method, and is more of an Access
programming technicality than Excel. Getting the lock
parameter correct could be key.

Also, here's some good reading:

http://www.erlandsendata.no/english/vba/adodao/

While I'm posting. Regards,
Nate

>.
>

[malvina shagabayeva]

This is great!!!

Nate,
thank you for taking your time!!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!