[RESOLVED] .NET joining tables in two Access databases

[Shaggy Hiker]

I've run into a problem that I've never seen before. The bulk of the data in a particular view comes from one Access database, but one field may be wanted from a related table in a totally different Access database. I have zero control over the first database, and little control over the second. Frankly, I can see three ways to go, but I'm looking for a better way than any of them. The three ways are these:

1) The number of records is trivial, so create a datatable from the first database, then add a column and query the second database for the information for that column record by record. This appears to be the way the legacy program worked....sort of, except that it didn't do this well, so I'd want to do it better.

2) Create a datatable containing the information from the first DB, then create a datatable containing the information from the second DB, then join the two using LINQ, and display.

3) Declare the whole thing silly and move on. After all, there is no reason that I can see to have the information from the second database, and since the legacy program wasn't working right anyways, it may be that nobody would care if I left it out.

The only argument against #3 is that I hate to leave it at that. I'd rather go with option #2, but I was hoping there might be a way to make a JOIN across two different databases. This can be done in SQL Server, but I don't know about Access. Any ideas?

[si_the_geek]

There are ways to make a Join across file-based databases, here is one of the methods available:
http://msdn2.microsoft.com/en-us/library/bb177907.aspx

[techgnome]

that was my first thought too, si.... but then I read option 3... and immediately thought of option 5 (with #4 being the result of the link Si posted) - talk to the users, find out if they do care... if they do, then go with #2 or #4 ... if they don't go with #3.

-tg

[Shaggy Hiker]

I have the great fortune in this case that the users are friends as well as colleagues. The reason I'd skip over #3 is only to go a bit above and beyond for them. Otherwise, I'd certainly be doing that. The existing program took what now must be considered Option #6, which was to just add the column to the datatable, but not actually populate it with anything until after the user took some kind of action. This is kind of like Option #1, except that they didn't bother querying the second database for the information, they waited for the user to get the information (or enter the information) and showed it only then. I do have to ask the user if that behavior was critical, since the lack of data in the field could act as an indication that the user hasn't visited that record, but that seems really unlikely, since the user would normally visit only one record each time they used the program, so the other empty cells were kind of useless.

[techgnome]

Do a sort of lazy-load on the data... certainly possible...

-tg

[Shaggy Hiker]

After several experimental contortions (I was unable to actually get my head up there, so the problem was not what I thought it might be), I got the code working. Oddly, I had tried the correct solution first, but messed it up, because it failed cryptically. I then tried a few other things that didn't work, but taught me a few things. I was in the middle of posting a follow-up question to this thread, and was posting the various contortions I had tried, but realized that I had lost that first one and needed to re-create it so that I could post it in the question. When I re-created it, I did it right, and it all worked, so I abandoned the follow-up question. The solution that Si posted was made to work. I wasn't able to create a JOIN in the FROM clause to join a table from one database to a table in a second database, but that was ok. What I did was use a nested SELECT clause to populate the field I needed as one field in the outer SELECT clause. The performance of this construct remains to be seen, but it should never be used on a datatable with a large number of rows. In fact, the number of rows will probably be only a few dozen, at most.

Therefore, the whole thing is resolved.

[techgnome]

Dave's Law. It states: The moment a problem is observed by a second developer, the ease with which the problem is solved becomes inversely proportional to the time already spent trying to solve it.

The Rubber Ducky Effect - where one of the programmers (at IBM) used to debug by talking the bug through with a yellow rubber duck toy thus helping him solve the problem.

-tg