Lookup In Excel from Access Database

[ca1358]

I need help on the following code, since I am very new at this, I am lost.

I am using a excel spreadsheet. In A1 is criteria "A1[AS400 ID]"
A2 needs to be the result. I have used lookup in Access VBA code but now
need to translate to Excel VBA code. So I may be totally on the wrong track.


Any help would be appreciated



Sub ADOImportFromAccessTable()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Dim TargetRange As Range

Set TargetRange = TargetRange.Cells(2, 1)


' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
[\\Dtcnas-ilsp002\mandatory\Analysts - Working
Files\Carol\Demo\Tradelimit.mdb] & ";"

Set rs = New ADODB.Recordset
With rs
' open the recordset
.Open [End of year incentive], cn, adOpenStatic, adLockOptimistic,
adCmdTable


'Criteria Range A1[AS400 ID]
'I NEED TO DO A LOOKUP
'A2 = DLookup("[V05 GOVT]", "End of year incentive", "[AS400 #] = " _
& Forms!Form![AS400 ID])

'Copy range to A2
Range("A2").CopyFromRecordset rs


End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub