?
Solved

VB>Access

Posted on 2003-03-22
10
Medium Priority
?
159 Views
Last Modified: 2012-05-04
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
Comment
Question by:wa1ed
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 28

Accepted Solution

by:
vinnyd79 earned 664 total points
ID: 8186189
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
 

Assisted Solution

by:ataurrehman
ataurrehman earned 664 total points
ID: 8186318
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
 

Expert Comment

by:ataurrehman
ID: 8186319
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
Independent Software Vendors: 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!

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 8186450
You can use the DataForm Wizard (from the Add-ins manager) to start a project for you.
0
 
LVL 30

Assisted Solution

by:Mayank S
Mayank S earned 664 total points
ID: 8193667
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
 
LVL 30

Expert Comment

by:Mayank S
ID: 8193681
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
 
LVL 30

Expert Comment

by:Mayank S
ID: 8193687
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
 

Expert Comment

by:CleanupPing
ID: 8531833
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
 
LVL 6

Expert Comment

by:GPrentice00
ID: 8958697
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Suggested Courses
Course of the Month9 days, 12 hours left to enroll

762 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