Excel Lookup in Access

[Guest]

What I have is this situation (in its simplest form)
I have an MS Access Database called Device Signals.mdb with a table called
“Devices†that contains 3 Fields (Primary Key, Acronym, Description). There
are 160,000 records in the database. Example of a worse case acronym in the
Acronym Field is L30_LST.SWI. The Description Field is simply the text
description of the Acronym. Both fields are formatted as text in MS Access.

In Excel I have a single spreadsheet I call (Sheet 1). Sheet 1 has 2 Rows.
The 2 rows can sometimes be 200 columns wide. Row 1 is Acronyms pasted from
field data, which is never in the same order. Row 2 is a place-holder for the
Descriptions.

What I need is some code (similar to VLOOKUP in Excel) to scan Row 1, got to
the Access database lookup the Acronyms in the “Acronyms Field†in Access and
copy the descriptions from the “Descriptions Field†into Row 2 of my Excel
Spreadsheet under the correct Acronym.
I have tried numerous versions of code without success, some work on
numbers, others on text, but none thus far have worked on alpha-numeric data
also taking into account spaces.

Can anyone Help

Thanks in advance

Romefucan

 

[NickHK]

No code, so I don't know how you are trying to do this. But with an ADO
connection and .execute some SQL in a loop.
SQL="UPDATE Devices SET Description=" & Chr(34) & Cells(2,Counter) & chr(34)
& " WHERE Acronym=" & Chr(34) & Cells(1,Counter) & Chr(34)
Trap the error if the update fails.

Or you can try a BatchUpdate of a recordset from the original data.

NickHK

 

[Guest]

NickHK
Sorry my bust
Code as follows

Dim adoCN As ADODB.Connection
Dim strSQL As String

Const DatabasePath As String = "D:\Visual Engineering\Acronym.mdb"

'Function argument descriptions
'LookupFieldName - the field you wish to search
'LookupValue - the value in LookupFieldName you're searching for
'ReturnField - the matching field containing the value you wish to return

Public Function DBVLookUp(TableName As String, _
LookUpFieldName As String, _
LookupValue As String, _
ReturnField As String) As Variant
Dim adoRS As ADODB.Recordset
If adoCN Is Nothing Then SetUpConnection

Set adoRS = New ADODB.Recordset
strSQL = "SELECT " & LookUpFieldName & ", " & ReturnField & _
" FROM " & TableName & _
" WHERE " & LookUpFieldName & "=" & LookupValue & ";"
' If lookup value is a number then remove the two '
adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
If adoRS.BOF And adoRS.EOF Then
DBVLookUp = "Value not Found"
Else
DBVLookUp = adoRS.Fields(ReturnField).Value
End If
adoRS.Close
End Function

Sub SetUpConnection()
On Error GoTo ErrHandler
Set adoCN = New Connection
adoCN.Provider = "Microsoft.Jet.OLEDB.4.0" 'Change to 3.51 for Access 97
adoCN.ConnectionString = DatabasePath
adoCN.Open
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation, "An error occurred"
End Sub

Romefucan

 

[NickHK]

OK, I was looking at it the wrong way round.
Here's one way, but you need to hit the DB every time :

Dim Cell As Range
Dim SQL
For Each Cell In Range("acronyms")
SQL = "SELECT Description FROM <Table> WHERE Acronym=" & Chr(34) &
Cell.Value & Chr(34)
adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
If adoRS.BOF And adoRS.EOF Then
Cell.Offset(0, 1).Value = "N/A"
Else
Cell.Offset(0, 1).Value = adoRS.Fields(ReturnField).Value
End If
Next

Depending on memory considerations, you could just get all the data locally,
then loop through the RS. Something like this psuedo code :

SQL = "SELECT Acronym, Description FROM <Table>"
adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly

For Each Cell In Range("acronyms")
adoRS.Find or Seek (depending on cursors etc)
If NothingFound Then
Cell.Offset(0, 1).Value = "N/A"
Else
Cell.Offset(0, 1).Value = adoRS.Fields(Found).Value
End If
Next

NickHK