Solved Access front end -to- SQL back end (1 Viewer)

mahjohn

New member
Local time
Yesterday, 22:14
Joined
Jan 30, 2019
Messages
7
Hello,


Looking for some help in understanding what's going on and what my options are to resolve this.

I run a local access database, pretty simple tables and queries. The database needs to be migrated to a remote SQL server, accessible only via VPN. ODBC connection, uploaded, and linked some tables to Access as a front-end.

The problem I have is speed...it could take 45mins to run a simple delete or update query that would be completed almost instantly were it local. Can someone explain to me how Access works with linked tables, and why it would take this long?

Do I need to have Access running on a server on the same network as the SQL instance and connect via RDP, or possibly convert the qrys to Stored Procedures and execute via a command from Access (don't know how to do this)

Any ideas....thanks
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:14
Joined
Oct 29, 2018
Messages
21,358
Hi. Yes, if you're talking about batch updates and deletes, one option is to convert your queries into pass through queries to let the server do the work instead of Access.
 

Minty

AWF VIP
Local time
Today, 05:14
Joined
Jul 26, 2013
Messages
10,355
This is the answer
Do I need to have Access running on a server on the same network as the SQL instance and connect via RDP

Unless your VPN connection is unbelievably good, which it isn't from what you described. There are lots of other mitigating circumstances, but the RDP solution is pretty bombproof.

Technically Access uses SMB protocol which relies on an active persistent connection.

Further techy stuff here: https://www.access-programmers.co.uk/forums/showthread.php?t=292915
 

sonic8

AWF VIP
Local time
Today, 06:14
Joined
Oct 27, 2015
Messages
998
The problem I have is speed...it could take 45mins to run a simple delete or update query that would be completed almost instantly were it local. Can someone explain to me how Access works with linked tables, and why it would take this long?
Hard to say without seeing the "simple query". If the query is actually simple, then it should also run very fast with a SQL Server backend on a remote network.

Can you post a sample of your queries?

Technically Access uses SMB protocol which relies on an active persistent connection.
This is the case only with an Access backend, but not with a SQL Server backend.
 

mahjohn

New member
Local time
Yesterday, 22:14
Joined
Jan 30, 2019
Messages
7
What has happened is that I am not a developer, or that great with Access, however, the process works well locally. That being said, the one query that is problematic is due to inefficiency. When it ran locally it wasn't an issue. Every week I import new data, and Step 1 is to set a field to null for every record. Step 2 was to join to the incoming data and where there was a match set the field to "Yes", Step 3 was to set any remaining null fields to "No". When it was a local table, not a problem, when it's remote, Step 1. is updating 25,000 fields to null and that's where it hangs. Yes...I know, could have used IF,THEN,ELSE....but as I said, not very good at this. I think if I convert to a single Step using IF,THEN,ELSE this would minimize the work being done and make the query more efficient and a lot faster. (I think I did the IF,THEN,ELSE once before, have to look for that query to see how it's written)

your thoughts?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:14
Joined
May 7, 2009
Messages
19,169
consult again your IT, to allow Nulls to your fields.
 

mahjohn

New member
Local time
Yesterday, 22:14
Joined
Jan 30, 2019
Messages
7
Nulls for that field are allowed in the SQL table.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:14
Joined
Oct 29, 2018
Messages
21,358
When it was a local table, not a problem, when it's remote, Step 1. is updating 25,000 fields to null and that's where it hangs.


your thoughts?
Hi. Have you tried converting Step 1 to a passthrough query? Just curious...
 

Users who are viewing this thread

Top Bottom