Brent Try this sub below.

Note if the lookup value is not text then change line:

.CommandText = "Select AE from table2 where ECT = '" &
Cells(iLoopRow, 1) & "'"

with:
.CommandText = "Select AE from table2 where ECT = " &
Cells(iLoopRow, 1)

This code assumes you have an Access database called: C:\temp\yourfile.mdb
And that your lookup data is in column 1(A) and you want to return your data
to Column 2 (B)


Sub Lookup

Dim iLoopRow As Integer

Set cnnConn = New ADODB.Connection
With cnnConn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open "C:\temp\yourfile.mdb"
End With

Set cmdcommand = New ADODB.Command
Set cmdcommand.ActiveConnection = cnnConn

Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn

With cmdcommand
.CommandType = adCmdText

'*** Get data for 100 rows
For iLoopRow = 1 To 100
.CommandText = "Select AE from table2 where ECT = '" &
Cells(iLoopRow, 1) & "'"
rstRecordset.Open cmdcommand

'*** Test to see if a lookup value was returned
If rstRecordset.EOF Then
Cells(iLoopRow, 2) = "Unknown"
Else
Cells(iLoopRow, 2) = rstRecordset("AE")
End If '*** No record returned

rstRecordset.Close

Next iLoopRow
End With '*** cmdcommand


'*** Cleanup
Set cmdcommand = Nothing
Set rstRecordset = Nothing
cnnConn.Close
Set cnnConn = Nothing


End Sub


Please post if this was helpful. Good luck.
--
Stewart Rogers
DataSort Software, L.C.


"Brent" wrote:

I am trying to do something like a vlookup in an Excel maco using ADO/VBA,
which looks up a value in an Access table and returns the corresponding value
to a variable in Excel. I have tried the this:

loc = "Select AE from table2 where ECT = Range("A" & rws).value"

where AE and ECT are columns in table2 and rws is a counter-like variable.
Any suggestions (websites, references) would be greatly appreciated!!