Solved

update a field in Access through VB

Posted on 2002-07-19
9
151 Views
Last Modified: 2010-05-02
I am just testing a 2 field form to see how to update a field in Access from VB.  I know this is basic but I am new at this.  It is going to the right row and getting the right jobnumber from the access database.  I did that so I could see if it was connecting to the database.  But when I change the Text box to reflect the new number I want in its place in the database it doesn't complete the action.  The end result is I want to capture the fields that I have on my form and get them into an access database.

Here is my code.  

Private cn As ADODB.Connection
Private mrscust As ADODB.Recordset


Private Sub Form_Load()
Set cn = New ADODB.Connection

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\testcount\countsplt42.mdb;Persist Security Info=False"
cn.Open

Set mrscust = New ADODB.Recordset
mrscust.LockType = adLockBatchOptimistic
mrscust.CursorLocation = adUseClient
mrscust.CursorType = adOpenKeyset

End Sub
Private Sub Command1_Click()
Dim strSQL As String

    strSQL = "Select * from 22shop_5min where [22shop_5min].id = 2"

mrscust.Source = strSQL
Set mrscust.ActiveConnection = cn
mrscust.Open

Label1.Caption = mrscust.Fields("jobnumber")
mrscust.Fields("jobnumber") = Text1.Text
mrscust.update

End Sub

Thanks for your help in advance!
0
Comment
Question by:sjmmajor
  • 5
  • 3
9 Comments
 
LVL 4

Expert Comment

by:gencross
ID: 7165271
It looks like you are updating the database immediately in the form load event.

Normally I will set a DataChanged flag in the Change event then update the data when the user unloads the form.  I also always use SQL statements to update the database (it is faster), but this way will work as well.

You can also use the LostFocus event in the textbox to update the database, but it is not always guaranteed that it will fire depending on the user action.

Private Sub Text1_Change()
    bDataChanged = True
End Sub

Private Sub Form1_Unload()
    If bDataChanged then
        mrscust.Fields("jobnumber") = Text1.Text
        mrscust.update
    End If
End Sub
0
 

Author Comment

by:sjmmajor
ID: 7165336
I tried this and it still didn't update the record in the database.  I can try the updating using SQL statements if that is the way to go.  I am looking for guidance.
0
 
LVL 4

Accepted Solution

by:
gencross earned 60 total points
ID: 7165368
To update using a SQL statement use the same method as above only once you fill your fields you can close the recordset.  

NOTE: You will also want to open the recordset Forwardonly, readonly.  This will be much faster.

Here is what your code would look like to open the recordset and populate...

Private cn As ADODB.Connection
Private mrscust As ADODB.Recordset


Private Sub Form_Load()
Set cn = New ADODB.Connection

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\testcount\countsplt42.mdb;Persist Security Info=False"
cn.Open

Set mrscust = New ADODB.Recordset
mrscust.LockType = adLockBatchOptimistic
mrscust.CursorLocation = adUseClient
mrscust.CursorType = adOpenForwardOnly

End Sub
Private Sub Command1_Click()
Dim strSQL As String

   strSQL = "Select * from 22shop_5min where [22shop_5min].id = 2"

mrscust.Source = strSQL
Set mrscust.ActiveConnection = cn
mrscust.Open

Label1.Caption = mrscust.Fields("jobnumber")

End Sub

For the update...

Private Sub Text1_Change()
   bDataChanged = True
End Sub

Private Sub Form1_Unload()

   Dim sSQL as String

   If bDataChanged then
       sSQL = "UPDATE 22shop_5min SET jobnumber = " & Text1.Text & " where [22shop_5min].id = 2"
       cn.Execute sSQL
   End If

End Sub
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:sjmmajor
ID: 7165397
Still won't update the database.  Got any other clues?
0
 
LVL 4

Expert Comment

by:gencross
ID: 7165403
Do you get an error message?
0
 

Author Comment

by:sjmmajor
ID: 7165504
Didn't get anything except for a message when I clicked on the command button twice.  It gave me an open error.  I just tried and moved the code that was in the sub form1_unload to the sub text1_change() and it just worked there.  I see the update.  Is there a reason?  The way I have this written is this considered an Disconnected recordset or is that a whole other ballgame?
0
 

Expert Comment

by:CarterMitchell
ID: 7165761
Your problem is simple: you are setting your lock type to Batch Optimistic and using an Update method on the recordset. Two ways to go here:
1. set the lock type to adLockOptimistic OR
2. keep the lock type and use the UpdateBatch method on your ADO recordset
0
 

Author Comment

by:sjmmajor
ID: 7166570
This didn't work but thanks.  How do I get the points to gencross?  That code worked for me.
0
 

Author Comment

by:sjmmajor
ID: 7166573
Thanks for your help!  
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question