1

I'm currently using MS Access as my front end for database I've created. I have a PowerApp that users utilize and it's backend is SQL Server.

However, I need to have functions to load stuff into those tables, so I'm utilizing Access as that tool.

There are a few queries I'd like to run on the server side vs having Access run them since Access pulls everything in from the server and then does the action and then pushes it back to the server.

Is there some way with VBA that I can run code on the server vs trying to use DoCmd.RunSQL which seems to run it through MS Access?

Improve this question
2
  • 2
    You are looking for Pass-Through queries. Commented Aug 29, 2022 at 16:29
  • 1
    Specifically, when you design your query set the type of query to be Pass-Through. You can code DDL or DML SQLs as well as Selects, but syntax needs to follow SQLServer rules, not ODBC rules, because Pass-Through means just what it says; whatever you type is passed through to the data base. Commented Aug 29, 2022 at 17:33

1 Answer 1

Reset to default
2

since Access pulls everything in from the server and then does the action and then pushes it back to the server.

Well, it depends on what you are doing. If you say launch a form in access, bound direclty to a linked table in sql server with 1 million rows, and do this:

docmd.OpenForm "frmCustomers",,,"CustomerID = 1323"

Then access ONLY pulls down the ONE row from SQL server.

but, for Access update queries? yes, they often run slow.

As suggested in comments, then you can create + run your SQL 100% server side.

You simply can launch the query builder in Access. You have to use SQL view, and you have to setup the query as pass-through.

So, say this:

enter image description here

You can quite much shove into the text of the PT query anything you want - even EXEC dbo.SomeStoreProcName or whatever.

and you can even set the above SQL in VBA if you wish.

So, create a PT query - anything you type into that query will run 100% server side.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.