SQL Update not working with OpenRecordset 
Author Message
 SQL Update not working with OpenRecordset

Hi there

I am new to VB and have hit a problem while writing a small VB6 database
app. The db I am using is Access2000.
I am not using a data control. I have started using DAO and at this stage
don't want to switch over to ADO unless there is no choice.

The problem I am having is when I try to update a value in one of my tables.
I keep getting the following error (offending line of code is on last line
of this e-mail) :

Runtime Error : 3219
Invalid Operation

If I replace the Update statement with a select statement, there is no
problem. Obviously the issue surrounds updating the
database/recordset/table.

Can someone tell me what do I need to make updateable (read/write) ? The
database object or the recordset object and how do I achieve this ?

Thanks in advance
Regards

Revantha

The relevant code snippets are as follows :

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'The opendatabase stuff

sdbPath = "C:\Program Data\NewProjects\testDB.mdb"
Set daoDB36 = DBEngine(0).OpenDatabase(sdbPath)

' incidentally the following always returns true (even though I have set the
flag to false <default> in the line above)

If daoDB36.Updatable Then
    MsgBox ("database is readonly")
End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'A select statement and recordset stuff that works

mySQL1 = "Select Questions.ID, Questions.Description from Questions Where
Questions.QHID = " & CStr(QHID)
Set RsQ = daoDB36.OpenRecordset(mySQL1, dbOpenDynaset)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'the stuff that does not work

Public RsUsers as DAO.Recordset

mySQLUpdateScores = "UPDATE Users SET UserName = 'A.Person'"

'I tried using RsUsers.Update here, didn't do any good.

Set RsUsers = daoDB36.OpenRecordset(mySQLUpdateScores, dbOpenDynaset)



Mon, 27 Sep 2004 22:56:58 GMT  
 SQL Update not working with OpenRecordset
Hi,
I guess you can not use the UPDATE statement in a Recordset, because UPDATE
is a SQL-command which returns no data.
Use querydef and the Execute-Method instead or the docmd.sql-command.
Sub UpdateX()

    Dim dbs As Database

    Dim qdf As QueryDef

    Set dbs = OpenDatabase("your db")

    dbs.Execute "UPDATE Users SET UserName = 'A.Person'"

    dbs.Close

End Sub



Quote:
> Hi there

> I am new to VB and have hit a problem while writing a small VB6 database
> app. The db I am using is Access2000.
> I am not using a data control. I have started using DAO and at this stage
> don't want to switch over to ADO unless there is no choice.

> The problem I am having is when I try to update a value in one of my
tables.
> I keep getting the following error (offending line of code is on last line
> of this e-mail) :

> Runtime Error : 3219
> Invalid Operation

> If I replace the Update statement with a select statement, there is no
> problem. Obviously the issue surrounds updating the
> database/recordset/table.

> Can someone tell me what do I need to make updateable (read/write) ? The
> database object or the recordset object and how do I achieve this ?

> Thanks in advance
> Regards

> Revantha

> The relevant code snippets are as follows :

> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> 'The opendatabase stuff

> sdbPath = "C:\Program Data\NewProjects\testDB.mdb"
> Set daoDB36 = DBEngine(0).OpenDatabase(sdbPath)

> ' incidentally the following always returns true (even though I have set
the
> flag to false <default> in the line above)

> If daoDB36.Updatable Then
>     MsgBox ("database is readonly")
> End If

> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

> 'A select statement and recordset stuff that works

> mySQL1 = "Select Questions.ID, Questions.Description from Questions Where
> Questions.QHID = " & CStr(QHID)
> Set RsQ = daoDB36.OpenRecordset(mySQL1, dbOpenDynaset)

> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

> 'the stuff that does not work

> Public RsUsers as DAO.Recordset

> mySQLUpdateScores = "UPDATE Users SET UserName = 'A.Person'"

> 'I tried using RsUsers.Update here, didn't do any good.

> Set RsUsers = daoDB36.OpenRecordset(mySQLUpdateScores, dbOpenDynaset)



Mon, 27 Sep 2004 23:03:56 GMT  
 SQL Update not working with OpenRecordset
Sascha

You do not appear to use the querydef you defined in the code block you sent
me.

With using a code block and simply using the following line, I am able to
update the db as required.

daoDB36.Execute "UPDATE Users SET UserName = 'A.Person'"

Thanks a million

Really appreciated your help

Regards

Revantha


Quote:
> Hi,
> I guess you can not use the UPDATE statement in a Recordset, because
UPDATE
> is a SQL-command which returns no data.
> Use querydef and the Execute-Method instead or the docmd.sql-command.
> Sub UpdateX()

>     Dim dbs As Database

>     Dim qdf As QueryDef

>     Set dbs = OpenDatabase("your db")

>     dbs.Execute "UPDATE Users SET UserName = 'A.Person'"

>     dbs.Close

> End Sub



> > Hi there

> > I am new to VB and have hit a problem while writing a small VB6 database
> > app. The db I am using is Access2000.
> > I am not using a data control. I have started using DAO and at this
stage
> > don't want to switch over to ADO unless there is no choice.

> > The problem I am having is when I try to update a value in one of my
> tables.
> > I keep getting the following error (offending line of code is on last
line
> > of this e-mail) :

> > Runtime Error : 3219
> > Invalid Operation

> > If I replace the Update statement with a select statement, there is no
> > problem. Obviously the issue surrounds updating the
> > database/recordset/table.

> > Can someone tell me what do I need to make updateable (read/write) ? The
> > database object or the recordset object and how do I achieve this ?

> > Thanks in advance
> > Regards

> > Revantha

> > The relevant code snippets are as follows :

> > '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> > 'The opendatabase stuff

> > sdbPath = "C:\Program Data\NewProjects\testDB.mdb"
> > Set daoDB36 = DBEngine(0).OpenDatabase(sdbPath)

> > ' incidentally the following always returns true (even though I have set
> the
> > flag to false <default> in the line above)

> > If daoDB36.Updatable Then
> >     MsgBox ("database is readonly")
> > End If

> > '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

> > 'A select statement and recordset stuff that works

> > mySQL1 = "Select Questions.ID, Questions.Description from Questions
Where
> > Questions.QHID = " & CStr(QHID)
> > Set RsQ = daoDB36.OpenRecordset(mySQL1, dbOpenDynaset)

> > '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

> > 'the stuff that does not work

> > Public RsUsers as DAO.Recordset

> > mySQLUpdateScores = "UPDATE Users SET UserName = 'A.Person'"

> > 'I tried using RsUsers.Update here, didn't do any good.

> > Set RsUsers = daoDB36.OpenRecordset(mySQLUpdateScores, dbOpenDynaset)



Tue, 28 Sep 2004 00:55:27 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Update and Insert queries work in SQL but not in Access

2. SQL Update not Updating

3. SQL string not works in VB and works in Access 2000

4. Parameterized Update query not working correctly in Access 97

5. Update method in VB6 not working properly

6. CDO update method works W2K but not in NT4

7. Updating Date fields in the database not working (CAPA)

8. ADO & Jet: Updates do not work

9. Update button not Working

10. Does SQL command UPDATE work with vbasic?

11. SQL UPDATE doesn't work

12. UPDATE not working

 

 
Powered by phpBB® Forum Software