VB>Access

It's simple ... u need to learn about ADO (Active Data Objects).

Steps:

1) Open Visual Basic. Select Projects -> References and select Microsoft ActiveX Data Objects 2.5 Library.

2) In your form create a variable
   Private objConnect As ADODB.Connection
   Private objCmd As ADODB.Command
   Private objRst As ADODB.Recordset

3) Create a method

Public Sub setConnection()
    Set objConnect = New ADODB.Connection
   
   objConnect.ConnectionString    = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=C:\YourApp\AccessData.mdb"
   
    objConnect.Open
    'Set up command Object
    Set objCmd = New ADODB.Command
    Set objCmd.ActiveConnection = objConnect
    objCmd.CommandType = 1  'adCmdText
   
End Sub

This method would establish the connection with the access database and setup command object.

4) Now create another method to open the result set

Public Function openRecordSet()
    objCmd.CommandText = "select * from yourTable"
    Set objRst = New ADODB.Recordset
    Set objRst.ActiveConnection = objConnect
    Set objRst.Source = objCmd
    'Set Locktype and Cursortype
    objRst.LockType = adLockOptimistic '3
    objRst.CursorType = adOpenStatic
   
    objRst.Open
End Function

This method would open the record set based upon the query you set in objCmd.CommandText.

5) As record set is open, you can display the value or update records. e.g. try to display a field's value in a message box like
   
     objRst.moveFirst
     msgbox objRst("FieldName")

     

You can use the DataForm Wizard (from the Add-ins manager) to start a project for you.

To add an entry into a table, use:

Data1.DatabaseName = "...." ' path of the .mdb file
Data1.Database.OpenRecordset ("TABLE_NAME")
Data1.Recordset.AddNew
Data1.Recordset![Attribute1] = value1
' set values for the attributes this way, and then:
Data1.Recordset.Update


Mayank.

To modify an existing entry, use:

Data1.DatabaseName = "...." ' path of the .mdb file
Data1.RecordSource = "SELECT * FROM TABLE WHERE KEY = " & CStr (value) & " ; " ' identify record - can also be used for more than one records depending upon the query
Data1.Refresh

With Data1.Recordset
  If Not .EOF Then
    .Edit
    ![Attribute] = newValue ' update other attributes to, if needed
    .Update
  End If
End With


Hope that helps!

Mayank.

Hi wa1ed,
This old question (QID 20559543) needs to be finalized -- accept an answer, split points, or get a refund.  Please see http://www.cityofangels.com/Experts/Closing.htm for information and options.

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

-->Split between vinnyd79 and ataurrehman and mayankeagle

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

GPrentice00
EE Cleanup Volunteer