Linked Table from SQL Server is done. But the Excel export macros are not working. (1 Viewer)

SORM2

New member
Local time
Today, 15:23
Joined
Jan 16, 2021
Messages
25
Dear Friends,

I have linked tables from SQL server and adjusted all queries based on new linked tables' names in my MS Access and tested it. İt workes a little slow in compared to compact MS Access (When the table is also from Access itself). Forms and reports are opening a bit more slowly. But it is not that bad. However I would expect it will be not worse than MS Access itself. Is there a way to accelerate the speed of forms openings? Can it be possible that I did something not very accurate?
(Some additional informations - My linked table connection is done with SQL Server Driver and File DSN
I did all indexing PKs, and also relations in SQL Server side)


Second and bigger problem: My excel export macros in MS Access that are using queries are now failed. I looked at the used queries within my macros. Those queries are working as expected properly by alone. But when it comes to click the export button in my forms which is running those queries are failing.

What is the solution here? I will be apprecitated.
 

Minty

AWF VIP
Local time
Today, 12:23
Joined
Jul 26, 2013
Messages
10,354
Using SQL as an efficient backend requires considerable re-engineering normally to achieve good results.
You have to learn to use the Server to do the "heavy lifting".

Consider using views on the server instead of Queries locally if many joins and records are involved.

Limit the number of records you pull onto forms.

Avoid custom VBA functions in queries - these are a killer.

Second Problem. No Idea.
If the queries run then the export should.

Please post up the code used to perform the export.

EDIT: Have a read here for performance issues https://www.access-programmers.co.uk/forums/threads/addressing-performance-issues.291269/#
 
Last edited:

Users who are viewing this thread

Top Bottom