Upsize Access > MSSQL

Quite often Access queries contain parameters or criteria based on fields on forms. These arent easily converted to views. I suggest you only convert the queries which return ALL records to VIEWS and those which take parameters convert to pass-through queries. Pass-through queries arent as fast as Views or stored procedures as they need to be \"compiled\" but the performance is MUCH better than Access\'s queries.

You can tbuild your Pass-through queries dynamically in code when paramters and criteria change.

terry> Skal jeg forstå det sådan at Access querys udføres i front-end og ikke på serveren, altså at Access først henter alle records fra serveren og derefter udfører selve forespørgslen?

Yes, you could say that.
Look at it like this. The linked SQL Server tables are much like Access\'s own tables (in the front-end). So when you make a query on these, then it is Access which does the work to find what it wants.  A pass-through query is sent to the server which does the work and returns only those asked for.

Thanks Terry. Does that also mean that if i link to a view, the work is done on the server and therefore faster than a link to a table?

a view can be on one or more tables and it could also return a subset of the total records (much like a query). Because it is on the server then it has been checked for correct syntax and compiled. A linked table is for only one table and contains all records. There are limitatins on views and passthrough queries in that they are not always updatable, so you should concider this when using them. I quit eoften use passthrough queries for combo boxes where I know the data doesnt change so often. I use views instead of queries for reports etc. The view returns all records and then I use passthrough queries to select subsets ets.

Thanks alot Terry. I\'ll properly be back later with more questions once I get a little more familiar with MSSQL.