Results 1 to 4 of 4

Can you run a query against sql server and save the results in access?

  1. #1
    dhartley is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    4

    Can you run a query against sql server and save the results in access?


    First of all, hello and I apologise if this has already been answered but I haven't been able to find a solution through my research.

    I was wondering if it is possible to run a query against SQL Server and then save the results into a new access database? I was thinking this could be done through VBA but I'm not sure where to start. Any ideas?

    Thanks

    Dave

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,834
    One way would be to create an ODBC connection to your SQL database.
    Then, you could create a new Access database in which you use a Pass-Through query to run the query against the SQL database (using the ODBC connection).
    You can make that query a Make Table Query to write the results to a new Access table in your Access database.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    dhartley is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    4
    Thanks Joe, got it sorted now.

    First of all I created a Query that was a pass through query.
    Then I created a Query that was a make table query - this query ran the Query above and put the results into a table.

    One problem I had was that it kept asking me what DSN to use - I found out that you can set the OBDC Connect Str on the query Property Sheet to stop this happening.

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,834
    Yep, you got it! That is usually how I do it.

    Glad you got it to all work out.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

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

Similar Threads

  1. Replies: 1
    Last Post: 04-13-2012, 01:45 PM
  2. Save Combobox results to Table
    By Kinder0177 in forum Access
    Replies: 5
    Last Post: 06-29-2011, 08:47 AM
  3. Replies: 12
    Last Post: 05-22-2011, 03:49 PM
  4. Convert Access query to SQL Server query
    By mkallover in forum Queries
    Replies: 2
    Last Post: 05-13-2011, 06:20 AM
  5. Convert query sql server for Access
    By webtony in forum Queries
    Replies: 0
    Last Post: 06-23-2009, 02:46 AM

Posting Permissions

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