retreive from recordset

[buddyel]

I am using VBA in Excel and trying to retrieve a record from an access db and insert the fields for that record into corresponding cells.

Fields.item("dwgno&quot = Range("b8&quot

I would like the retrieve the record where the field (dwgno) matches the value entered in Cell B8 in the spreadsheet. I have trying for days, can anyone help....please. Thanks

 

[ttmac]

Try the following:

Paste the following function into a new excel module.

Option Explicit

Function searchDB(SearchFor As String)

Dim strConnection As String
Dim objConn As Object
Dim myRS As Object
Dim mySQL As String


'Replace "c:\Your Path\database name.mdb" with the full path and name of your database

strConnection = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = c:\Your Path\database name.mdb;" & _
"Jet OLEDBatabase Password=Scuba; "

Set objConn = CreateObject("ADODB.Connection&quot

objConn.Open strConnection

Set myRS = CreateObject("ADODB.Recordset&quot

'The following is the SQL uesd to search the table tablename where field1 = search string

mySQL = "SELECT tablename.fieldname1, tablename.fieldname2, tablename.fieldname3 " & _
"FROM tablename " & _
"WHERE tablename.fieldname1 ='" & SearchFor & "';"

Set myRS = objConn.Execute(mySQL)


If myRS.EOF Or myRS.BOF Then
MsgBox "Records not found"
Else
MsgBox "Records Found"
searchDB = myRS.fields("LastName&quot
End If

End Function


To use insert the following into a cell
"=searchDB(cellreference)"

Tom