Speed problems when accessing large MS Access database

  • Thread starter Thread starter Patrick Fryer via .NET 247
  • Start date Start date
P

Patrick Fryer via .NET 247

I?m doing some work on a project which involves accessing a largeAccess database (about 850Mb)

The problem is when I?m doing an update/insert into the databasethe oleDbCommand.executeNonQuery() method seems to return beforeAccess has committed the change ? This means that a display Ihave on screen which reads back what has just been ?inserted?doesn?t work correctly.

At first the problem was only intermittent but as the databasehas grown it seems to happen all the time. It does work okay ifI step through the program in Visual Studio (thus introducing anartificial delay). Does anyone know a way around this? Somewayfor the code to block until the operation has been committed? Ican?t seem to find info about this online. If I make the threadsleep for 500 MS after performing the insert it's okay as well?

Is there a connection string parameter or something I can use?Our customer wants to stick with MS Access for now although theywant to upgrade to SQL Server later.

Thanks. Patrick.
 
Suggest MSDE to your customer. It's free, and Access offers ABSOLUTELY NO
advantage over MSDE.

Yes it is FREE as in F-R-E-E !!

If your customer still insists on keeping access and yet getting the
performance, tell him "Hey I wanna play for the redsox too, but it ain't
gonna happen !!".
If he still insists, then compact the database, rearchitect the structure
and seperate your data into multiple access files - and oh make a sad face
as you do it.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/



I?m doing some work on a project which involves accessing a large Access
database (about 850Mb)

The problem is when I?m doing an update/insert into the database the
oleDbCommand.executeNonQuery() method seems to return before Access has
committed the change ? This means that a display I have on screen which
reads back what has just been ?inserted? doesn?t work correctly.

At first the problem was only intermittent but as the database has grown it
seems to happen all the time. It does work okay if I step through the
program in Visual Studio (thus introducing an artificial delay). Does anyone
know a way around this? Someway for the code to block until the operation
has been committed? I can?t seem to find info about this online. If I make
the thread sleep for 500 MS after performing the insert it's okay as well?

Is there a connection string parameter or something I can use? Our customer
wants to stick with MS Access for now although they want to upgrade to SQL
Server later.

Thanks. Patrick.
 
MSDE is SQL Server. You'd be surprised at the number of organizations
that go through hell to get it installed for use with desktop applications.
Almost every company that has tried to install this or use it has had
problems during the install. They beg us to go back to Access.

Believe me, this is a growing trend for large corporate customers. We've
even had the Microsoft folks themselves come in and try to resolve
these problems. They couldn't either. The dirty little secret is that the
people at Microsoft are afraid of MSDE choking...
 
Allright I just had to reply to this.

I have deployed an MSDE based install to a company where we had to "push"
the updates via a "pull" mechanism to something as low as a dialup.
This deployment was done to over 600 offices throughout the united states.
What was doing our updates? Well the program itself, over guess what - a
remoting connection, because clickonce etc. wasn't there.

And we migrated from MS Access to MSDE.

And we never had any problems.

Not to mention, SQL2005 will alleviate the install problem too - and that
isn't really too far off.

MSDE is a safe bet IMO. I have used it firsthand.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/



Robbe Morris said:
MSDE is SQL Server. You'd be surprised at the number of organizations
that go through hell to get it installed for use with desktop
applications.
Almost every company that has tried to install this or use it has had
problems during the install. They beg us to go back to Access.

Believe me, this is a growing trend for large corporate customers. We've
even had the Microsoft folks themselves come in and try to resolve
these problems. They couldn't either. The dirty little secret is that
the
people at Microsoft are afraid of MSDE choking...

--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
Click to expand...
 
¤ Allright I just had to reply to this.
¤
¤ I have deployed an MSDE based install to a company where we had to "push"
¤ the updates via a "pull" mechanism to something as low as a dialup.
¤ This deployment was done to over 600 offices throughout the united states.
¤ What was doing our updates? Well the program itself, over guess what - a
¤ remoting connection, because clickonce etc. wasn't there.
¤
¤ And we migrated from MS Access to MSDE.
¤
¤ And we never had any problems.
¤
¤ Not to mention, SQL2005 will alleviate the install problem too - and that
¤ isn't really too far off.
¤
¤ MSDE is a safe bet IMO. I have used it firsthand.
¤

Yeah, but MSDE deployment is messy as Robbe stated. Access doesn't have this problem and the
database is extremely portable and self contained. SQL Server 2005 Express should be a more viable
replacement.

In addition MSDE is throttled at five concurrent users. While it's more architecturally sound than
Access and actually has a database management system, Access does not have a built-in concurrent
usage limit.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top