Results 1 to 11 of 11
  1. #1
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568

    Moving queries from Access to SQL Server

    Hi Guys,

    I have query where i have another built queries:

    Click image for larger version. 

Name:	Screenshot_4.png 
Views:	41 
Size:	47.1 KB 
ID:	33314

    C_ServerInfo_Copy is also a query consisted with another queries.

    How to move these queries to MS SQL Server?

    Create views and do constraints between them?

    Best,


    Jacek

  2. #2
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    In SQL server you can create views or procedures to retrieve data. Just like in Access you can create views based on other views.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    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.

  4. #4
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    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

  5. #5
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    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.

  6. #6
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Hi NoellaG,

    thank you!
    I will use T-SQL.

    Best,
    Jacek

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    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.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    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

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 09-15-2017, 08:15 PM
  2. Replies: 2
    Last Post: 07-10-2015, 07:10 AM
  3. Replies: 7
    Last Post: 01-09-2015, 12:15 PM
  4. Moving an existing Access Database backend into a SQL Server?
    By IncidentalProgrammer in forum SQL Server
    Replies: 9
    Last Post: 09-16-2014, 08:13 AM
  5. Replies: 6
    Last Post: 05-14-2012, 08:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums