Comparing local table with another Access database table

[aqif]

HI

I got a table TblStudents with RollNumber and Name in my local database. I want to write a code which allows me to compare with another database table having same fields. So far I am able to able to choose another database. What I want to do is that when the user choose the database than it will automatically compare the local tblStudents with extrenal database tblStudents and return me how many records are similar in terms of roll number. So far I have wrriten this much of code


Dim ExtDB As Database
Dim IntDB as Database
Dim strTNs As String
Dim strPath As String
Dim ExtRS as RecordSet
Dim IntRS as RecordSet

strPath = Me.TxtPath 'the path is filled by File Open Dialogue box

'Internal Database Connection
Set IntDB = CurrentDB()
Set IntRS = InttDB.OpenRecordset("tblStudents")

'External Database Connection
Set ExtDB = DBEngine.Workspaces(0).OpenDatabase(strPath)
Set ExtRS = ExtDB.OpenRecordset("tblStudents")

' Now I want a code which compares this external opened database tblStudents with my local tblStudents. I dont want to use LinkTable approach as by this way I will end up with 100s of link connections after some time.

Cheers!
Aqif

 

[FoFa]

Two things I can think of right off. With your approach you have to sort the recordsets and do a manual merge on the two recordsets. But have you thought of creating a single linked table, and when they choose an external DB just relinking to that table in code. That way you keep one link, and use a query to do the match for you.