473,322 Members | 1,538 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Why Does .Addnew Not Work?

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.CurrentDb

Set qdf1 = dbs.CreateQueryDef("")

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=blah 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_test1 = Me.txt1

.Fields!timtest2 = 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
Nov 12 '05 #1
2 7039
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******@morgan.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.CurrentDb

Set qdf1 = dbs.CreateQueryDef("")

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=blah 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_test1 = Me.txt1

.Fields!timtest2 = 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
Nov 12 '05 #2
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******@Gunner.Sabot.Spam.On.Loaded.FIRE> wrote in message
news:3F***************@Gunner.Sabot.Spam.On.Loaded .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******@morgan.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.CurrentDb

Set qdf1 = dbs.CreateQueryDef("")

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=blah 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_test1 = Me.txt1

.Fields!timtest2 = 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

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Jan van Veldhuizen | last post by:
I have an application which is running fine with MS SqlServer, but it should be working with Oracle as weel. At a lot of places we rely upon the ADO Recordset to return incremented identity...
1
by: Jon Trelfa | last post by:
I have been fighting with this script for several days and I'm finally at the end of my rope. Here's the scenario: -I have to add an entry into 1 table, called "calendar" -I must retrieve the...
2
by: Bill Short | last post by:
The following code copies recordset rs into recordet rst and adds it to tblVMSParts. The records being copied are the data that shows on a subform. I copy all of the data from the main record,...
25
by: dixie | last post by:
I have some code that adds new records into a table for each ID in a list box when a button on a form is clicked. This works fine. My problem now is that I wish to be able to edit all the records...
2
by: Harold | last post by:
Sat I have a customers table with the fields CustomerID and Customer and I use the recordset.addnew method to add a new record to the table. What is the best way to get the CustomerID of the new...
2
by: pillmill | last post by:
I replaced AddNew statments with INSERT INTO, but am unable to write to the same tables. Foreign keys violations are the main errors. Why are these occuring ? Before: set rs3=...
1
by: Leonardo | last post by:
Hi. I'm trying to build my first application with database access using VB 2005. I'm a VB 6 programmer and learning everything again has been challenging. I managed to write a code using some tips...
0
by: Cong | last post by:
I have a main form (Main-member) with a cancel button. When user click on the button, it open up another form (Frame40) and the user can choose a cancel option and double click to close. But...
4
by: the other john | last post by:
ok, for a change I'm looking for why something "does" work. I wanted to insert a record into an Access DB and then retrieve the value that was just inserted. I came across this... ...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.