• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

VB>Access

Hi, I am an EXTREME newbie who would like to play with Visual basic.  I have visual Studio 6.  Can someone walk me through what I need to do to start a data project and connect to an access database?  I think I can continue from there.  I just cannot seem to connect to my access DB.
ED
WA1ED
0
wa1ed
Asked:
wa1ed
3 Solutions
 
vinnyd79Commented:
here is a basic example:

' add a reference to Microsoft ActiveX Data objects Library(pref 2.5 or higher)
' add to declarations area
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Form_Load()
Dim sDatabase As String
sDatabase = "C:\MyDatabase.mdb"
' open connection
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & sDatabase & "'"
cn.CursorLocation = adUseClient
cn.Open
' open recordset
Set rs = New ADODB.Recordset
rs.Open "Select * From YourTableName", cn, adOpenKeyset, adLockOptimistic
End Sub

Private Sub Command1_Click()
' add record to table
With rs
    .AddNew
    .Fields("FieldName1").Value = "Value to enter in Fields1"
    .Fields("FieldName2").Value = "Value to enter in Fields2"
    .Fields("FieldName3").Value = "Value to enter in Fields3"
    .Update
End With
End Sub

Private Sub Command2_Click()
' Loop through records
With rs
    .MoveFirst
    Do Until .EOF = True
    MsgBox .Fields("FieldName1").Value
    .MoveNext
    Loop
End With
End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
' clean up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
0
 
ataurrehmanCommented:
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")

     




0
 
ataurrehmanCommented:
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")

     




0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Éric MoreauSenior .Net ConsultantCommented:
You can use the DataForm Wizard (from the Add-ins manager) to start a project for you.
0
 
Mayank SAssociate Director - Product EngineeringCommented:
You can use the data control (locate it on the toolbar). For reading data from a table, you can use the following code:

Data1.DatabaseName = "...." ' path of the .mdb file
Data1.RecordSource = "TABLE_NAME" ' or query-string
Data1.Refresh

With Data1.Recordset
  If .EOF Then
    MsgBox "No records found. "
  Else
    ' access individual attributes as ![AttributeName]
    .MoveNext
  End Else
End With

Mayank.
0
 
Mayank SAssociate Director - Product EngineeringCommented:
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.
0
 
Mayank SAssociate Director - Product EngineeringCommented:
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.
0
 
CleanupPingCommented:
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.
0
 
GPrentice00Commented:
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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now