468,527 Members | 2,109 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,527 developers. It's quick & easy.

Multiple Access Databases


Greetings,

I've inherited a project which requires the use of multiple Access
databases, each containing a dozen or so tables each.
I need to perform queries which included relations and results from
the contents of tables from separate databases.

I've got the table adapters configured in the XSDs, connection strings
in the web.config, and use data objects in the site's code, but for
the life of me I cannot figure out how to accomplish what I need.

As a temporary workaround, I'm using a single Access database
containing linked tables from the various other databases, but it is
my fear that this is not optimal, and is certainly not particularly
eloquent.

I've read the wretched MSDN content which comes with VS2005 till my
aging eyes are blurry, What am I missing here?
(Besides classic ASP, ADO, Lindy from Baltimore and Elaine from
Chicago...)

Thanks,
Greg G.
Aug 4 '07 #1
6 1831
"GregG" <Gr***@electron.comwrote in message
news:cb********************************@4ax.com...
I've got the table adapters configured in the XSDs, connection strings
in the web.config, and use data objects in the site's code, but for
the life of me I cannot figure out how to accomplish what I need.
Don't do any of that - use a DAL instead, e.g.
http://dotnetjunkies.com/Article/29E...7F100A9F9.dcik
As a temporary workaround, I'm using a single Access database
containing linked tables from the various other databases, but it is
my fear that this is not optimal, and is certainly not particularly
eloquent.
Upsize the individual Jet databases into a single SQL Server (Express)
database.
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

Aug 4 '07 #2
"GregG" <Gr***@electron.comwrote in message
news:kf********************************@4ax.com...
If you are saying that the use of ASP.NET requires formal
re-education, then perhaps it's not the right tool for the job, at
least for me. I've managed Assembler (1982), C (1984), dBase,
Clipper, QB (ha), C++ (1992), VBA etc without "formal training", so I
wouldn't think this would be insurmountable.
The .NET Framework is different in almost every way from what came before -
that is the biggest challenge when moving to .NET programming. E.g. ADO.NET
bears almost no resemblence to ADO... You're an experienced programmer, so
probably won't find the .NET Framework as daunting as someone who is more of
a newcomer, but you will still need to spend some time learning it...

I'd suggest you get one (preferably both) of these and work your way from
cover to cover:
http://www.amazon.com/ASP-NET-All-Re...6300677&sr=8-1
http://www.amazon.com/ASP-NET-Everyd...6300677&sr=8-3
Not meaning to sound like a smart-ass, truly, but at my age, more
acronyms such as DAAB, ConfAB, and ELCC are not particularly welcome
additions to a mind cluttered with bits of legalese, 30 year old IC
datasheets, specs for cars that haven't been on the road in 40 years,
and mountains of dirt on the local redneck politicians.
OK.
While the above digression may perhaps represent a failed attempt at
tongue in cheek / humor, the issue which remains is how to run a query
against three tables contained in three ACCESS databases and spit the
results to my logic so that I can produce ad hoc bar/pie/scatter
charts and return them to the browser - without reinventing the wheel.
Then I would respectfully suggest that you consider hiring in outside help
to do this particular piece of work - it should not take an experienced
ASP.NET programmer more than a day to complete, especially if they have
their own DAL which they're prepared to sell to you, which you can then use
for other work...
Alternate DB formats are not an option, nor is formal training in yet
another
temporal layer of proprietary abstraction.
Fair enough - as you said above, ASP.NET may not be the right tool for
you...
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

Aug 5 '07 #3
Mark Rae [MVP] said:
>"GregG" <Gr***@electron.comwrote in message
news:kf********************************@4ax.com.. .
>If you are saying that the use of ASP.NET requires formal
re-education, then perhaps it's not the right tool for the job, at
least for me. I've managed Assembler (1982), C (1984), dBase,
Clipper, QB (ha), C++ (1992), VBA etc without "formal training", so I
wouldn't think this would be insurmountable.

The .NET Framework is different in almost every way from what came before -
that is the biggest challenge when moving to .NET programming. E.g. ADO.NET
bears almost no resemblence to ADO... You're an experienced programmer, so
probably won't find the .NET Framework as daunting as someone who is more of
a newcomer, but you will still need to spend some time learning it...

You are correct. It is somewhat unlike anything I've worked with
before. But after a year, I've got a handle on most of it.

However, I have completed the app itself, including a charting engine,
ASHX handler, and real AJAX implementation rather than the pseudo MS
AJAX update panel stuff - which I was shocked to find running the
entire page at the server, including all databinds, on postbacks, even
though it only returns the relevant update panel's data to the
browser.

It is working properly with SQL Server2000 and through the
aforementioned kludge enabling queries on multiple Access MDBs.

>I'd suggest you get one (preferably both) of these and work your way from
cover to cover:
http://www.amazon.com/ASP-NET-All-Re...6300677&sr=8-1
http://www.amazon.com/ASP-NET-Everyd...6300677&sr=8-3

So, do these books contain an example DAL for multiple ACCESS
databases? If not, they are irrelevant. I already have several of Dino
Espisito's books on .NET 2.0.

>While the above digression may perhaps represent a failed attempt at
tongue in cheek / humor, the issue which remains is how to run a query
against three tables contained in three ACCESS databases and spit the
results to my logic so that I can produce ad hoc bar/pie/scatter
charts and return them to the browser - without reinventing the wheel.

Then I would respectfully suggest that you consider hiring in outside help
to do this particular piece of work - it should not take an experienced
ASP.NET programmer more than a day to complete, especially if they have
their own DAL which they're prepared to sell to you, which you can then use
for other work...

Not going to happen. What would we learn that way?

Actually, you have helped us in our recent struggle to decide whether
to continue development for MS products or move on to the growing body
of Linux converts.
Ciao,
Greg G.
Aug 5 '07 #4
"GregG" <Gr***@electron.comwrote in message
news:3m********************************@4ax.com...

Hi Greg,
>>Then I would respectfully suggest that you consider hiring in outside help
to do this particular piece of work - it should not take an experienced
ASP.NET programmer more than a day to complete, especially if they have
their own DAL which they're prepared to sell to you, which you can then
use
for other work...

Not going to happen. What would we learn that way?
Like you, we struggled when we moved up to .NET just over a year ago, and
eventually decided to bring in some professional help. This was one of the
best things we ever did! Not only did it show us how little we knew of what
we thought we knew, but also highlighted some extremely bad practices which
we were using.

The consultant had a suite of base classes based loosely on the Microsoft
Enterprise ones which she tailored a little to our specific needs and left
for us to use. Our development productivity went through the roof at that
point.
Actually, you have helped us in our recent struggle to decide whether
to continue development for MS products or move on to the growing body
of Linux converts.
I'd be interested to know how you intend to use Access databases in a Linux
environment when you say "Alternate DB formats are not an option".

DJ
Aug 5 '07 #5
"GregG" <Gr***@electron.comwrote in message
news:pu********************************@4ax.com...

Greg,

<snip>

OK - I think I now have a better understanding of what you're trying to
do...
The only problem I have is when converting to use the native Access
databases in lieu of the SQL development database, which contains many
tables in ONE database.
This is the problem - multiple DATABASES. Is it a kludge? Yes!
There is no way that I see that one can create a DAL comprised of
multiple databases, and create relational queries bases on multiple
tables from within these multiple databases.
I wonder if your problem could be solved by heterogeneous joins...?
Basically, these allow you to select from tables in more than one Jet
database within the same query.

The following Microsoft article: http://support.microsoft.com/kb/254130
details a fix for a bug which can occur when mixing Jet 4 and Jet 3
databases in the same query, but also includes a code snippet which shows
how to construct these joins in the actual SQL.

HTH
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

Aug 5 '07 #6
"GregG" <Gr***@electron.comwrote in message
news:d1********************************@4ax.com...
In Access, however, you can link external tables from innumerable foreign
databases into the current database and perform heterogeneous queries
Yes you can, but you don't *have* to... You can include tables from more
than one Jet database in the same query without actually having to actually
create linked tables i.e. your current collection of Jet database could stay
as they are without modification...
The new implementation of OLEDB which comes with .NET still uses a
connection string, stored in Web.config,
For the record, the connection string doesn't *have* to be stored in
web.config...
it's there, same as before. But it now includes a strong-typing layer
above that, and the ability to create predefined Get, Set, Fill
methods to make vastly different data models appear the same to the
BLL and ultimately the application layer. These definitions are
contained in the .XSD files, which are in XML format.
Yes, it creates the *ability* to do those things that you mention, but
doesn't *force* you to do any of them...
But! I can find no mechanism for connecting these disparate tables
into a homogenous unit in ASP.NET. I believe it was designed to
primarily integrate with SQLServer - which is admittedly a superior,
scalable solution.
OK, here's a method from my DAL which will return a DataSet given an OleDb
connection string and a piece of SQL:

using System.Data;
using System.Data.OleDb;

public abstract class COleDb
{
public static DataSet GetDataSet(string pstrConnectionString, string
pstrSQL)
{
try
{
using (OleDbConnection objOleDbConnection = new
OleDbConnection(pstrConnectionString))
{
objOleDbConnection.Open();
using (OleDbCommand objOleDbCommand = new
OleDbCommand(pstrSQL, objOleDbConnection))
{
using (OleDbDataAdapter objDA = new
OleDbDataAdapter(objOleDbCommand))
{
using (DataSet objDataSet = new DataSet())
{
objDA.Fill(objDataSet);
objOleDbConnection.Close();
return (objDataSet);
}
}
}
}
}
catch (OleDbException ex)
{
throw ex;
}
catch (Exception)
{
throw;
}
}
}
Then, supposing you needed to bind a GridView to a DataSet fetched from two
separate Jet databases without creating any linked tables in either of them,
you could do something like this:

string strConnection = ".......";
string strSQL = "SELECT Customers.* FROM Customers INNER JOIN
[Sales.mdb].Sales ON Customers.CustomerID = Sales.CustomerID"
MyGridView.DataSource = COleDb.GetDataSet(strConnection, strSQL);
MyGridView.DataBind();
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

Aug 5 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Sergio | last post: by
5 posts views Thread by Scott Tilton | last post: by
1 post views Thread by Jorge | last post: by
reply views Thread by CountDraculla | last post: by
1 post views Thread by David | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.