In SQL server you can create views or procedures to retrieve data. Just like in Access you can create views based on other views.
you CAN copy the sql , then paste it into SqlServer side.
but you can still run the queries as-is in access.
zero work, same table results.
Thank you Guys!
Hmm views based on other views - nice !
ranman256 - what do you mean?
Sql server using different sql commands than access, for example CASE instead of IIF.
Please explain more,
thank you!
Best
Jacek
Hi,
SQL server uses T-SQL, a similar but different SQL dialect than Access, so never copy SQL statements from Access to SQL server or vice versa. What you can do is create pass-through query's in Access in T-SQL . T-SQL is far more powerful than Access SQL, so it pays off to use T-SQL.
Hi NoellaG,
thank you!
I will use T-SQL.
Best,
Jacek
MS SQL Server query syntax has much more options available to use. You can get the result with a single query instead using saved queries (views), or even write a query instead of which in Access you have to write a procedure;
MS SQL Server query restrictions for number of datasources/links/etc are higher;
MS SQL Server queries are faster in general (the queries are optimized better).
All queries you have in Access you can port into SQL Server without big problems (there may be slight differences in syntax). But my advice is, that at least with queries using subqueries you consider redesign them so you take full advantage from SQL Server query syntax.
I think I'm with ranman. Certainly T-SQL provides more powerful tools, but in my experience the average Access query against linked SQL Server tables runs just as fast as a view/SP/whatever. Access is pretty good about passing SQL back to SQL Server and getting just the result set back. In other words, the server is already doing the heavy lifting.
My rule of thumb is to start with Access queries and move to SQL Server solutions when performance isn't acceptable.
thank you Guys.
pbaldy i do not exatly what you and Ranman mean. you are suggesting to create queries in Access and pass and pass results to SQL Server?
Best,
Jacek
I'm saying with linked tables, a simple Access query against those linked tables will often run just as quickly as a pass through query (or view, etc). In the background, Access will try to convert the Access SQL to T-SQL, including parameters, and pass that to the server. When it can't is when you start to get performance problems (in general terms).
I have a db open with tables containing millions of records. An Access query that groups and sums with form parameters returns the correct 200 records instantly. Maybe I'm just lazy, but why would I go through the extra work of moving that to T-SQL?
thank you!
I have to pull these queries to excel so i think that the better way will be write PostgreSQL views/queries.
Or give users access to Access FE and download queries from Access directly. It will be easier to write them and not change them into T-SQL...
Best,
Jacek