Updated Record using ADODB (1 Viewer)

[Tyndie]

Hi,

I am having problems updating a record in a table using adodb:

My code is:

Dim sqlStr As String
sqlStr = "UPDATE (Students INNER JOIN Student_Contacts ON Students.stuID = Student_Contacts.stuID) INNER JOIN Student_Enrollments ON Students.stuID = Student_Enrollments.stuID SET Students.stuFirstName = '" & txtEditFirstName & "', Students.stuMiddleNames = '" & txtEditMiddleNames & "', Students.stuSurname = '" & txtEditSurname & "', Students.stuGender = '" & cbEditGender & "', Students.stuDOB = '" & txtEditDOB & "', Students.stuStAddress = '" & txtEditAddressLine1 & "', Students.stuDistrict = '" & txtEditDistrict & "', Students.stuHmeCity = '" & txtEditTown & "', Students.stuHmeCounty = '" & txtEditCounty & "', Students.stuHmePostCode = '" & txtEditPostCode & "', Students.stuHmeCountry = '" & txtEditCountry & "', Students.stuNotes = '" & txtEditAdditionalNotes & "', Student_Contacts.stuHmeContactNumber = '" & txtEditTelNumber & "',"
sqlStr = sqlStr & "Student_Contacts.stuMobileContactNumber = '" & txtEditCellNumber & "', Student_Contacts.stuEmailAddress = '" & txtEditEmailAddress & "', Student_Enrollments.enrCourseStatus = '" & frmStatus & "' WHERE (([WHERE Students].[stuID]='" & txtEditStudentID & "'));"
Dim updateStudent As ADODB.Recordset
Set updateStudent = New ADODB.Recordset

updateStudent.Open sqlStr, CurrentProject.Connection, adOpenStatic, adLockReadOnly

updateStudent.Close

Does anyone know what I am doing wrong please?

 

[Surjer]

what error are you getting. I havent done this in a while but it appears you are running an update query where you cant.

Have you tried running the SQL?

Is this a Form In ACcess?

I would recomend doing a DoCmd.RunSQL sqlStr

 

[boblarson]

I believe you must preface your running of the SQL update by using

updateStudent.EditMode

   .... do your updates

updateStudent.Update

 

[KernelK]

Error would help but....

The error you are getting would help tremendously, but...it seems you are attempting to run an UPDATE on a recordset that you specified the locktype as read-only and a cursortype as static. I would set the third and fourth parameters of your open method as:

updateStudent.Open sqlStr, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

See if that helps any.

 

[boblarson]

sheesh! I can't believe I missed that one in my answer.