Multiple Access Databases

[GregG]

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.

 

[Mark Rae [MVP]]

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/29EF3A4F-A0C2-4BB2-A215-8F87F100A9F9.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.

 

[GregG]

Mark Rae [MVP] said:

Don't do any of that - use a DAL instead, e.g.
http://dotnetjunkies.com/Article/29EF3A4F-A0C2-4BB2-A215-8F87F100A9F9.dcik


Upsize the individual Jet databases into a single SQL Server (Express)
database.

Thanks for the response, Mark.
Perhaps I misunderstand, but I cannot upsize into anything.

This is for live, read-only queries on an accounting system which
uses Access DBs. It's old, it's outdated, and yet 500+ dealers across
the US use it for their accounting. Nothing about the data sources can
be changed. I'm stuck with Access MDBs.

Additionally, I was under the impression that SQL Express was for
development use only - a replacement for the old desktop development
thingy...

As for the Data Access Layer, a cursory exam of your link reveals ever
more cryptic acronyms I am unfamiliar with. And having just begun to
use .NET, I've just about OD'd on redundant, duplicative objects and
obscure cryptic documentation. I've managed to write a
graphing/charting engine which returns an async image stream via an
ASHX file, a custom DataGridView control, etc., but the database thing
remains a quandary.

Thanks,


Greg G.

 

[Mark Rae [MVP]]

Perhaps I misunderstand, but I cannot upsize into anything.

This is for live, read-only queries on an accounting system which
uses Access DBs. It's old, it's outdated, and yet 500+ dealers across
the US use it for their accounting. Nothing about the data sources can
be changed. I'm stuck with Access MDBs.

Oh right - so this isn't a web app on the live Internet...?

Additionally, I was under the impression that SQL Express was for
development use only - a replacement for the old desktop development
thingy...

Nope: http://www.microsoft.com/sql/editions/express/default.mspx

As for the Data Access Layer, a cursory exam of your link reveals ever
more cryptic acronyms I am unfamiliar with. And having just begun to
use .NET, I've just about OD'd on redundant, duplicative objects and
obscure cryptic documentation. I've managed to write a
graphing/charting engine which returns an async image stream via an
ASHX file, a custom DataGridView control, etc., but the database thing
remains a quandary.

Hmm - in which case, if you're being asked to support a system being used by
500+ users, I'd respectfully suggest you get some training, otherwise you
might find yourself in some real trouble when this (almost inevitably) goes
wrong...

 

[GregG]

Mark Rae [MVP] said:

Oh right - so this isn't a web app on the live Internet...?

Simply, it is for the Intranet. It is to allow route salesmen to
query their performance history via a browser on their laptops/PDAs.
It has to extrapolate TotalSales, GP%, etc from the GL, history and
customers tables. No writing to the tables, just read-only select
queries which are used to produce a graph and summary panel via HTTP.

Nope: http://www.microsoft.com/sql/editions/express/default.mspx

We already use SQL Server 2000 here.
But I don't believe this is truly relevent...

Hmm - in which case, if you're being asked to support a system being used by
500+ users, I'd respectfully suggest you get some training, otherwise you
might find yourself in some real trouble when this (almost inevitably) goes
wrong...

I've been producing reports, automated emailers, desktop apps and
online ordering web apps for this system for 8+ years with C++, Access
and VBA, and Classic ASP/ADO/COM. Written system services for
2000/2003, hardware device drivers in ASM/C++, etc. Saw the need and
wrote an AJAX-like web engine in 1998 which is still in operation.
Haven't had a problem yet. What I am proposing works fine with these
"older" technologies. FWIW, we are also MS Partners.

We eventually decided to go the .NET route due to the implied ease and
wealth of web server / client side controls development. While a bit
of a slog for someone mired in a history of non-overload, lightly
object oriented, single-threaded worlds, I've managed most of it.
While originally repulsed by the non-compliance with W3C standards due
to issues such as the tag pairs <table> </TABLE> being generated by
certain data controls, much seems to have been smoothed over in the
past year. For me, the DOCs still blow, and many seem to be written by
someone not particularly fluent in English.

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.

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.

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.
We already have much time invested in conversion of other ASP/COM
components. Alternate DB formats are not an option, nor is formal
training in yet another temporal layer of proprietary abstraction.

Thanks,
Greg


Greg G.

 

[Mark Rae [MVP]]

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-R...2808633?ie=UTF8&s=books&qid=1186300677&sr=8-1
http://www.amazon.com/ASP-NET-Every...2808633?ie=UTF8&s=books&qid=1186300677&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...

 

[GregG]

Mark Rae [MVP] said:

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-R...2808633?ie=UTF8&s=books&qid=1186300677&sr=8-1
http://www.amazon.com/ASP-NET-Every...2808633?ie=UTF8&s=books&qid=1186300677&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.

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.

 

[David Jackson]

Hi Greg,

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

 

[GregG]

David Jackson said:

Hi Greg,

Hi David,

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.

This may be true for TVGuide, but for systems with a user base of 30
people, hardly necessary. Remember, this is 500 individual, unrelated
dealers with approximately 30 users each. Existing software (albeit
ancient), existing servers, existing accounting practices. They are
not going to abandon the use of what they have when it works, is paid
for, and they know how to use it. They could care less about the
technology involved, and in fact, the existing code was based upon a
system written for Unix over 20 years ago. It doesn't even possess a
Windows GUI - it was/is terminal based. The thought of upgrading
anything frightens the heck out of them - with good reason.

They are America's few remaining small businesses struggling against
the BORG of BigBox stores.

I make a living augmenting this kludge of a system with reports,
online ordering, etc. Previous work has been with ASP.
A few web sites have been tried with .NET. Additionally, they don't
like the complexity - for them it's like swatting flies with a
jackhammer.

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.

As I poorly explained in the original post, I already have DALs, BLL,
etc. in place for the development of this thing. Speed isn't an
issue, nor is scaleability, server loading, excessive security, or
public appeal. It is for internal use. What matters is cost,
simplicity, and support for their obsolete accounting systems.

This thing generates 100 or so different ad hoc queries which present
reports and charts to the user via HTTP. My explanation was
intentionally simplistic so as to not obfuscate the relevant issue
with needless details.

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.

The native data format is multiple databases, each with 12 or so
tables. IE:

Customers DB
Main Data Table
Supplementary Data Table
More Data Table
...

Sales History DB
Main Data Table
Supplementary Data Table
More Data Table
...

SalesPeople DB
Main Data Table
Supplementary Data Table
More Data Table
...
etc...

NOT

Accounting DB
CustomersTable
SalesHistoryTable
SalesPeopleTable


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.

Even if there were some third party panacea for this issue, we will
not farm it out to a third party - period.

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".

Linux adapters for Access MBDs are not uncommon, one quick example
being UnixODBC RPM. http://edas.visaci.cz/en/unixodbc/

Works fine with Pearl and PHP.

Thanks,


Greg G.

 

[Mark Rae [MVP]]

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

 

[GregG]

Mark Rae [MVP] said:

Greg,

<snip>

OK - I think I now have a better understanding of what you're trying to
do...

Whew!... ;-)

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.

Mark, you're on the right track. I've been writing SQL queries by
hand for years, so the actual syntax isn't a problem. In Access,
however, you can link external tables from innumerable foreign
databases into the current database and perform heterogeneous queries
with no special tricks - whether from Access/VBA, OLEDB, ADO or DAO.
It's just a set of pointers and a connection string, same as the
ASP.NET DALs do behind the covers.

The new implementation of OLEDB which comes with .NET still uses a
connection string, stored in Web.config, with an almost identical
syntax to the old OLEDB stuff. Whether SQL or Jet MDBs, or Oracle,
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.

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. Alas, I do not now have the option of using it or
MySQL.

One solution which works is to create a local Access database in
APP_DATA, create within it links to the external tables, and then
building the DAL against that local database. But I believe this
forces data access to navigate not only the .NET interop layer, but an
additional path through COM. I can't be sure, as I didn't write .NET,
but logic dictates that it is so.

What I am seeking may not be possible, and it's looking like that is
the case. My workaround functions (for now), but it bothers me because
it grants access through undocumented behaviours which may or may not
work in the future. And considering that it thrashes some 200,000
records, depending on the query type, efficiency is a consideration.

Thanks Again,

Greg G.

 

[Mark Rae [MVP]]

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();

 

[GregG]

Mark Rae [MVP] said:

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...

This is true but have never had to do this...

For the record, the connection string doesn't *have* to be stored in
web.config...

This is also true, but it makes for 'predictable' configuration...

Yes, it creates the *ability* to do those things that you mention, but
doesn't *force* you to do any of them...

This is true as well - in fact I'm sure many DALs omit these
abilities, particularly during development.

OK, here's a method from my DAL which will return a DataSet given an OleDb
connection string and a piece of SQL:

string strSQL = "SELECT Customers.* FROM Customers INNER JOIN
[Sales.mdb].Sales ON Customers.CustomerID = Sales.CustomerID"

This is the meat. folks.

I have never before had to configure an SQL query like this, but
darned if it doesn't work - assuming you use the proper path...
I'm assuming it used the default connection string to configure the
adapter, substituting the filename in Jet. It also works in Access 2k.
Never had the need to do this, and I'm surprised to have never run
across this before.

I now have a variety of table adapters configured in my DAL, and it
works as intended. Funny thing is, on the complex pivot table query I
tested it with, it runs faster with the linked tables in new Access
database method than with this more integrated approach. Could be due
to caching however - it's not a reliable benchmark till I time each
after a reboot.

Your previous pointer to the KB article pointed this out, but since no
one was here at 4:00am shining a big spotlight on the relevant
portion, I missed it the first time...

Mark, Thanks for the pointer.
The past 7 years should amply demonstrate that we Yanks are a little
slow... ;-)



Greg G.

 

[Mark Rae [MVP]]

I have never before had to configure an SQL query like this, but
darned if it doesn't work - assuming you use the proper path...

Indeed, but getting the right path would apply to any file-based RDBMS...
;-)

I'm assuming it used the default connection string to configure the
adapter, substituting the filename in Jet. It also works in Access 2k.

Yes, that's correct, although things start to get very complicated very
quickly when the linked database uses different security from the one
specified in the connection string...

Never had the need to do this, and I'm surprised to have never run
across this before.
OK.

I now have a variety of table adapters configured in my DAL, and it
works as intended.
Excellent.

Funny thing is, on the complex pivot table query I
tested it with, it runs faster with the linked tables in new Access
database method than with this more integrated approach. Could be due
to caching however - it's not a reliable benchmark till I time each
after a reboot.

In fact, the difference is minimal unless you have very many foreign
databases. When you link an external data source into a Jet database, it
also stores a certain amount of metadata about that data source such as the
path and some high-level schema information which needs to be passed to the
Jet engine for parsing the query. With the raw SQL method, this has to be
fetched every time, so it's marginally slower, and gets slower and slower as
you added more foreign databases into the SQL string...

Your previous pointer to the KB article pointed this out, but since no
one was here at 4:00am shining a big spotlight on the relevant
portion, I missed it the first time...

We've all been there!

Mark, Thanks for the pointer.

All part of the service...