Relate two lAdodc links


I am trying to simulate a DLookup in Visual Basic.  What I have is an Adodc link to a table with part numbers in my own Access db.  I need to look up values associated with each part number from a tbl on our network.  I created a second Adodc connection using ODBC to the tbl I needed on our network.  Is there a way to relate the two connections?   I want to view related data in ODBC linked tbl to part numbers in my own Access tbl, say in a Change event.

Can this be done?  Any help would be appreciated.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Stephen MandersonSoftware EngineerCommented:
So are you trying to compair two seperate db's ? And what output format are you trying to show it as ?
JermyDAuthor Commented:
Well, I have my Access db that I'm linked to through an Adodc control.  Within that db is tblmain.  We have a tbl on our network called db_hnymastr.  I linked to this tbl also with Adodc, but using ODBC conn instead of Jet 4.0.  I wanted to see if I could enter a part number in a text box on VB form that is linked to tblmain.  Then I wanted to see a field of information about that part number from dbo_hnymastr.  The only problem is that the Adodc links on the VB form act sparately.  If I search for part number in Adodc1 it finds it, but not in Adodc2.  

I wanted to link dbo_hnymastr in my Access db.  I can do that.  But the tbl is read-only, so I can't connect to it in VB through Access (it seems).

I figure if I gave both connections the same name, Adodc1, then it would find the number in both my Access tbl and dbo_hnymastr.  But the connections are different, JET 4.0 and ODBC.  

Is there a way to find the same part number in both tbls at the same time (I'm using your find method from your sample).

Thanks very much, you've been more than helpful already.  I don't deserve this much attention.

Stephen MandersonSoftware EngineerCommented:
You could try using a single text box with a button and when you call to find the part number in each table
it will use the part number in the text box and populate both tables with the requested number. as long as you are going to compair the two tables i guess it can be done. If you paste your jet and odbc code ill make them both do a search.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

JermyDAuthor Commented:

Thanks for responding.  I'm not using code for the links, I just used the Adodc button and then set the properties and built links with build buttons.  I think I know what you mean, though.  I have two Adodc links, named Adodc1 and Adodc2.  Could I just add extra lines in the search code to reflect both recorsets rather than just the one?  Like this maybe:

Public Sub CSearch(SCriteria)
Dim found As Boolean
           'Check if there are any records
           If Adodc1.Recordset.RecordCount = 0 Then                                                           'Adodc2 always has records (read-only table)
           MsgBox "No records to search", vbInformation, "Search Status"
           Exit Sub
           End If
'Move to first record first or records before the current record
'wont be searched
'Loop through database record
Do Until Adodc1.Recordset.EOF Or found
    'Check if the current record fields are like the search criteria
    If Adodc1.Recordset.Fields("partno") And Adodc2.Recordset.Fields("part_number") Like SCriteria Then                'not sure about this line
        found = True
        'Move to next record
    End If
'If record wasn't found then tell the user
If found = False Then
    MsgBox "Record not found", vbInformation, "Record Search"
    'Move back to the first record
End If
'Show record number
Adodc1.Caption = Adodc1.Recordset.AbsolutePosition & " of " & Adodc1.Recordset.RecordCount
Adodc2.Caption = Adodc2.Recordset.AbsolutePosition & " of " & Adodc2.Recordset.RecordCount
End Sub

Thanks very much.  You are too kind.

JermyDAuthor Commented:
I got it.  I just duplicated the code for the other recordset.  Probably not a good way to do it, but I'll go with it for now.
Stephen MandersonSoftware EngineerCommented:

Stephen MandersonSoftware EngineerCommented:
Jeremy, you should put into get your points refunded.
PAQed with points refunded (50)

Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.