Good Morning,
Hopefully someone can help me with what I am assuming is a simple query that I simply can't get to work.
I am new to using Visual Basic and I am using Visual Studio 2005, Previously only been working in VBA.
I need to update a field in my database with the changes that are made in my application, I use the following code to call the Record Set.
- Dim Conn97 As ADODB.Connection
-
Dim Path97PRS As String
-
Dim rsDetails As New ADODB.Recordset
-
Dim strSql As String
-
-
Path97PRS = "R:\PRS 97 Version.mdb"
-
Conn97 = New ADODB.Connection
-
Conn97.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path97PRS & ";"
-
Conn97.Open()
-
-
strSql = "SELECT * FROM tbl_workdetails_agb WHERE tbl_WorkDetails_agb.Job_Booking_Referenc = " & Me.Job_Ref.Text
-
-
rsDetails.Open(strSql, Conn97, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
-
I then update the record set with the new entries
-
-
rsDetails.Fields("Address").Value = Me.txtAddress.Text
-
rsDetails.Fields("Mpan").Value = Me.txtMpan.Text
-
rsDetails.Fields("Account_Number").Value = Me.txtAccount.Text
-
But now I need to update the Record Set back into the Database, I tried using the following code
-
strSql = "UPDATE tbl_Workdetails_agb SET Address = Address, Mpan = Mpan,"
-
strSql = strSql & " Account_Number = Account_Number WHERE tbl_WorkDetails_agb.Job_Booking_Referenc = " & nbrSelect
-
rsDetails = Conn97.Execute(strSql)
-
But alas it simply does nothing, probably becasue I am wiping the record set with my next SQL statement or something. I have tried using the .update command but that simply seems to update the record set again.
Any help would be appreciated.
Thanks
Adam
