Results 1 to 3 of 3
  1. #1
    cage4000 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    1

    Copy DAO.Recordset with SQL Server data to Local table in MS Access 2016

    Hi Access forum peeps, i need your help.




    I'm trying to pull records I need from the SQL Server that I have pulled into a DAO.Recordset and save them to the local MS Access Table (Either via append or make table) but no mater what i try i cant get the SQL VBA Query to make or append to a local table. Any help with this is most appreciated. I have been working on this issue for 2 weeks now




    here is what i have so far:



    Code:
    Dim MakeAdjTbl As String
    Dim dbSQL As DAO.Database
    Dim rsMakeT As DAO.Recordset
    
    
    Set dbSQL = OpenDatabase("", False, False, "Driver={SQL Server Native Client 11.0};Server=L563931F034C53\ICQADB;Database=QA;Trusted_Connection=yes;")
    
    
    
    
    MakeAdjTbl = "SELECT * " _
        & "INTO tblAdjAllDates " _
        & "FROM ADJ_SUBTemp;"
    
    
    
    
    Set rsMakeT = dbSQL.OpenRecordset(MakeAdjTbl, dbOpenDynaset, dbSeeChanges)
    
    
    ‘ Need code to append or make table to local access database table “tblAdjAllDates”

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    As answered elsewhere, you can open a second recordset on the local table. Within a loop of the first recordset, use the AddNew method of the second recordset to add each record.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oh, and a lot of people will refer you to this:

    cross posters
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. sql server query to local table
    By SteveApa in forum SQL Server
    Replies: 3
    Last Post: 09-08-2016, 01:31 PM
  2. Replies: 1
    Last Post: 07-05-2016, 07:42 AM
  3. How to Create local copy of linked Table
    By behnam in forum Programming
    Replies: 3
    Last Post: 11-20-2014, 05:49 PM
  4. Replies: 1
    Last Post: 09-03-2014, 10:48 AM
  5. Replies: 4
    Last Post: 08-23-2012, 12:28 AM

Tags for this Thread

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