INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Visual Basic (Microsoft) Versions 5/6 FAQ

Database

ADO Basics -- Connect to Access Database by billybobk
Posted: 17 Jun 03 (Edited 31 Jul 03)

'This WILL let you connect to an Access database
'First, in the Project menu, choose References...
'then check the Microsoft ActiveX Data Objects 2.x Library
'Also select Microsoft ActiveX Data Objects 2.x Recordset Library
'2.6 works fine here.
'Also select Microsoft Data Access Components Installed Version
'Otherwise, forget about ADO!
'The three main OBJECTS of an ADO connection are the Connection, the Recordset, and the Command objects.For reading data, you need all three. For writing or deleting, you only need a Connection and a Command. Now declare your objects:
**************************************

Sub Get_Accountno()
    Dim Conn as ADODB.Connection  'Connection
    Dim Rs as ADODB.Recordset     'Recordset
    Dim strQuery As String        'Command

'Set, describe, and open the connection to the database:

Set Conn = New ADODB.Connection
  With Conn
  .CursorLocation = adUseClient
  .ConnectionString= "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\MyPath\myDatabase.mdb"
  .Open
  End With
   
'Set and define your Command:

strQuery = "SELECT Accountno FROM tblTable " & _
"WHERE  Username = 'BillK'"
   
'Set, define and open the recordset. You also assign the
'connection and the command that will allow you to retrieve
'the recordset:

Set Rs = New ADODB.Recordset
    With Rs
    Set .ActiveConnection = Conn
        .CursorType = adOpenStatic
        .Source = strQuery
        .Open
    End With
    
'Now you have a 'snapshot' of the recordset in memory, but
'what good is the recordset if you can't see it? "Set" it to a textbox
'(or any number of other data-bindable controls like DataList,
'DataCombo, Label, etc.)
'like this:

Set txtAccountno.DataSource = Rs
    With txtAccountno
        .DataField = "Accountno"
        .Refresh
    End With

'Now you can do whatever you wish with the accountno, because
'it is there as txtAccountno.Text!
'Don't ever leave an open connection or recordset hanging
'open! Clean up like this:
    
   Rs.Close
   Set Rs = Nothing
   Conn.Close
   Set Conn = Nothing
End Sub


Back to Visual Basic (Microsoft) Versions 5/6 FAQ Index
Back to Visual Basic (Microsoft) Versions 5/6 Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close