Access VBA to run query on server side-CodePudding

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?

CodePudding user response：

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:

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.