Pulling value from table using VBA (ADO)

[Matt]

Hey Guys, I have a table (selectedNameTable)
with one record in it and 3 fields (Last Name,
First Name, Contact ID), and I'm trying to
use the value from the Contact ID field and
haven't had luck getting that value using ADO.
(note: I'm very novice at ADO When I create
the recordset, how do I link it to the table,
and from there how do I refer to the value in
the "Contact ID" field?
Thanks for your time,
Matt

 

[Chris]

Matt,

First off, I don't recommend putting spaces in and field names.

Now to answer your question:


Sub Test()

Dim conn As ADODB.Connection 'Connection to the database
Dim rst As ADODB.Recordset 'Recordset to get/hold the
data
Dim strSQL as String 'Will Hold the SQL
String for the recordset
Set conn = CurrentProject.Connection 'Access 2000 and XP

Set rst = New ADODB.Recordset

strSQL = "Select ContactID, LastName, FirstName from SelectedNameTable"

rst.Open strSQL, conn 'Opens the recordset

'If you want to loop through the recordset
Do Until rst.Eof
Debug.Print rst("ContactID")
'Do something else here

'Move to the next record
rst.MoveNext
Loop


rst.Close
Set rst = Nothing
conn.Close
Set conn = nothing

End Sub