473,320 Members | 1,861 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

How can I speed up an Access database with SQL backend.


Here is the scenario. We have a database with patient case information.
This database was previously solely used in Access. The problem is our
users connect to this database (located in California) from locations
across a large network. California to Virginia. Naturally the east
coast locations were running very slow. We decided to migrate to an
Access front end with an SQL backend. The database contains several
forms with multiple sub-forms in each. In some instances the sub-form
has a fairly large Query as its recordset. As is, we are strictly using
SELECT statements in the recordset property value in Access. This seems
to be the bottleneck of the database. I have tried Store Procedure
calls using Pass Through queries and set them as the recordset but
these forms need to be editable and the return recordset is not
editable.
My question is, Is there a more efficient way to get an editable
recordset into these forms. Speed is our main concern

Nov 8 '06 #1
12 10522
Ma*************@gmail.com wrote:
Here is the scenario. We have a database with patient case
information. This database was previously solely used in Access. The
problem is our users connect to this database (located in California)
from locations across a large network. California to Virginia.
Naturally the east coast locations were running very slow. We decided
to migrate to an Access front end with an SQL backend. The database
contains several forms with multiple sub-forms in each. In some
instances the sub-form has a fairly large Query as its recordset. As
is, we are strictly using SELECT statements in the recordset property
value in Access. This seems to be the bottleneck of the database. I
have tried Store Procedure calls using Pass Through queries and set
them as the recordset but these forms need to be editable and the
return recordset is not editable.
My question is, Is there a more efficient way to get an editable
recordset into these forms. Speed is our main concern
You need to change to a system where the user indicates what ONE RECORD they
need and then retrieve that one record for editing. If ONE is not possible
then you use criteria that will pull the absolute minimum required to do the
job. Pull the records for the subforms one at a time and only when
required.

Standard Access forms with "Record 1 of SomeBigNumber" in the navigation bar
are just not the way to go over a WAN connection.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Nov 8 '06 #2
Ma*************@gmail.com wrote:
Here is the scenario. We have a database with patient case information.
This database was previously solely used in Access. The problem is our
users connect to this database (located in California) from locations
across a large network. California to Virginia. Naturally the east
coast locations were running very slow. We decided to migrate to an
Access front end with an SQL backend. The database contains several
forms with multiple sub-forms in each. In some instances the sub-form
has a fairly large Query as its recordset. As is, we are strictly using
SELECT statements in the recordset property value in Access. This seems
to be the bottleneck of the database. I have tried Store Procedure
calls using Pass Through queries and set them as the recordset but
these forms need to be editable and the return recordset is not
editable.
My question is, Is there a more efficient way to get an editable
recordset into these forms. Speed is our main concern
Have you already split your access database into a front end and back
end. If not do that first.

Second, the way to make things run faster across any network is to
reduce the amount of network traffic. In other words, use data
selection to reduce the quantity of data being queried in main and/or
sub queries.

Thirdly, Access databases do not work well over WANS because of the
potential for corruption if the link fails.

I wonder if database replication could work for you? The key would be
the speed in which updates must be available to all users and the
potential for concurrent updating of the same data.

More info on what you are doing would be helpful.

Bob
Nov 8 '06 #3
For purely speed, I would ditch Access and go with ASP/ASP.NET hosted
on a server. That would be your fastest solution, but your setup costs
will be a bit higher. If the database is only a couple of forms, that
should keep costs down.
Chris Nebinger
Ma*************@gmail.com wrote:
Here is the scenario. We have a database with patient case information.
This database was previously solely used in Access. The problem is our
users connect to this database (located in California) from locations
across a large network. California to Virginia. Naturally the east
coast locations were running very slow. We decided to migrate to an
Access front end with an SQL backend. The database contains several
forms with multiple sub-forms in each. In some instances the sub-form
has a fairly large Query as its recordset. As is, we are strictly using
SELECT statements in the recordset property value in Access. This seems
to be the bottleneck of the database. I have tried Store Procedure
calls using Pass Through queries and set them as the recordset but
these forms need to be editable and the return recordset is not
editable.
My question is, Is there a more efficient way to get an editable
recordset into these forms. Speed is our main concern
Nov 8 '06 #4
"Bob Alston" <bo********@yahoo.comwrote
>Access front end with an SQL backend.
Thirdly, Access databases do not work well over
WANS because of the potential for corruption
if the link fails.
But, according to the original poster's comment above, it is not an Access
database, but client-server with an Access frontend. Your caution it does
not apply to client-server with an SQL Server back-end, in any case.

Rick's advice about redesigning the interface so as to minimize network
traffic is the single-most-helpful thing I know to do with C/S databases.
It is really amazing, if you really look at the _requirements_, how often
you need just ONE record, if it exists, or NONE, if the record doesn't
exist.

Larry Linson
Microsoft Access MVP
Nov 8 '06 #5
As quoted by Bob: I wonder if database replication could work for you

This would be the avanue I wouuld be looking at.
Setup each site with MSDE and create replication to the Parent Server.
Then each site would have a local query to a SQL server, and speed
would be greatly improved.

Nov 8 '06 #6
Larry Linson wrote:
"Bob Alston" <bo********@yahoo.comwrote
>Access front end with an SQL backend.
Thirdly, Access databases do not work well over
WANS because of the potential for corruption
if the link fails.

But, according to the original poster's comment above, it is not an Access
database, but client-server with an Access frontend. Your caution it does
not apply to client-server with an SQL Server back-end, in any case.

Rick's advice about redesigning the interface so as to minimize network
traffic is the single-most-helpful thing I know to do with C/S databases.
It is really amazing, if you really look at the _requirements_, how often
you need just ONE record, if it exists, or NONE, if the record doesn't
exist.

Larry Linson
Microsoft Access MVP

Good point Larry. I read too fast. I thought while they had decided to
switch to SQL, they were still using Jet. My error.

So perhaps my second point, is in agreement:
"Second, the way to make things run faster across any network is to
reduce the amount of network traffic. In other words, use data
selection to reduce the quantity of data being queried in main and/or
sub queries. "

Bob
Nov 9 '06 #7
If you retrieve the info using a pass-through query and the performance
is good, then you have the bandwidth you need.

To update the data, you will need to write some code.

You can create a button on the form to save the data. You will need to
update the record via SQL commands.

Do a search for unbound forms.

Ma*************@gmail.com wrote:
Here is the scenario. We have a database with patient case information.
This database was previously solely used in Access. The problem is our
users connect to this database (located in California) from locations
across a large network. California to Virginia. Naturally the east
coast locations were running very slow. We decided to migrate to an
Access front end with an SQL backend. The database contains several
forms with multiple sub-forms in each. In some instances the sub-form
has a fairly large Query as its recordset. As is, we are strictly using
SELECT statements in the recordset property value in Access. This seems
to be the bottleneck of the database. I have tried Store Procedure
calls using Pass Through queries and set them as the recordset but
these forms need to be editable and the return recordset is not
editable.
My question is, Is there a more efficient way to get an editable
recordset into these forms. Speed is our main concern
Nov 9 '06 #8
We are currently using Replication. Each location has a local file that
an admin synchronizes every night with the back end. This process is
very time consuming as the material is pretty time sensitive.

I have created a form with search criteria that reduces the amount of
records significantly. Usually searching by Social Security Number so
at most 3 or 4 records would be available. I am doing this using a
dynamic Select Statement changing my WHERE clause based on the criteria
selected. But it is my understanding that by using a Select and not a
Stored Procedure the whole recordset set is being filtered on the user
side and not on the SQL server. Is this correct?

Anthos wrote:
As quoted by Bob: I wonder if database replication could work for you

This would be the avanue I wouuld be looking at.
Setup each site with MSDE and create replication to the Parent Server.
Then each site would have a local query to a SQL server, and speed
would be greatly improved.
Nov 9 '06 #9
Depends.

In my testing, which I'm sure is not as extensive as some others, this was
the fastest way to access data over a WAN:

Assuming a local table and a matching server table, using Access here:
---------------------
Dim f As Integer
Dim cnxn As ADODB.Connection
Dim rS As ADODB.Recordset
Dim rL As ADODB.Recordset
Set cnxn = New ADODB.Connection
Set rS = New ADODB.Recordset ' Server
Set rL = New ADODB.Recordset ' Local

rL.Open "table1", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTable

cnxn.Provider = "Microsoft.Jet.OLEDB.4.0" ' Access
cnxn.Open "Data Source=" & ' Server

rS.Open "SELECT * FROM table1 WHERE table1.ssn=' & Me.txtSSN & " ' ", _
cnxn, adOpenForwardOnly, adLockReadOnly, adCmdText

Do While Not rS.EOF
rL.AddNew
For f = 0 To rL.Fields.count - 1
rL.Fields(f) = rS.Fields(f)
Next f
rL.Update
rS.MoveNext
Loop
rS.Close
rL.Close
cnxn.Close
----------------

Of course, if just retrieving a value, not storing it for further use, then
a simple SELECT works faster, but for most of my use, I was copying a client
record to the local tables to manipulate, then saving back to the server at
the end.

Passing parameters to a query/stored procedure on the backend .mdb sped up
large queries; I assume that it would be even faster with SQL Server.

Of course, I made every effort to not download text fields, using every
locally stored lookup table I could.

--
Darryl Kerkeslager

<Ma*************@gmail.comwrote
I have created a form with search criteria that reduces the amount of
records significantly. Usually searching by Social Security Number so
at most 3 or 4 records would be available. I am doing this using a
dynamic Select Statement changing my WHERE clause based on the criteria
selected. But it is my understanding that by using a Select and not a
Stored Procedure the whole recordset set is being filtered on the user
side and not on the SQL server. Is this correct?

Nov 10 '06 #10
Thanks I think that may work. I have also noticed an great increas in
performance by Deselecting the "Track Name AutoCorrect" options. As
well as setting the subdatasheet view to [None]. A couple simple things
that I wasn't aware of to increase performance.

Darryl Kerkeslager wrote:
Depends.

In my testing, which I'm sure is not as extensive as some others, this was
the fastest way to access data over a WAN:

Assuming a local table and a matching server table, using Access here:
---------------------
Dim f As Integer
Dim cnxn As ADODB.Connection
Dim rS As ADODB.Recordset
Dim rL As ADODB.Recordset
Set cnxn = New ADODB.Connection
Set rS = New ADODB.Recordset ' Server
Set rL = New ADODB.Recordset ' Local

rL.Open "table1", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTable

cnxn.Provider = "Microsoft.Jet.OLEDB.4.0" ' Access
cnxn.Open "Data Source=" & ' Server

rS.Open "SELECT * FROM table1 WHERE table1.ssn=' & Me.txtSSN & " ' ", _
cnxn, adOpenForwardOnly, adLockReadOnly, adCmdText

Do While Not rS.EOF
rL.AddNew
For f = 0 To rL.Fields.count - 1
rL.Fields(f) = rS.Fields(f)
Next f
rL.Update
rS.MoveNext
Loop
rS.Close
rL.Close
cnxn.Close
----------------

Of course, if just retrieving a value, not storing it for further use, then
a simple SELECT works faster, but for most of my use, I was copying a client
record to the local tables to manipulate, then saving back to the server at
the end.

Passing parameters to a query/stored procedure on the backend .mdb sped up
large queries; I assume that it would be even faster with SQL Server.

Of course, I made every effort to not download text fields, using every
locally stored lookup table I could.

--
Darryl Kerkeslager

<Ma*************@gmail.comwrote
I have created a form with search criteria that reduces the amount of
records significantly. Usually searching by Social Security Number so
at most 3 or 4 records would be available. I am doing this using a
dynamic Select Statement changing my WHERE clause based on the criteria
selected. But it is my understanding that by using a Select and not a
Stored Procedure the whole recordset set is being filtered on the user
side and not on the SQL server. Is this correct?
Nov 14 '06 #11
Thanks I think that may work. I have also noticed an great increase in
performance by Deselecting the "Track Name AutoCorrect" options. As
well as setting the subdatasheet view to [None]. A couple simple things
that I wasn't aware of to increase performance.

Darryl Kerkeslager wrote:
Depends.

In my testing, which I'm sure is not as extensive as some others, this was
the fastest way to access data over a WAN:

Assuming a local table and a matching server table, using Access here:
---------------------
Dim f As Integer
Dim cnxn As ADODB.Connection
Dim rS As ADODB.Recordset
Dim rL As ADODB.Recordset
Set cnxn = New ADODB.Connection
Set rS = New ADODB.Recordset ' Server
Set rL = New ADODB.Recordset ' Local

rL.Open "table1", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTable

cnxn.Provider = "Microsoft.Jet.OLEDB.4.0" ' Access
cnxn.Open "Data Source=" & ' Server

rS.Open "SELECT * FROM table1 WHERE table1.ssn=' & Me.txtSSN & " ' ", _
cnxn, adOpenForwardOnly, adLockReadOnly, adCmdText

Do While Not rS.EOF
rL.AddNew
For f = 0 To rL.Fields.count - 1
rL.Fields(f) = rS.Fields(f)
Next f
rL.Update
rS.MoveNext
Loop
rS.Close
rL.Close
cnxn.Close
----------------

Of course, if just retrieving a value, not storing it for further use, then
a simple SELECT works faster, but for most of my use, I was copying a client
record to the local tables to manipulate, then saving back to the server at
the end.

Passing parameters to a query/stored procedure on the backend .mdb sped up
large queries; I assume that it would be even faster with SQL Server.

Of course, I made every effort to not download text fields, using every
locally stored lookup table I could.

--
Darryl Kerkeslager

<Ma*************@gmail.comwrote
I have created a form with search criteria that reduces the amount of
records significantly. Usually searching by Social Security Number so
at most 3 or 4 records would be available. I am doing this using a
dynamic Select Statement changing my WHERE clause based on the criteria
selected. But it is my understanding that by using a Select and not a
Stored Procedure the whole recordset set is being filtered on the user
side and not on the SQL server. Is this correct?
Nov 14 '06 #12
Efficient use of indexes also greatly increases speed.

Always using transactions may also speed up this code.

Also, if you time this code at key points, you will likely find that the
actual opening of the connection can be the biggest bottleneck, so try to
open, do lots of SQL at once, then close.
--
Darryl Kerkeslager

<Ma*************@gmail.comwrote
Thanks I think that may work. I have also noticed an great increas in
performance by Deselecting the "Track Name AutoCorrect" options. As
well as setting the subdatasheet view to [None]. A couple simple things
that I wasn't aware of to increase performance.

Nov 15 '06 #13

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

Similar topics

6
by: MAB71 | last post by:
There should be a way to copy an access database ( .mdb file ) without closing connections to it. Unfortunately the FileCopy statement in VB gives an error if users are connected to the db. But I...
7
by: JMCN | last post by:
Is this possible to have the 97 users with 97 front end, 2000 users with 2000 front end, 2002 users with 2002 front end, and 2003 users with 2003 front end all linked up to an access 97 backend? ...
5
by: premmehrotra | last post by:
I currently have a multi-user access database which is put on a shared drive L: on a Windows Servers. Entire database is one file premdb.mdb. Users access this database from their laptops....
9
by: Booster | last post by:
Hop someone can help me Working with access 2000 desktop application I am looking to make connection to a backend access database online on a windows hosting How to connect if possible Many...
5
by: Stewart | last post by:
Hi there, I have an Access database, which contains the details of company staff and services. The plan is to extract data from this database onto our forthcoming Intranet (no inserting,...
1
by: Tammy Viola | last post by:
I would like to import an Excel Worksheet as a new table in the backend of an Access database. Looked at using docmd.TransferSpreadsheet without success. Also looked at creating a table using...
12
by: jimdan | last post by:
I have a ms access database that I have split into a front and backend database. After the user enters data into the database (stored in the backend), the DB grows to about 50 MB. The user then...
2
by: ykhamitkar | last post by:
Hi There, I have some questions about ms access database 1. How much data ms access can handle with good speed. 2. Does the size of column affect the speed of ms access database. (If i...
1
by: anjee | last post by:
Hello all, We are in the process of rewriting a large application that uses an Access 2003 backend database. Unfortunately we will be building and releasing the application in phases which means...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.