Speeding up SQL server connection (1 Viewer)

CedarTree

Registered User.
Local time
Today, 05:05
Joined
Mar 2, 2018
Messages
404
Hello.
Access is FE to an SQL server DB. To speed things up, I have users look up a primary record and any tables/records associated with that primary record are temporarily copied to local tables. When a user uses a form to delete/update/insert records in the local table, I use Form events to mirror the changes to the SQL server. It works well, but has a lot of coding (I have to worry about each event, etc.) Is there a reasonably fast way to do this with perhaps less coding? Appreciate suggestions / tricks. Thanks!
 

isladogs

MVP / VIP
Local time
Today, 09:05
Joined
Jan 14, 2017
Messages
18,186
Is there a reasonably fast way to do this with perhaps less coding?

Yes! Scrap the copy to local & copy back to SQL Server.
If your network connection is over a wired LAN it SHOULD be good enough
If its wireless or over a WAN then you will have serious problems

Instead focus on using more efficient queries - e.g index all fields used in query joins, search criteria, order by & grouping clauses
 

CedarTree

Registered User.
Local time
Today, 05:05
Joined
Mar 2, 2018
Messages
404
I did that before and I found using pass through queries to copy back and forth was 10 times faster.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:05
Joined
May 7, 2009
Messages
19,169
You just need a persistent connection to the back end. Open a a linked table and held it open until you close your app. No need coding.
 

CedarTree

Registered User.
Local time
Today, 05:05
Joined
Mar 2, 2018
Messages
404
How do you mean? Open it and make it hidden somehow?

I did some googling... and my front-end form has some subforms connected to the SQL server... so doesn't that keep a persistent connection open?
 
Last edited:

CedarTree

Registered User.
Local time
Today, 05:05
Joined
Mar 2, 2018
Messages
404
Thanks will give it a try. So having subforms connected to BE is not enough per se to keep a persistent connection?
 

Users who are viewing this thread

Top Bottom