ADO Basics -- Connect to Access Database

'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