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 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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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,...
|
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...
|
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...
|
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=...
|
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...
|
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...
|
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...
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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...
| |