This can't be right:
Data Source=" & ThisWorkbook.Path & "Q:\IT\Database Masters\Guarantees2.mdb

try
MsgBox strConn
somewhere and see what it reads.

HTH. Best wishes Harald



"richard gregson" wrote in message
...
I am trying to return a value from an access database by looking up a value
in excel. This is the code I have - but I cannot get it to work - I keep
getting a run time error not a valid file name at the cnn.open part -
please help!

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

'Q:\IT\Database Masters\Guarantees2.mdb
'
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & ThisWorkbook.Path & "Q:\IT\Database
Masters\Guarantees2.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 tblBank Gtes.* FROM tblBank Gtes WHERE(((tblBank
Gtes.GTEE_NMBR)='" & cellPointer & "'));"
'If you project number field is number use this sSQL
'sSQL = "SELECT tblBank Gtes.* FROM tblBank Gtes WHERE" (((tblBank
Gtes.GTEE_NMBR)=" & cellPointer & "));"
Set rs = New ADODB.Recordset

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

cnn.Close
Set cnn = Nothing
End Sub