Results 1 to 10 of 10

Using VBA to copy table with SQL Server data into my MS access table

  1. #1
    ezeanya is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    9

    Using VBA to copy table with SQL Server data into my MS access table

    Please can someone help with this. I'm new to using VBA and i wanted to find a way to copy a table from an SQL server into my microsoft access table



    here is what i did ( i'm familiar with doing in excel but the data is HUGE!)

    Sub test()
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim SQL As String


    cnn.Open "Provider=MSDAORA;Password=xx;User ID=xx;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOC OL=TCP)(HOST = xx)(PORT = xx)))(CONNECT_DATA=(SID=xx)));"


    SQL = "SELECT lotnumX FROM InvTable "


    rs.Open SQL, cnn, adOpenForwardOnly


    End Sub

    what i dont know is how to take the SQL and copy into a table i have in my access

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,617
    If you can link to the SQL Server table, it's quite simple:

    CurrentDb.Execute "INSERT INTO LocalTable(FieldName) SELECT lotnumX FROM InvTable", dbFailOnError

    If you can't, you'll have to loop that recordset and insert values one-by-one. It will not be near as efficient.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,261
    What would you do with that recordset in Excel?

    I don't work with SQLServer but maybe following will help.

    With that connection and recordset, think would have to loop through recordset and save 1 record at a time.

    Explore DoCmd.TransferDatabase https://docs.microsoft.com/en-us/off...ansferDatabase

    Or DSN-less connection to establish link then work with the linked table https://support.microsoft.com/en-ph/...nked-tables-in

    Now I see Paul's post. Yes, can you set ODBC link with Access External Data Import/Export wizard?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    ezeanya is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    9
    thanks. i added this and i get an error that says "runtime error 3024 could not find file"

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,617
    Added what exactly? If mine, the SQL Server table would have to be linked, and the names of the objects adjusted to yours.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    ezeanya is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    9
    yes i added the "CurrentDb.Execute "INSERT INTO LocalTable(FieldName) SELECT lotnumX FROM InvTable", dbFailOnError"

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,617
    So is the SQL Server table linked to the database you're running this in? Did you change the items in red to match your names?

    CurrentDb.Execute "INSERT INTO LocalTableName(LocalFieldName) SELECT lotnumX FROM InvTable", dbFailOnError
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    ezeanya is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    9
    thanks. i did the following
    i changed the name "currentDb.execute" to the cnn.execute and that removed the error. now i get a different error of "table or view does not exist"

    the table name in my MS access is MPSUI. and the field name (only one field to test the VBA) is Item_Code

    cnn.Execute "INSERT INTO MPSUI (Item_Code)SELECT LOTNUMBER FROM ETH.lotinventory ", dbFailOnError

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,617
    I don't seem to be able to get an answer to "is the SQL Server table linked to the database you're running the code in?" You can't insert across platforms.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    3,757
    Agree with Paul. If the SQL table is linked, this becomes a trivial INSERT statement/query.

    Or you can then right click the linked table in the nav pane and select Convert to Local Table.
    Doing so makes a new local Access table in the FE and removes the link to the SQL table
    Colin (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

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

Similar Threads

  1. Replies: 18
    Last Post: 08-09-2018, 06:45 AM
  2. Replies: 2
    Last Post: 03-30-2018, 11:33 AM
  3. Export Data from Access 2003 Table to SQL Server 2012 Table.
    By Robeen in forum Import/Export Data
    Replies: 3
    Last Post: 02-26-2016, 02:07 PM
  4. Replies: 1
    Last Post: 09-03-2014, 10:48 AM
  5. Replies: 7
    Last Post: 08-20-2013, 06:03 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