UPDATE DAO RECORDSET 1

[sal21]

attention is a test code!

my code:

Sub TEST()

Dim Rs As DAO.Recordset, db2 As DAO.Database, sConnect As String, SQL As String, queryDef As queryDef
'Set ws = CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC)

sConnect = "Driver={ODBC Driver 17 for SQL Server};Server=99.99.99.99;Database=keystore2;UID=sa;PWD=abcdef;OPTION=3;"
Set db2 = OpenDatabase("", False, False, sConnect)

' Rs.Close
'Set Rs = db2.OpenRecordset("SELECT * FROM CLIENTI", dbOpenDynaset)

'SQL = "UPDATE CLIENTI SET RAG_SOC='PIPPO' WHERE CODICE='100'"

Set Rs = db2.OpenRecordset( _
"UPDATE CLENTI SET RAG_SOC='PIPPO' WHERE CODICE='100'", dbOpenDynaset, _
dbSeeChanges) <<<<<<


'Set Rs = db2.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)

'Rs.MoveFirst
'Rs.MoveLast

'Rs.FindFirst "CODICE ='100'"

'Rs.Edit
'Rs("RAG_SOC") = "PIPPO"
'Rs.Update '->>>>>>>> ERRORE !

End Sub

have error in <<<<<<

I need to update the table CLIENTI > fieLd RAG_SOC='PIPPO' where CODICE='100'

 

[strongm]

[tt]"UPDATE CLENTI SET RAG_SOC='PIPPO' WHERE CODICE='100'", dbOpenDynaset, _
dbSeeChanges) <<<<<<[/tt]

 

[sal21]

TKS bro.

But i need to use:

Rs.Edit
Rs("RAG_SOC") = "PIPPO"
Rs.Update '->>>>>>>> ERROR HERE !

original code

Sub TEST1()

Dim Rs As DAO.Recordset, db2 As DAO.Database, sConnect As String
     'Set ws = CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC)
     
     sConnect = "Driver={ODBC Driver 17 for SQL Server};Server=2.59⁹99.19;Database=keystore2;UID=sa;PWD=Ab8769;OPTION=3;"
     Set db2 = OpenDatabase("", False, False, sConnect)
     '  Rs.Close
     Set Rs = db2.OpenRecordset("SELECT * FROM CLIENTI", dbOpenDynaset, dbSeeChanges)
     
     Rs.MoveFirst
     Rs.MoveLast
     Rs.FindFirst "CODICE ='100'"
     
     Rs.Edit
     Rs("RAG_SOC") = "PIPPO"
     Rs.Update    '->>>>>>>> ERROR !

End Sub

See image

https://files.engineering.com/getfi...4554-4a0d-bede-330d475a2e45&file=Immagine.jpg

 

[strongm]

OK, step one. Update your ODBC driver

 

[sal21]

For example of:
Update your ODBC driver

 

[strongm]

Seriously? Ok, let me google that for you ...

https://learn.microsoft.com/en-us/s...c-driver-for-sql-server?view=sql-server-ver16

 

[Andrzejek]

Ok, let me google that for you ...

Wouldn't be better to just do this...?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

Yes, I did consider that, but decided against, because that just takes you to a set of search results rather than a specific page, and on current evidence I get the impression sal21 would come back to us to ask "which one do I need?"

(also, I suspect it won't fix the issue, but at least means we'll be working with most up to date drivers)

 

[sal21]

sConnect = "PROVIDER = MSDASQL;driver={SQL Server};Server=1.2.3.45;Database=keystore2;UID=sa;PWD=aaaasssss;OPTION=3;"

new driver

 

[mattKnight]

Be warned - what seems like a public IP address and 'god' level database credentials displayed in an open forum is a very risky thing to do

Take Care

Matt
I have always wished that my computer would be as easy to use as my telephone.
My wish has come true. I no longer know how to use my telephone.

 

[sal21]

A friend suggest me to set the connection with a dao.workspace.
But i dont know how to!

 

[strongm]

And now let's jkust confirmn - using this new driver (not an uopdate, a completely different one), do you get the same problem?

And, in the meantime, try the following code:

[COLOR=blue]sConnect = "Driver={ODBC Driver 17 for SQL Server};Server=yourserver;Database=keystore1;UID=userid;PWD=password;" 
 Set db2 = OpenDatabase("", False, False, sConnect)
     
 db2.Execute "UPDATE CLIENTI SET RAG_SOC='PIPPOL' WHERE CODICE='100'"[/color]