.NET Framework - Problem with reading Access DB using OleDBDataReader

Asked By Durango2008 on 27-Jan-09 12:15 AM
Hello everyone,

I have a very hairy problem that I need immediate help in.  I have written a
simple web application which can upload and read in an Access mdb file,
retrieve its contents and populate a table in SQL server.
Here is where things get weird.
On my developement server it runs fine, no hiccups at all.
On my production server that the client uses it does not work.
All I get back is Server resources exceeded.
I looked this up on google and it explains that this can happen if too many
concurrent connection to an Access DB occur.
Now I am assuming that the server where things run fine allows more
connections to be left open than the one that the application breaks in.

Here is my code so you can get an idea of what I am doing.
I minimized the code for readability.

OleDbConnection accessConn =
new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + fileName);
OleDbDataReader OleDR_TblA = null, OleDR_TblB = null;
accessConn.Open();

OleDbCommand accessTblACmd = new OleDbCommand("select a, b, c from
[TblA] order by a", accessConn);
try
{
OleDR_TblA = accessTblACmd.ExecuteReader();
}
catch (Exception e)
{
msgStr = e.Message.ToString();
}


OleDbCommand accessTblBCmd  = null;

while(OleDR_TblA.Read())
{
string aVal = OleDR_TblA.GetValue(0);
string bVal = OleDR_TblA.GetValue(1);
string cVal = OleDR_TblA.GetValue(2);

string d = a + "-" + b + "-" + c;

accessTblBCmd  = new OleDbCommand("Select * from [TblB]
where id=" + d, accessConn);
try
{
OleDR_TblB = accessTblBCmd.ExecuteReader();
}
catch(){ Exception }
if (OleDR_TblB.HasRows)
{
OleDR_TblB.Read();
// Read the data
// Insert into SQL Server
}
}// end of while loop

// close all connections
OleDR_TblA.Close();
OleDR_TblB.Close();
accessConn.Close();
...etc.

As you can see I need to read one table to create a sql statement to read
the main data from a different table hence the way it's been coded.
Now again this works but not on the server that I need to put the web
application on.
I am a bit wet behind the ears with this stuff so excuse my ignorance, but I
am not sure what settings are different on the 2 servers that allows one to
work and not the other.
I am also curious on why the MS-Access file would create a lock if it is
only being read from and not written to.
I am not familiar with this stuff so I did not know how you can pass a
ReadOnly parameter to the connection.
Please give any criticism of my code, I know it's not good and I am happy to
hear any advice.

Thanks in advance,
Durnago.




Miha Markic replied on 27-Jan-09 02:13 AM
Try using a second connection instance for the other reader.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
Cor Ligthert[MVP] replied on 27-Jan-09 09:10 AM
Durango,

You write that you have minimized the code. Especially in this kind of
problems, there can be something where you yourself thinks, "I have set that
in the wrong place". Therefore the now supplied code says almost nothing. By
instance we cannot see what the method is where this code is in, and how
many time this method is processed. Keep in mind that a webapplication is a
kind of multitier (thread) application in itself for all the pages that are
posted.

Cor
Cowboy \(Gregory A. Beamer\) replied on 27-Jan-09 11:19 AM
It always creates a lock file, even if you are just reading. This is just
how Access works.


With Access, the ReadOnly parameter is less important, in many ways, than
the nature of the database. By default, an Access DB is one user. You can
set the database up as multi-user and it is advised for web access.

If possible, I would try to get all of this into SQL Server, even if it is
just Express, as Access creates far more headaches than it is worth, IMO at
least.

--
Gregory A. Beamer
MVP; MCP: +I, Se, SD, DBA

Blog:
http://feeds.feedburner.com/GregoryBeamer

*************************************************
*************************************************
sloan replied on 27-Jan-09 11:23 AM
I've done this on several projects, and the way I handle it is like this:


Get an IDataReader on the Access DataSource.
Loop on the IDataReader.
Populate a strong dataset with the Access Data.
After X Number of Rows, (1000?), send the DataSet.GetXml down to a sql
server stored procedure.
(see http://support.microsoft.com/kb/315968 for a rough sketch)
After the 1000 go in , clear the DataSet, and keep going until the
IDataReader has no more rows.

One connection for the IDataReader for the Access (source)
A second connection for the sql server stored procedure call.

............

If you take the time and put the effort in, the bulk insert will save you
alot of time, instead of doing if row by row.



Actually, I took a small example I wrote and put it here:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!527.entry
You can download the code and see for yourself.
William Vaughn \(MVP\) replied on 27-Jan-09 01:41 PM
Ah, we've talked about this a number of times. I expect that a significant
part of your problem is that the JET/Access database engine was never
intended for use on web sites. It's a single-user shared-file DBMS engine.
In an ordinary small-office implementation, several individuals can share
the database file but each uses his own instance of the JET engine to access
it. In a web application you'll have any number of "users" trying to access
the same instance of JET at the same time--something it's not equipped to
handle.

There are several alternatives to JET including SQL Server--even the Express
(but not the Compact) Edition. It's designed as a database "service" that
can handle a few to a few thousand simultaneous tasks.


--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205  (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com  http://betav.com/blog/billva
____________________________________________________________________________________________
William Vaughn \(MVP\) replied on 27-Jan-09 01:49 PM
If the underlying task is to copy data from an Access database to SQL
Server, using ADO.NET to do it is... well, challenged. I suggest
investigating SqlBulkCopy which can import data from virtually any data
source and pass it to SQL Server very quickly--probably several orders of
magnitude faster than an ADO query.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205  (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com  http://betav.com/blog/billva
____________________________________________________________________________________________
sloan replied on 27-Jan-09 02:02 PM
I mentioned that there are other viable methods.

I picked mine simply because of a few things:
the need to run some business rules
the need to exclude some data
the need to massage some of the data



If you're after speed, then my method is definately not the way to go.

..

However, the concept of "bulk insert" via xml is still a better option than
row by row.
Tony Toews [MVP] replied on 27-Jan-09 02:04 PM
That's slightly misleading.   I've had 25 users in an app with 160 tables.  There are
reliable reports of 75 to 100 users using Access databases.    Granted most of those
should likely have been upsized to SQL Server.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
sloan replied on 27-Jan-09 02:22 PM
I would also add that having it on a single website is maybe the best way to
use it.

With a winform application, and a file sitting on the server, you have to
drag the tables across the network and allow the (local) jet files do the
work.

With a website, the jet database is sitting right there on the machine.

..

If you can "get in, get out" really quickly, then a website jet database
isn't horrible for small needs.
Because the files for processing the data are at the same place the mdb file
resides.....it's better than dragging it across the network.


I'm not advocating for it.  But in certain situations, it can work.  If its
alot of "readonly" data, so much the better.
Of course it is NOT a true RDBMS, but it can be an effective small
datastore.


And if you think you'll ever move up to something grander, there is a way to
code for jet (now) but not get uber screwed when you move to something
grander later down the road.  Here is my take on that situation:
Multiple RDBMS Support and the Factory Design Pattern
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!176.entry

.............


However, I remember back with Access 2.0 (16bit) where the documentation
said "Up to 255 Users".
Yeah right.
I found some documentation later that said "255 is the theoretical limit,
10-12 users is more realistic".
I don't remember what year I read that, I just remember going "That would
have been nice to know before banking on some crapping mdb file for
concurrent use".

...........
Live and learn!
William Vaughn \(MVP\) replied on 27-Jan-09 06:05 PM
All good reasons... but all of these operations can be done on the server.
Generally, one moves the rows to the server to a temporary holding table and
runs a SP to filter, exclude or apply other logic that then merges the rows
with the base tables.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205  (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com  http://betav.com/blog/billva
____________________________________________________________________________________________
Cor Ligthert[MVP] replied on 28-Jan-09 02:32 AM
Bill,

In my idea "Multi-user". That is the way I have always used it in past (it a
while ago)

Cor
Larry Linson replied on 28-Jan-09 03:35 PM
Hi, Bill -- you're sounding like aaron kempf, again.

It would be easy to draw the implication from your statement that the other
databases you go on to mention were "intended for use on web sites". But, to
be truthful, wouldn't you have to admit that most server databases also were
not, at least initially, "intended for use on web sites", as they were first
created before the web was implemented or was, at best, in its infancy?

Many large server databases were designed to support many simultaneous
users, and it is a happy coinicidence that that architecture lends itself
well to supporting high-traffic web applications, just as it lends itself
well to supporting high-traffic LAN/WAN applications.

But, in fact, for low-traffic web applications, Jet databases have served
well on web sites, just as they have served well in relatively low-traffic
multiuser environments. And, in fact, a Jet database on a web site will
often satisfactorily serve a somewhat larger audience than one with the same
tables on a local area network. (I see no reason not to assume that ACE
databases will similarly serve well for appropriate environments.)

There are a number of server databases, also, that were not designed for
high volume. They, too, have served well on low-traffic website use... one
that comes to mind is the older, smaller Sybase SQL Anywhere product.

It's simply a matter of choosing the appropriate tool for the application at
hand.

Larry Linson
Microsoft Office Access MVP
William Vaughn \(MVP\) replied on 28-Jan-09 04:09 PM
Granted, some have implemented web sites with JET and some seem to be
working. But it's like making a bridge out of balsa wood. While if done
correctly, it can hold up under some bike traffic, once the strain gets
beyond a point (as success usually does), the bridge fails. I hesitate to
encourage folks to use JET as a starting point because Microsoft seems to be
abandoning it (there is no 64-bit support, nor anyone working on it) and as
a building block it lacks the scalability and security that many of them
eventually need. Yes, the right tool for the right job, but since there are
more scalable, more suitable tools, why not use them?

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205  (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com  http://betav.com/blog/billva
____________________________________________________________________________________________
sloan replied on 28-Jan-09 04:21 PM
The only concrete reason I can give for a Jet database is this one:

My hosting company will let me create a billion Jet databases on the server
(for "free" only limited by my total disk space).
I have to pay for Sql Server (non express) databases.
They are not currently offering "free" hosting of express (sql server)
databases.

......

That is one reason I developed my "Multiple RDBMS" example.  I have the same
source code, but I can deploy it against a Sql Server Database (for big
customers with alot of traffic) and pay my hosting provider for a Sql Server
database....OR.....for small clients or demos, I can swap out to Jet.

http://sholliday.spaces.live.com/blog/cns!A68482B9628A842A!176.entry
is where I have the example.
sloan replied on 28-Jan-09 04:28 PM
But I agree, if my particular host offered (for example) Sql Server Express
databases for "free", it would be foolish to use a Jet database at that
point.
Cor Ligthert[MVP] replied on 29-Jan-09 02:28 AM
Bill,

I had to smile reading your message.

Your statement "It's like making a bridge of balsa wood" was missing one
small sentence.

don correctly......"

I assume that you understand what I mean (I.o.w. Making a bridge from
Titanium does not result forever in a better bridge), it would have made
your message even more true.

Cor
The Frog replied on 03-Feb-09 01:01 AM
Hi Guys,

Long time no speaky :-)

Why not just be done with the whole JET / SQL Server thing for
websites - if you have the opportunity to build from scratch as seems
the case - and just do the thing in Java using JavaD? Its free,
scalable, embeddable, and can be made pretty much as secure as you
want it to be. Java also seems to lend itself well to web based
applications / scenarios, and hosting a Java EE application in Tomcat
or some other similar container is pretty straight forward.

Just my 2cents

Cheers

The Frog

PS: Jet rules! I love it.
Cor Ligthert[MVP] replied on 29-Jan-09 04:22 AM
Due to crossposting, most posts are probably answers done from .Net
newsgroups.

Cor
Paul Clement replied on 29-Jan-09 08:54 AM
¤ Hello everyone,
¤
¤ I have a very hairy problem that I need immediate help in.  I have written a
¤ simple web application which can upload and read in an Access mdb file,
¤ retrieve its contents and populate a table in SQL server.
¤ Here is where things get weird.
¤ On my developement server it runs fine, no hiccups at all.
¤ On my production server that the client uses it does not work.
¤ All I get back is Server resources exceeded.

Couple of suggestions, first make certain to periodically compact the database file. That could be
causing the error you are referring to.

Also, make certain that the account that your web application is running under has full permissions
to the folder where the database is located. I can't tell you what account that would be without
knowing what type of authentication your app is configured for or whether it has been enabled for
impersonation.

There is a corresponding .LDB file that is created, updated and deleted automatically when the .MDB
file is opened and closed for write access and that is why full permissions for the authenticated
user of the app is required on the database folder.


Paul
~~~~
Microsoft MVP (Visual Basic)
Larry Linson replied on 29-Jan-09 02:52 PM
First, one correction: _many_ have implemented web sites or web applications
with Jet and _many_ of them _are_ (not "seem to be") working; many are
working quite well, in fact.  You don't bolster your argument with
unfounded, condescending dismissals.

Actually, it's more like building a bridge out of cypress or oak. You
wouldn't want to do that on an Interstate, but there are some around that
have been serviceable for a long, long time, for modest traffic of cars,
trucks, and lesser vehicles.


Hmm. Sounds as if you are part of the
everything-is-going-to-grow-to-an-enterprise-application crowd, Bill. That's
an implicit assumption of many who argue that only tools suitable for
enterprise applications are suitable for any application.

I, on the other hand, hesitate to encourage people who aren't necessarily
professional programmers or databasers that they should _start_ with
something that requires them to become professionals, on the off-chance that
their application or website will grow to need it. Typically, they aren't
going to change professions, and, also typically, they can't afford to hire
a Bill Vaughn to create one for them (at the point that they need a

I've been in this business for a good many years, and I've never seen a
small application grow to be an enterprise application simply by "scaling".
Every one I've seen that made such a trip was rewritten completely at least
once.  As far as I can see, "scalability" is almost always a bogus argument.
And, as for security, most of the sites that would use Jet wouldn't have
information of much worth to a cracker; but, highly secure sites are
penetrated, hacked, cracked every day; there's no absolute security.

If the original poster were asking about building something that would be
and Oracle) would be appropriate -- because almost certainly something of
that scope would need them, and almost certainly, the only one who would
seriously undertake that would be a programming and database professional
(more likely a team of those).

On the other hand, it seems likely that anyone who would consider Jet/ACE
does not have a reasonable expectation that their site will grow, without
alteration, to be "another Amazon", and quite possibly, is neither a
programming nor database professional. One of the great advantages of
Access/Jet/ACE is that it allows the non-professional to create usable,
stable, solid database applications. There are many, despite condescending
digs by some in the "professional" community. There are also some failures,
but there are also a lot of failures of enterprise-application-scale
projects, as well.  So, while it may be appropriate to warn that Jet will
have its limits, a blanket recommendation against Jet (or ACE, remember, now
that it is released) is unwarranted. (That's why I said, yours reads like a
post by aaron kempf.)

Your guess may be correct, that Jet is on the way out, in time.  It may be
that ACE, too, is on the way out, but the mere fact that Microsoft made
significant investment in creating this descendant of Jet for Office 2007
indicates to me that it will not be on the way out any time in the near
future.

Larry Linson
Microsoft Office Access MVP
Tony Toews [MVP] replied on 30-Jan-09 11:52 PM
The Access product group is happily working with Jet's successor ACE, whatever that
acronym stands for.    Mind you, they've added a number of features which most
experienced folks, including myself, don't care for.   Such as multi valued fields.
Ah, well, billable time.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Larry Linson replied on 31-Jan-09 05:25 PM
It's my elder grandson's nickname.

He doesn't mind the Access Product Group naming the new database engine
after him. I suspect he doesn't even expect to be paid royalties. <GRIN>

Larry