accessing linked tables in MS Access via .NET (VB.net, Access 2K3)

Topic

New Reply

Greetings!

I’m currently working on a web page that will hopefully be able to run queries that pull from tables both created in MS Access and tables that are linked in MS Access (specifically, Pervasive Timberline tables)

My page has no problem connecting to and displaying results pulled from the native Access tables, but any queries that involve data from one of the linked tables produces an error: “Connection to ‘Timberline’ – failed”.

I originally developed these queries directly in Access, and they run fine, but the transition to .Net seems to leave me in a bind.

I’m wondering if anyone has successfully been able to accomplish this, and if so, or even if not, I’d love to hear any suggestions you have.

Here are the relevant sections of code (note: MASTER_JCM_JOB_1 is the linked table in this SQL, and tblMain & tblSub are native Access tables)

Code:

Dim conClsf As IdbConnection = New OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0; Data Source=” & server.mappath(“Excavation.mdb”) & “;”)

Code:

conClsf.Open

Dim strSQL as String = “SELECT tblMain.JobNumber, MASTER_JCM_JOB_1.Total_Labor_Units_Est, MASTER_JCM_JOB_1.JTD_Labor_Units ” & _
“FROM MASTER_JCM_JOB_1 INNER JOIN (tblMain INNER JOIN tblSub ON tblMain.ID = tblSub.ID) ON MASTER_JCM_JOB_1.Job = tblMain.JobNumber ” & _
“GROUP BY tblMain.JobNumber, MASTER_JCM_JOB_1.Total_Labor_Units_Est, MASTER_JCM_JOB_1.JTD_Labor_Units ” & _
“HAVING (((tblMain.JobNumber)='” & TextJobPerf.Text & “‘));”

Dim cmdMbrs As OleDbCommand = New OleDbCommand(strSQL,conClsf)
Dim rdrMbrs As IDataReader = cmdMbrs.ExecuteReader

(the error occurs on this last line of code)

Thanks much!!!!

Reply | Quote

Replies

I think the linked table is where you problem lies. It doesn’t seem to be possible to perform a query against a data source that uses linked tables from ASP.NET. See this question (and accepted answer) from Experts Exchange.

An alternative solution here, which would require a bit of extra effort, would be:
– Fill a dataset with the raw data from table 1
– Fill the same dataset with raw data from table 2
– Create a relationship in the dataset between the two tables
– Perform the selected statement against the two tables

Assuming you are forced to use multiple MDB files, this seems like the best way to accomplish the goal, although it’s probably not very efficient.

Hope this helps

Reply | Quote

That’s what I was afraid of. Are you familiar with any Pervasive syntax? I think I read that it works with the SqlClient namespace (using SqlConnection, SqlDataAdapter, etc.) If that’s the case, I could get the data, as you suggested, in a dataset and work with it like that, right?

Thanks for the help!

Reply | Quote

I’m not familiar with Pervasive. But if you can link to it in Access, you can almost certainly link to it in .NET. You’ll probably want to use the OLEDB or ODBC objects rather than SQL Server.

You *should* be able to perform a basic SELECT * FROM YourTable against almost any data source in order to populate the Dataset. Once you’ve to the data into the dataset you can do anything you’d like with it.

Reply | Quote