Copy data MSAccess to SQL Server w/VBA & ADO ( bulk Copy)

Posted on 2006-04-20
Last Modified: 2012-06-27
have an MSAccess database I need to recreate one of it's tables on SQL Server. I need to do it in VBA just after I create the MSAccess Table. What would the syntax be to copy and entire recordset to SQL Server. I know how to do it in a record cursor mode how do you do a bulk copy or can you.
My current code looks something like this:

 Dim mxdb As Database
  Dim MainSet As Recordset

    Set MainSet = mxdb.OpenRecordset( _
    "SELECT D.field1, D.field2, C.field3, C.Field4 " & _
    "FROM (Cubes AS G LEFT JOIN Desc AS C ON G.CLASS = C.Class) " & _
    "     LEFT JOIN Comm AS D ON G.CODE = D.Alpha " & _
    "WHERE G.NAME='UXXX' " & _
    "AND G.CODE = '6' ")
   Set XY_Conn = New ADODB.Connection
   XY_Conn.Provider = "MSDASQL"
   XY_Conn.ConnectionString = "UID=XYadmin;PWD=XY001"
   XY_Conn.Open DSNXX
   Set XY_RecSet = New ADODB.Recordset
   strSQL = "Truncate Table XY_PSA"
   Set XY_RecSet = New ADODB.Recordset
   XY_RecSet.Open strSQL, XY_Conn

   Set XY_RecSet = New ADODB.Recordset
   XY_RecSet.Open "XY_PSA", XY_Conn, adOpenDynamic, adLockOptimistic
   With MainSet
      Do Until MainSet.EOF
         XY_RecSet!field1 = MainSet!field1
         XY_RecSet!field2 = MainSet!field2
         XY_RecSet!field3 = MainSet!field3
         XY_RecSet!field4 = MainSet!field4
   End With

What would I do to reproduce this without walking through each record .
Question by:mbmalm
    LVL 9

    Accepted Solution

    Create a Link to the SQL Server table, and then you can write a Append query that selects data from your access table and inserts into SQL linked table

    Author Comment

    Excellent suggestion. Seems like such a logical solution, but I didn't think of it.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    error on vba access function, ByRef 4 27
    MS Access question 11 39
    Access left join query 5 17
    Microsoft Access 2010 Question 2 14
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now