• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 863
  • Last Modified:

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

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"
   DSNXX = "DRIVER={SQL Server};SERVER=PSQL04;UID=XYadmin;PWD=XY001;DATABASE=XY"
   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.AddNew
         XY_RecSet!field1 = MainSet!field1
         XY_RecSet!field2 = MainSet!field2
         XY_RecSet!field3 = MainSet!field3
         XY_RecSet!field4 = MainSet!field4
       XY_RecSet.Update
 
      MainSet.MoveNext
     Loop
   End With
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

What would I do to reproduce this without walking through each record .
0
mbmalm
Asked:
mbmalm
1 Solution
 
raopsnCommented:
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
0
 
mbmalmAuthor Commented:
Excellent suggestion. Seems like such a logical solution, but I didn't think of it.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now