MS Access VB record retrieve, recordupdate

[Versys]

Hi, everybody

I am trying to retrieve a record from my access data base upon user's request to either update the record or add a new record, the scenario should be: the program ask for the code (key) to be retrieved, if it gets it then it shows the fields to be modified, otherwise user enter the fields to be added with the key previously entered, and added or updated.

Thanks for your support....


Vinnie....

 

[MattSTech]

An example of one way to do it.

Assumes:
1 Form called frmSearch
1 textbox called txtSearch
1 command button called cmdSearch
database.mdb located in the application path (Where your .exe file or project is)

You must reference Microsoft ActiveX Data objects 2.5 Library and Microsoft ADO Ext. 2.5 for DDL and Security

Private Sub cmdSearch_Click()
On Error GoTo ErrHandler

    strSearch = frmSearch.txtSearch.Text
    
frmSearch.MousePointer = vbHourglass

    Dim objCon As ADODB.Connection
    Dim strCon As String
    Dim objRS As ADODB.Recordset
        
        Set objCon = New ADODB.Connection
        Set objRS = New ADODB.Recordset
        strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info" & _
        "=False;Data Source=" & app.path & "\database.mdb"

        objCon.Open strCon
        objRS.Open ("tblSearch"), objCon, adOpenKeyset, adLockOptimistic, adCmdTable
        
            Do While Not objRS.EOF
                If objRS.Fields("Search_Field").Value = strSearch Then
                    msgbox "Record has been found!"
                    exit Do
                Else
                    objRS.MoveNext
                End If
            Loop
    		
	    If objrs.eof = true then
               objRS.AddNew
                   objRS.Fields("Search_Field").Value = strSearch
               objRS.Update
	    end if

    objRS.Close
    Set objRS = Nothing
    objCon.Close
    Set objCon = Nothing
    
frmSearch.MousePointer = vbDefault
Exit Sub
ErrHandler:
    Set objRS = Nothing
    Set objCon = Nothing
    msgbox "Bad things happened!" & vbnewline & err.number
End Sub

 

[Versys]

Hi MattSTech

Thanks for your reply!!!
Help me to understand this, if I have a table with let's say 10000 records, do this method gets/retrieve the record by key? Is there any other way like get it by the index "select mydata from mytable where mytable_key = strserch" or something like this? Which you be the faster method, performance wise?

Thanks again.

 

[MattSTech]

Certainly sql statements would be the way to go. Check out this Thread

My example was just one way to do it, definately not for 10000 records.

Matt