This will do it
Sub getProjectDescFromAccess()
'Needs reference the Axtive X Library 2.0 or higher
Const projectIDColumn = "B"
Const projectDescColumn = "C"
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim looper As Long
Dim cellPointer As Variant

'C:\PathToYourMdb\Database.mdb (Change)
'
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & ThisWorkbook.Path & "\db2.mdb;Persist Security Info=False"
Set cnn = New ADODB.Connection
cnn.Open strConn

For looper = 2 To Range(projectIDColumn & Rows.Count).End(xlUp).Row
Set cellPointer = Worksheets("Sheet1").Range(projectIDColumn & looper)
'If you project number field is text use this sSQL
'sSQL = "SELECT tblProjects.* FROM tblProjects WHERE
(((tblProjects.PROJECT_NUM)='" & cellPointer & "'));"
'If you project number field is number use this sSQL
sSQL = "SELECT tblProjects.* FROM tblProjects WHERE
(((tblProjects.PROJECT_NUM)=" & cellPointer & "));"
Set rs = New ADODB.Recordset

rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
If Not IsNull(rs.Fields("PROJECT_DESCRIPTION").Value) Then
Range(projectDescColumn & looper) =
rs.Fields("PROJECT_DESCRIPTION").Value
End If
rs.Close
Set rs = Nothing
Next looper

cnn.Close
Set cnn = Nothing
End Sub

"LenJr" wrote:

Thanks for the quick response....but I am looking to import the data based on
the Project number listed in the B column. So just importing the table would
not be what I am looking for. I think I need to write some VBA doing a
connection to that database and using the values from B2, B3..... to look up
the description from the record set of the tblProjects....the values in
column B would be the Key to the tblProjects. At that point I need to
import(write out) the Description from the table lookup to Column C in excel.
Exmple:
Workbook before run the macro
A B
1 project# Description
2 123
3 456
4 789

Workbook After run the macro
A B
1 project# Description
2 123 Project name1
3 456 Project name2
4 789 Project name3



"Joel" wrote:

try recording a macro while manually importing the data

From excel worksheet
1) Sttart recording a macro from the Tools - Macro menu
2) Import data from access by going to Data - Import external data - New
database Query
3) select MS Access Database
4) select Browse and locate your database
5) select data you want to import.
6) When finish stop recording macro from Tools - Macro menu


"LenJr" wrote:

I have an Excel workbook that I would like to have a macro run to update a
description field. This macro would link to an Access table and look up a
value and write it to a column in the open workbook. For example:
in my workbook I have column B that have project number in it (Range B2....)
and column C that I would like to populate with the project description. I
have an access database table tblProjects that has the Project number and
Description. I would like to run a macro that would connect to the access
database and use the value in Column B from the open Excel workbook and look
up that value on the tblProjects and write the Description in Column C of the
excel workbook. Some of the values in Column B will not be found on the
tblProject and I would like to skip those(not write any value in column C).

Thanks for any input.