Connecting with a back end access database

[dvdw]

I have a split Access 2003 database with all tables in the back end. Being a relative newcomer to vb I want to search each back end table for a particular value in a field and then list all tables in which the value is contained. (It is actually a "Where Used" search for a part in various parts list.)

The help I need is advice on the best way to connect to the back end, there seems to be a number of options such as ADO or DAO. Can someone point me in the right direction, preferably with reference to a detailed tutorial on the subject.

Thanks.

 

[DCrake]

Are you actually looking for a part number or a field that holds the part number? Either way you should know which tables have the neccessary field in it. Being the case you would simply perform DLookups on the tables to search for actual part numbers.

 

[dvdw]

I am looking to see if the part number is contained in the PartNo field of multiple tables. I am trying to do this using vb from the front end database, hence the need to make a "connection" with the backend. I cant search the linked tables in the front end as there are other very large tables I don't want to search.

 

[DCrake]

Whether the table is a linked table or not you will only need to search the tables that have a field called PartNo in it. The fact you have other large tables does not matter as it is you that is telling access which tables to look in.

 

[ezfriend]

DVDW, as DCrake pointed out, you should know where the data resides. Then it is just a matter of running a simple SELECT statement to get/search for what you are looking for.

Private Function GetPartNumber(byval sPartNum as string) as String

    Dim rs as Recordset
    Dim sSQL as String

    sSQL = ""
    sSQL = sSQL & "SELECT PartNo FROM TABLE_NAME "
    sSQL = sSQL & "WHERE PartNo = '" & sPartNum & "' "

    set rs = CurrentDB.OpenRecordset(sSQL)

    If rs.EOF = False then
        GetPartNumber= rs!PartNo & ""
    Else
        GetPartNumber=""
    End If

    rs.Close
    Set rs = Nothing

End Function