Hi, I have found the following code which I think will open a database and search a field, I am looking to see if we can search per-field:
Code
Private Sub MemberDetailsToXL()
'The name of the database
Const STR_DB_NAME As String = "C:\Path\MWCSDB.mdb"
'The DAO (3.51) object library is needed for these objects
Dim objEngine As DBEngine
Dim objDb As Database
Dim objRs As Recordset
Dim strSQL As String
Dim strTableName As String
Dim strFieldName As String
Dim lngMemNum As Long
'The name of the table/DataSource and field to look in
strTableName = "tblName"
strFieldName = "[lngMemID]"
'The member number to look for in the database
lngMemNum = CLng(ActiveCell.Value)
' SQL statement to query the database with
strSQL = "SELECT * FROM " & strTableName & _
" WHERE " & strFieldName & " = " & lngMemNum & ";"
'Initiate new DAO session, connect to the given database
'and open the above SQL query results
'MS Access will NOT be visible
Set objEngine = New DBEngine
Set objDb = objEngine.OpenDatabase(STR_DB_NAME)
Set objRs = objDb.OpenRecordset(strSQL)
'Takes the first result from the query and prints the fields
'on to the XL spreadsheet
With objRs
.MoveFirst
ActiveCell.Offset(0, 1).Value = ![lngMemID]
ActiveCell.Offset(0, 2).Value = ![strName]
.MoveNext
'If there is a 2nd record .EOF will return False
If Not .EOF Then
MsgBox "More than 1 record has been found.", vbOKOnly
End If
End With
'Destroys the DAO/MS Access objects
Set objRs = Nothing
Set objDb = Nothing
Set objEngine = Nothing
End Sub
Display More
any ideas on how it would open a database, look in a record, which may have 8 fields and then search them all, and return a match or close match?
thanks in advance