How to access related tables in MS Access Database

Hope someone out there can help as I've tried to find an easy way to read the data in one MS Access table that has columns whose data is in another table but the tables are related by say the AutoNumber column of the second table. I assume I have to open the two databases into the same dataset but then how to relate them? Then once they are related if I call to display the data in the first table will the linked data be filled automatically from the second table to the webpage? This seems to be a pretty standard need but I haven't found any information out there through searches. Thanks in advance. Arnold

 

yes this is ASP.NET. Actually, my issue is that the column (UserID) of one table (TableA) is populated with values in a column (EmployeeID) from another table (TableB)in the same database. The links are created using the "Lookup Column" command in MS Access so how to handle these relationships? I had read that ASP.NET handles this and assumed it could handle any database automaticallybut this is probably not the case. I would like to display records fromTableA which has a lookup column (UserID) linked from the (EmployeeID) column of TableB. Here's how I open TableA and fill the dataset abcDS:

SelectStatement = "Select * From TableA"
Connect.ConnectionString = ConnectString
Adapter.SelectCommand = _
new OleDbCommand(SelectStatement, Connect)
Adapter.SelectCommand.Connection.Open
Adapter.Fill(abcDS,"TableA")
AssetsGrid.DataSource = abcDS.Tables("TableA")
Page.DataBind

at this point if I change the command to fetch TableB so I can later "link/relate" them in ASP.NET I get the error that the database is already opened. How do I fetch the second table and preserve the relationship between the two columns? Thanks in advance

 

If I understand your needs correctly, one of the following two select statements should join tables A and B and return the result set you desire.


If you want only the rows where a matching record exists in both TableA and TableB, use this select statement:
SELECT TableA.*, TableB.*
FROM TableA INNER JOIN TableB ON TableA.UserId = TableB.EmployeeID;


To include all rows in TableA, whether or not there is a match in Table B, use this select statement:
SELECT TableA.*, TableB.*
FROM TableA LEFT JOIN TableB ON TableA.UserId = TableB.EmployeeId;