Calling sql server queries in MS access forms

[Derek]

Hi Guys

We are planning to migrate tables and queries from access to sql server and use ms access as a frontend for the applications only. I have the option to migrate tables using upsizing wizard or sql server migration assistant. But as there is no migration tool available to migrate queries from ms access to sql server so I am thinking to copy and paste queries from Ms access to sql server editor and then change the syntex accordingly.

So my question is when I have both tables and queries on sql server then I will link the tables to MS access frontend .But how can I call queries in MS access forms?

Any suggestions will be much appreciated.

Thanks

 

[arnelgp]

You can create the query in ms access just as before to return the data. Bind the query to the form.

 

[Derek]

No I want all the queries on server side for optimum performance..

 

[Minty]

There are a myriad of ways to achieve this, as Arne has said your queries will still work if you link the tables and rename them to the same as the original table. That is the simplest route.

If you are trying to remove the Access processing from the front end and move it all to the server then there are two routes, any non parameterised queries can be created as Views on the server and then link them back to the front end - they will appear as if they are linked tables, but again - if you rename them to your query names everything will again work as originally.

Anything you want to provide a parameter to will either have to be done with Access locally filtering the view or table, or you can pass a parameter through to a stored procedure or create a Pass through parameter query on the fly using VBA.

All are possible in VBA.

 

[pbaldy]

No I want all the queries on server side for optimum performance..

With linked SQL Server tables, Access is pretty good at passing the SQL back to SQL Server to do the processing. In many/most cases an Access query will perform just as well as a SQL Server stored procedure, view, whatever. I start with Access queries and only move to SQL Server solutions when performance is an issue.

 

[isladogs]

No I want all the queries on server side for optimum performance..

I concur totally with Minty regarding the use of SQL Server views.
However you need to be aware that unless you ensure the view has a PK field when it is linked in Access, it will be read only.

In addition, although it is widely believed that running queries in SQL server will lead to better performance and, in certain instances this is indeed true, in reality the difference is often negligible.

Whilst on the subject of misconceptions, the Import/Export wizard in SQL Server can import both tables & SELECT queries (which it calls views) from Access. Action queries cannot be imported using the wizard

 

[CJ_London]

To add to the other comments. It is the ODBC driver which converts access sql to tsql to run on the server. As already said, in many cases this works as well as an access backend.

However you do need to ensure your queries do not mix local and server tables (which doesn't sound like it will be an issue in your case) and do not require the use of vba functions such as domain functions, iif, nz, date and the like which the driver will be unable to translate, so will pass back a larger dataset for local processing. This is particularly important if they are used in subqueries.

You might find this link of interest on performance issues which applies to any db connected across a LAN.

https://www.access-programmers.co.uk/forums/showthread.php?t=291269

 

[Derek]

Thanks , I have never used Pass-through queries. Do we need to create those queries in MS Access so basically those queries will be created in MS access but will be processed on server side (sql server). am I right?

There are a myriad of ways to achieve this, as Arne has said your queries will still work if you link the tables and rename them to the same as the original table. That is the simplest route.

If you are trying to remove the Access processing from the front end and move it all to the server then there are two routes, any non parameterised queries can be created as Views on the server and then link them back to the front end - they will appear as if they are linked tables, but again - if you rename them to your query names everything will again work as originally.

Anything you want to provide a parameter to will either have to be done with Access locally filtering the view or table, or you can pass a parameter through to a stored procedure or create a Pass through parameter query on the fly using VBA.

All are possible in VBA.

 

[CJ_London]

yes - they are written in the query sql window and passthrough parameters provided (connection string etc), or use vba as you would for writing access queries. You can also use a passthrough query to execute a stored procedure.

But as advised, an ordinary query will still be processed server side so there is no benefit in performance unless you are going to take advantage of some of the features of TSql.

It would be useful to know why your are planning to migrate to sql server, there are many reasons to do so, but if it is for a perceived improvement in performance, you will most likely be disappointed. There are other factors which have a much bigger impact on performance such as network speed. Sql server still needs to be maintained and tuned so has significantly higher costs than access in terms of licencing and maintenance costs.

You might want to experiment and try using sql server express before committing to the full version