Problem with reading Access DB using OleDBDataReader

[Durango2008]

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]

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/

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.

 

[Cor Ligthert[MVP]]

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

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.

 

[Cowboy \(Gregory A. Beamer\)]

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.

It always creates a lock file, even if you are just reading. This is just
how Access works.

I am not familiar with this stuff so I did not know how you can pass a
ReadOnly parameter to the connection.

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.

 

[sloan]

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.

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.

 

[William Vaughn \(MVP\)]

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
____________________________________________________________________________________________

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.

 

[William Vaughn \(MVP\)]

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
____________________________________________________________________________________________

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.

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.

 

[sloan]

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.

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
____________________________________________________________________________________________

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.

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.

 

[Tony Toews [MVP]]

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.

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]

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\)]

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
____________________________________________________________________________________________

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.

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
____________________________________________________________________________________________

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.









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.

 

[Cor Ligthert[MVP]]

Bill,

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

Cor

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
____________________________________________________________________________________________

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.

 

[Larry Linson]

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

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\)]

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]

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]

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

Bill,

I had to smile reading your message.

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

"There can be situations that it is valid, but in most situations while if
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]

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

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

Due to crossposting, most posts are probably answers done from .Net
newsgroups.

Cor

 

[Paul Clement]

¤ 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)