MS Access/SQL Server Speed Issues (1 Viewer)

Learn2010

Registered User.
Local time
Today, 17:59
Joined
Sep 15, 2010
Messages
415
I work for a hospital support team and have created Access databases with a SQL back-end for our department. I have no control over the SQL. Here is my issue. Onsite, my database is average to good speed. The other users are much slower than mine. Using Cisco to connect from offsite, we get the same problems, with 2 of the users not being able to even open their front-end. It could be the way each was setup.

I connected to the SQL DB through my front-end via an ODBC connection. I then made a copy for each of the users (5). On each station I would connect to the SQL DB using the same connection method. As I made updates, I would export them to the other databases; forms, tables, queries, etc. Occasionally, I would replace the whole database by copying mine over to their respective folders and replacing theirs. My speed has been constant. Theirs seems to keep getting slower.

I have a suspicion that my replacing their original database has caused some confusion with that SQL connection and is the reason theirs is slower. Can anyone address this issue.

Thank you.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:59
Joined
Oct 29, 2018
Messages
21,358
Hi. Just a quick question, are you using a persistent connection?
 

Learn2010

Registered User.
Local time
Today, 17:59
Joined
Sep 15, 2010
Messages
415
I don't know. It is even hard for me to get a SQL support person. So, when I do, I have to have the right question. I do know that they usually close the database at the end of each day.
 

HiTechCoach

Well-known member
Local time
Today, 16:59
Joined
Mar 6, 2006
Messages
4,357
... I have no control over the SQL.

Does this mean the SQL Server database you connect to is not your database? Possibly belongs to another software application?


The other users are much slower than mine. Using Cisco to connect from offsite, we get the same problems, with 2 of the users not being able to even open their front-end. It could be the way each was setup.

Connecting to a back end over a WAN will be slower.

I connected to the SQL DB through my front-end via an ODBC connection. I then made a copy for each of the users (5). On each station I would connect to the SQL DB using the same connection method.

Are you using a DSN or DSN-less connection?


As I made updates, I would export them to the other databases; forms, tables, queries, etc. Occasionally, I would replace the whole database by copying mine over to their respective folders and replacing theirs. My speed has been constant. Theirs seems to keep getting slower.

I have a suspicion that my replacing their original database has caused some confusion with that SQL connection and is the reason theirs is slower. .

I always replace my front end when deploying updates and have never had a speed decrease. Either they can connect to the SQL Server database or they can't.


Pass-Trough Queries.

Are you using pass-through queries when you need snapshots of data?
 
Last edited:

Learn2010

Registered User.
Local time
Today, 17:59
Joined
Sep 15, 2010
Messages
415
I believe it is DSN. Also, as I said, I have no control over the SQL other than connect to it. I use to have a contact when I first set it up. He has since left.
 

Learn2010

Registered User.
Local time
Today, 17:59
Joined
Sep 15, 2010
Messages
415
I use normal VBA code. Most of the queries pull data from the SQL DB. I process it, then append new or update the old via a query. As you can tell, my knowledge of SQL is not at a high level.
 

HiTechCoach

Well-known member
Local time
Today, 16:59
Joined
Mar 6, 2006
Messages
4,357
What you really need is the ability to use SQL Server Management Studio (SSMS). It is a desktop application that would allow you to create views and stored procedures in the SQL Server databases.

If your Access front end can write to the database, it would make sense you should have permission to run SSMS.

What impacts speed is how much data moves "over the wire" (the network connection) when you pull data from the back end. This is true with SQL Server and Access back ends.

If speed is the goal, you want to move as little data as possible from the back end tot eh front end.

What can make using an SQL Server faster is moving data processing to the SQL Server, eliminating the need to move any data over the network. This can be done with views and stored procedures (T-SQL)

Also making sure that the tables are properly indexed can have a significant impact on speed. A lack of indexes can really slow things down as the volume of data in the database increases.
 

Learn2010

Registered User.
Local time
Today, 17:59
Joined
Sep 15, 2010
Messages
415
What you really need is the ability to use SQL Server Management Studio (SSMS). It is a desktop application that would allow you to create views and stored procedures in the SQL Server databases.

If your Access front end can write to the database, it would make sense you should have permission to run SSMS.

What impacts speed is how much data moves "over the wire" (the network connection) when you pull data from the back end. This is true with SQL Server and Access back ends.

If speed is the goal, you want to move as little data as possible from the back end tot eh front end.

What can make using an SQL Server faster is moving data processing to the SQL Server, eliminating the need to move any data over the network. This can be done with views and stored procedures (T-SQL)

Also making sure that the tables are properly indexed can have a significant impact on speed. A lack of indexes can really slow things down as the volume of data in the database increases.
Thank you.
 

Users who are viewing this thread

Top Bottom