Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 139
  • Last Modified:

Relate two lAdodc links

Hello,

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.

Thanks

Jeremy
0
JermyD
Asked:
JermyD
  • 4
  • 3
1 Solution
 
Stephen MandersonCommented:
So are you trying to compair two seperate db's ? And what output format are you trying to show it as ?
0
 
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.

Jeremy
0
 
Stephen MandersonCommented:
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.

Manderson
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
JermyDAuthor Commented:
Hello

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
Adodc1.Recordset.MoveFirst
Adodc2.Recordset.MoveFirst
'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
    Else
        'Move to next record
        Adodc1.Recordset.MoveNext
        Adodc2.Recordset.MoveNext
    End If
Loop
'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
    Adodc1.Recordset.MoveFirst
    Adodc2.Recordset.MoveFirst
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.

Jeremy
0
 
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.
0
 
Stephen MandersonCommented:
Sorted.

Manderson
0
 
Stephen MandersonCommented:
Jeremy, you should put into get your points refunded.
0
 
moduloCommented:
PAQed with points refunded (50)

modulo
Community Support Moderator
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now