Results 1 to 10 of 10
  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 online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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 offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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 online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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 online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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 online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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 MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    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, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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
  •  
Other Forums: Microsoft Office Forums