472,363 Members | 1,935 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,363 software developers and data experts.

Access 2003 and SQL Server 2000 record locks (long)

Hello everybody,

I've got a bit of a situation that I could use some guidance with if
possible. I work for an auditing firm where my users audit electronic
shipping data for customers to see if they've been overcharged for
shipments by truck and rail carriers.

99.9% of the time, one of our auditors needs to see all data sent by a
customer in datasheet view so that they can sort records in many
different ways looking for duplication of bill payments over a
specific time period. This might involve as little as 100 records for
a small client or 200,000 or more records for a larger client. I know
this sounds ludicrous to have that many records open at one time in a
form, but you would not believe what our auditors have found at times
and thus have recovered many thousands of dollars for our clients just
because they can see the whole picture of charges in one screen full
of data. The nature of our business basically violates every database
rule there is about only retrieving small datasets and disconnecting
the recordset.

The problem I'm having at this using Access 97, 2000 or 2003 as the
frontend and SQL Server 2000 as the backend...record locks that
escalate into full table locks / deadlocks if a user doesn't goto the
end of a recordset with a query/form open so that Access releases the
locks. Sorting of a recordset in a form causes entire the entire
query to be re-run thus pulling the entire recordset back over the
network to the machine and then creating major locking problems when
multiple users are sorting and updating rows with data to print
claims.

Yes, I've been using bound forms for a very long time and have enjoyed
the freedom of not having to marshal connections and record changes,
but that freedom is coming to an end I believe, according to what you
guys are going to say. I can see the writing on the wall about this,
but need some guidance of how to make my app perform better.

I have tried the following to fix the record locking and performance
problems using Access 2003:

1. Using an .MDB, create a client-side ADO recordset from a query,
disconnecting the recordset from the database and then assigning
the .Recordset property of the form to this ADO recordset.
Performance was horrible even with just 10,000 records. There were
other concurrency issues to that I never got around also when trying
to update the ADO recordset and flushing the data back to the
database.

2. Tried using an ADP with stored procedures but the OLEDB driver
just goes nuts and eats all of the RAM in the machine when working
with large recordsets. I don't think an ADP will be the way I need to
go because the users change queries all the time looking for anomolies
in the data to file a claim against. I know that the auditors don't
want to learn SQL to change stored procs!

I've thought of trying the following, what do you all think?

1. Create a local table that contains all of the fields that the
auditor will need to do their thing.
2. Execute an INSERT into this local table from the SQL server with
an append query.
3. Base the auditing form on this local table so that the performance
is good and write changes back to the database with a stored proc call
passing the fields that were changed.

If there's some other way to do this with some sort of disconnected
recordset and binding that recordset to a form, I'm ALL EARS!

My flame suit is on for using bound forms, but they've worked like a
charm in the past before moving up to SQL server because we needed
much more database space than an Access .MDB could hold!

Thanks,
Jim
Nov 29 '07 #1
6 2572
Hi Jim,

(disclaimer incase some decides to flame me for making the following
statements, but here are the facts)

Access is a micro database system and will function effectively for
micro operations -- even on the web. But for large scale operations
like yours a micro rdbms just isn't going to cut it as you are seeing.
You already have a macro back end (sql server). So you need to step up
to a macro system to overcome your issues. That would be a .Net front
end which can take advantage of ADO.Net.

ADO.Net was specifically designed to handle concurrency/deadlocking
issues and works like a champ. Performance is phenomenal because .Net
has reduced I/O to the minimum required amount. Classic ADO writes to
the disk for every operation (but still better than ODBC which has teeny
bandwidth compared to ADO and keeps a constant connection open). When
you pull data with classic ADO - it goes to a table on the disk.
ADO.Net pulls data into tables in memory. This is where you get
significant performance. And another improvement is that .Net has
reduce data looping to the minimum required (like for arrays). With
ADO.Net you Fill a dataset - no looping required. And, of course, you
have way more bandwidth.

I have users that need to scroll through hundreds of thousands of
records also. I feel your pain :). I still love Access for the kinds
of operations it was designed for. But for large scale stuff - nothing
beats .Net (unless you need opensource - then Java - but .Net still has
more performance than Java).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Nov 29 '07 #2
On Nov 29, 2:08 pm, Rich P <rpng...@aol.comwrote:
Hi Jim,

(disclaimer incase some decides to flame me for making the following
statements, but here are the facts)

Access is a micro database system and will function effectively for
micro operations -- even on the web. But for large scale operations
like yours a micro rdbms just isn't going to cut it as you are seeing.
You already have a macro back end (sql server). So you need to step up
to a macro system to overcome your issues. That would be a .Net front
end which can take advantage of ADO.Net.

ADO.Net was specifically designed to handle concurrency/deadlocking
issues and works like a champ. Performance is phenomenal because .Net
has reduced I/O to the minimum required amount. Classic ADO writes to
the disk for every operation (but still better than ODBC which has teeny
bandwidth compared to ADO and keeps a constant connection open). When
you pull data with classic ADO - it goes to a table on the disk.
ADO.Net pulls data into tables in memory. This is where you get
significant performance. And another improvement is that .Net has
reduce data looping to the minimum required (like for arrays). With
ADO.Net you Fill a dataset - no looping required. And, of course, you
have way more bandwidth.

I have users that need to scroll through hundreds of thousands of
records also. I feel your pain :). I still love Access for the kinds
of operations it was designed for. But for large scale stuff - nothing
beats .Net (unless you need opensource - then Java - but .Net still has
more performance than Java).

Rich

*** Sent via Developersdexhttp://www.developersdex.com***
Hey Rich,

Yes I do see what you mean by using a BB-gun where a .300 magnum is
required! On .net forms where your users load up 100's of thousands
of records, do you use the normal grid controls that come with VS.NET
or have you bought some 3rd party controls that deal with the volume
better? I tried messing with a normal grid control this morning and
the performance was not very stellar. I'm new to VS.NET and need any
and all guidance that anyone can give while moving to a larger
toolset.

Thanks,
Jim
Nov 29 '07 #3
Hi Jim,

I use the standard datagridview that comes with VS2005. Here is the
catch though, VS2005 works best with a 2.8 gig processor or higher and
at least 2 gigs of memory. I have had hardware issues at my place for
people that are still using 1.8 gig processors and 1 gig of mem. The
memory at 1 gig isn't actually that bad (if they aren't loading hundreds
of thousands of records), but the 1.8 gig processor does hurt a little
bit. But if you are going to load 200,000 records into a table -
remember that in .Net it is all in memory. So 2 gigs definitley better
than 1 gig.

.Net is fairly resource intensive, but if your hardware has enough
horsepower, you won't notice it (big IF -- I know).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Nov 29 '07 #4
On Nov 29, 3:11 pm, Rich P <rpng...@aol.comwrote:
Hi Jim,

I use the standard datagridview that comes with VS2005. Here is the
catch though, VS2005 works best with a 2.8 gig processor or higher and
at least 2 gigs of memory. I have had hardware issues at my place for
people that are still using 1.8 gig processors and 1 gig of mem. The
memory at 1 gig isn't actually that bad (if they aren't loading hundreds
of thousands of records), but the 1.8 gig processor does hurt a little
bit. But if you are going to load 200,000 records into a table -
remember that in .Net it is all in memory. So 2 gigs definitley better
than 1 gig.

.Net is fairly resource intensive, but if your hardware has enough
horsepower, you won't notice it (big IF -- I know).

Rich

*** Sent via Developersdexhttp://www.developersdex.com***
Thanks Rich, now it's off to the store for some training material on
VS.NET 2005!

Jim
Nov 29 '07 #5
You will love it. I still deal with Access because I support hordes of
people who are still using Access, but for enterprise operations - .Net
all the way.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Nov 29 '07 #6
On Nov 29, 4:15 pm, jsac...@comcast.net wrote:
On Nov 29, 3:11 pm, Rich P <rpng...@aol.comwrote:
Hi Jim,
I use the standard datagridview that comes with VS2005. Here is the
catch though, VS2005 works best with a 2.8 gig processor or higher and
at least 2 gigs of memory. I have had hardware issues at my place for
people that are still using 1.8 gig processors and 1 gig of mem. The
memory at 1 gig isn't actually that bad (if they aren't loading hundreds
of thousands of records), but the 1.8 gig processor does hurt a little
bit. But if you are going to load 200,000 records into a table -
remember that in .Net it is all in memory. So 2 gigs definitley better
than 1 gig.
.Net is fairly resource intensive, but if your hardware has enough
horsepower, you won't notice it (big IF -- I know).
Rich
*** Sent via Developersdexhttp://www.developersdex.com***

Thanks Rich, now it's off to the store for some training material on
VS.NET 2005!

Jim
Great!

Have you researched this carefully?

I have an ASP file that shows twenty records. It appears
instantaneously.
I have an ASPX file on the same site that shows the same records. It
appears in five to ten seconds.

Now it's quite possible that I haven't optimized my ADO.Net and ASPX
to the extent I've optimized my ADO and ASP. I've been using the first
for two years or so and the last for six or seven years.
Then again I have been programming for more than twenty years so it's
always possible that I have optimized them both.

Nov 30 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB...
5
by: Praty77 | last post by:
Hello - we have been using access 97 for a multiple user trading system at a small bank. Typically around 10 users entered a total of hundred trades everyday. Some of the data was shared with a...
8
by: Thats Me | last post by:
Background: Access 2000 running on Windows 2000, Did not design inherited (three previous database maintainers in last 18 months), Non-existent comments for existing code modules and objects, six...
6
by: Terry Bell | last post by:
We've had a very large A97 app running fine for the last seven years. I've just converted to SQL Server backend, which is being tested, but meanwhile the JET based version, running under terminal...
3
by: JasonRMerrill | last post by:
I've got a system that's been corrupting repeatedly recently and I'm having trouble tracking down the cause. I've done as much reading as I can so far, but am starting to get lost as most of the...
3
by: RayPower | last post by:
I'm having a system using Access 2000 as both front-end (queries, forms, reports & temp tables for reports) & back-end (data) with back-end running on the server. The application runs on the...
49
by: Mell via AccessMonster.com | last post by:
I created databases on Access 2003 and I want to deploy them to users. My code was also done using 2003. If they have Ms Access 2000 or higher, will they be able to use these dbs with all code,...
0
by: Murali | last post by:
In MsAccess project I Create the Link table with Sqlserver 2000. I am facing the multiuser Problem in my application. The problem describes below: If one user is updating one record and at that...
4
by: ThePhenix | last post by:
Hi everybody, I have recently been doing a conversion for my boss for a access 2000 database (both front and backend) to a MSSQL backend. I finished the conversion today (as quite a lot of the...
2
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection...
1
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
0
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
1
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.