Why Does .Addnew Not Work?

[Tim Marshall]

ODBC situation, Access 97, here is the code and I've marked where
Access coughs up. The Access error message is 3027, can't update,
database is read only. The Oracle table in question has a primary key
defined as TEST_PK which is, of course, also an index:

Private Sub btnSave_Click()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf1 As DAO.QueryDef

Set dbs = Access.CurrentD b

Set qdf1 = dbs.CreateQuery Def("")

With qdf1

'The following connect string is correct. It works for all select
'statements and also works with insert statements when I
'perform .execute dbfailonerror

.Connect = "ODBC;DSN=b lah blah"

.SQL = "Select test_pk, test_tim1, testtim2 from tim_test"

.ReturnsRecords = True

Set rst = .OpenRecordset

End With

With rst

.AddNew 'THIS IS WHERE THE ERROR IS THROWN

.Fields!tim_tes t1 = Me.txt1

.Fields!timtest 2 = Me.txtNumber

.Update

MsgBox .Fields!TEST_PK

.Close

End With

Set rst = Nothing

Set qdf1 = Nothing

Set dbs = Nothing

End Sub
If anyone can help, I'd really appreciate it. I'm tearing my hair out.

--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Want some?" - Ditto

[Tim Marshall]

Thanks TC, I appreciate the suggestion. I always though you could not
"embed" with/end withs? However, I tried it and didn't use the with
rst, ie, used rst.addnew. But I get the same response. Actually, the
format I used originally is what I always use when I'm doing .execute
methods and if I change the sql to an append/insert query, the insert
works, so it's got nothing to do with Oracle permissions either.

Thanks...

TC wrote:

This is a real stab in the dark, but, are you sure it is cosher to close the
'with qdf1' before you have finished with rst? Try moving the first 'end
with' down to the end, next to the last one. I doubt this is it, but you may
as well give it a try.

TC

"Tim Marshall" <tm******@morga n.ucs.mun.ca> wrote in message
news:3F******** *******@morgan. ucs.mun.ca...

ODBC situation, Access 97, here is the code and I've marked where
Access coughs up. The Access error message is 3027, can't update,
database is read only. The Oracle table in question has a primary key
defined as TEST_PK which is, of course, also an index:

Private Sub btnSave_Click()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf1 As DAO.QueryDef

Set dbs = Access.CurrentD b

Set qdf1 = dbs.CreateQuery Def("")

With qdf1

'The following connect string is correct. It works for all select
'statements and also works with insert statements when I
'perform .execute dbfailonerror

.Connect = "ODBC;DSN=b lah blah"

.SQL = "Select test_pk, test_tim1, testtim2 from tim_test"

.ReturnsRecords = True

Set rst = .OpenRecordset

End With

With rst

.AddNew 'THIS IS WHERE THE ERROR IS THROWN

.Fields!tim_tes t1 = Me.txt1

.Fields!timtest 2 = Me.txtNumber

.Update

MsgBox .Fields!TEST_PK

.Close

End With

Set rst = Nothing

Set qdf1 = Nothing

Set dbs = Nothing

End Sub
If anyone can help, I'd really appreciate it. I'm tearing my hair out.

--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Want some?" - Ditto

--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Want some?" - Ditto

[TC]

Yes, it's fine to embed with/end withs:

with this
.method
.method
with that
x = .property
etc.
end with
.method
end with

Using rst.addnew is not quite what I meant. You had:

With qdf1
.props = values
Set rst = .OpenRecordset
End With
With rst
.AddNew 'THIS IS WHERE THE ERROR IS THROWN
End With
I wondered whether the validity of the recordset was affected by having
closed the first with/end with. So I meant for you to try this:
With qdf1
.props = values
Set rst = .OpenRecordset
With rst
.AddNew 'THIS IS WHERE THE ERROR IS THROWN
End With
End With
I doubt that will fix it. But I don't have Access here to check, so it is
maybe worth a try.

Cheers,
TC
"Tim Marshall" <tm******@Gunne r.Sabot.Spam.On .Loaded.FIRE> wrote in message
news:3F******** *******@Gunner. Sabot.Spam.On.L oaded.FIRE...

Thanks TC, I appreciate the suggestion. I always though you could not
"embed" with/end withs? However, I tried it and didn't use the with
rst, ie, used rst.addnew. But I get the same response. Actually, the
format I used originally is what I always use when I'm doing .execute
methods and if I change the sql to an append/insert query, the insert
works, so it's got nothing to do with Oracle permissions either.

Thanks...

TC wrote:

This is a real stab in the dark, but, are you sure it is cosher to close the 'with qdf1' before you have finished with rst? Try moving the first 'end with' down to the end, next to the last one. I doubt this is it, but you may as well give it a try.

TC

"Tim Marshall" <tm******@morga n.ucs.mun.ca> wrote in message
news:3F******** *******@morgan. ucs.mun.ca...

ODBC situation, Access 97, here is the code and I've marked where
Access coughs up. The Access error message is 3027, can't update,
database is read only. The Oracle table in question has a primary key
defined as TEST_PK which is, of course, also an index:

Private Sub btnSave_Click()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf1 As DAO.QueryDef

Set dbs = Access.CurrentD b

Set qdf1 = dbs.CreateQuery Def("")

With qdf1

'The following connect string is correct. It works for all select
'statements and also works with insert statements when I
'perform .execute dbfailonerror

.Connect = "ODBC;DSN=b lah blah"

.SQL = "Select test_pk, test_tim1, testtim2 from tim_test"

.ReturnsRecords = True

Set rst = .OpenRecordset

End With

With rst

.AddNew 'THIS IS WHERE THE ERROR IS THROWN

.Fields!tim_tes t1 = Me.txt1

.Fields!timtest 2 = Me.txtNumber

.Update

MsgBox .Fields!TEST_PK

.Close

End With

Set rst = Nothing

Set qdf1 = Nothing

Set dbs = Nothing

End Sub
If anyone can help, I'd really appreciate it. I'm tearing my hair

out.
--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Want some?" - Ditto

--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Want some?" - Ditto