Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    nd0911 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    31

    Export Excel table value's to Access table useing VBA ADODB

    Hello,

    I have two identical table's (fields name) one table in Excel and one table in Access.



    I have values in the Excel file that I want to export to the Access table (both of them with the same name) by useing Ado.

    Everithing with the Ado connections works fine, my problem is with the Sql string.

    My Sql string look's like this:


    Code:
    Private Function ImportAllTable_SqlString(tblName As String, Lbj As ListObject) As String
    
    ImportAllTable_SqlString = "INSERT INTO " & tblName & _
                                " SELECT *" & _
                                " FROM [" & Lbj & "]"
    
    
    Debug.Print ImportAllTable_SqlString
    End Function

    I will be happy for same halp.

    Thank you

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    why do you need to use ADO?
    connect the excel as a table, then run an append query.

    or
    use Transferspreadsheet.

    NO code needed.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    is your code in excel, exporting to access? or in access, importing from excel?

    And when you say you have a problem - what is that exactly?

  4. #4
    nd0911 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    31
    Hi ranman,

    I need it with Ado because I use Access as a close Database, and this "Excel table" updates the "Access table" automaticly.

  5. #5
    nd0911 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    31
    Hi Ajax,

    My code is in excel exporting to Access (the access without any code at all).

    The problem is that Im getting a runtime error because of the Sql statment.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the problem is with lbj which is a listobject. You haven't said what the listobject is and you haven't referenced any property of it that can be assigned to a string.

    If it is a listbox, there should be an assigned cell to store the selected value and that is what you should be referencing. Without knowing what it is and it's structure, not possible to advise further.

  7. #7
    nd0911 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    31
    Thanks Ajax,

    Here is the full code:

    Code:
    Sub Test
    Dim DbConn As ADODB.Connection
    Dim Rs As ADODB.Recordset
    Dim Pth As String: Pth = Range("o2").Value
    
    
    OpenDatabase = True
    
    
    
    
    Set DbConn = New ADODB.Connection
    Set Rs = New ADODB.Recordset
    
    
    DbConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Pth & ";Persist Security Info=False;"
    
    With Rs
        .ActiveConnection = DbConn
        .Source = "INSERT INTO " & AccessTableName & " SELECT *" & " FROM " & ListObjects("ExcelTableName")
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset  
        .Open
    End With
    
    
    
    
    
    DbConn.Close
    Set DbConn = Nothing
    End sub
    this code sitting in Excel the VBE and supposed to copy all the information that I have in the Excel table and past it in the Access table that have the same field's name's in the same order like the Excel table have.

    I hope now I was more clear

    Thank you again.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you don't appear to have picked up my comment

    If it is a listbox, there should be an assigned cell to store the selected value and that is what you should be referencing.

    Also, listobjects are local to a worksheet, so you should be referencing the listobjects collection of a specified worksheet

    see this link about listobjects

    https://msdn.microsoft.com/en-us/lib.../ff197604.aspx

    the assigned cell will be the range property

  9. #9
    nd0911 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    31
    My table is a Listobject if thats what you asking NOT a listbox.

    I'm sorry if I wasn't clear about it, my english is not that good

    can you please give me an exaple how to write this thing ?

    Thank you again.

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    a listobject can be many things, including a listbox - did you look at the link I provided?

    can't help without knowing what your listobject is and some example data

  11. #11
    nd0911 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    31

    I attached an image

    Please tell me if you need more information


    Click image for larger version. 

Name:	2017-06-18_16h51_39.jpg 
Views:	12 
Size:	38.0 KB 
ID:	29152

    thank you

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    see second paragraph of post #6.

    When you created this you will have supplied an input range - you should also have supplied a cell link - that is where the seleceted value us stored

    Click image for larger version. 

Name:	Capture.JPG 
Views:	11 
Size:	24.4 KB 
ID:	29153

  13. #13
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Alternatively is this a table or just a filter set. Not possible to tell from what is supplied. But assume you have created a table object called ExcelTableName

    The problem is, the user can select more than one.

    You need to loop through each row and see if the cell is visible or not - and if visible, triggers your sql insert code. see this link for code to do that. https://excel.tips.net/T002286_Skipp...n_a_Macro.html

    normal way to reference a table is

    worksheets("worksheetname").listobjects("nameoftab le").range

    which you can then apply to the linked code as your selection

  14. #14
    nd0911 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    31
    Hi Ajax,

    Thank you for answer.

    I'm reading your instractions and I dont understand the connection bitween your instractions and my quastion.

    What I need is just a SQL line of code that copy the entier Excel table listobject/range (NOT a ListBox) to Access table.

    I know how to do it with Loop, I run over all the cells in the excel range and copy them to the access table but I want to do it with one Sql statment that copy the entier range as a "one block" and put it in the access table.

    Note, the Access table will always be empty before the procedure and the excel table will always be full with data, and no filters/sorting and what so ever.

    I hope now I was more clear

    Thank you again.

  15. #15
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I can only refer you to the last part of my last post which would apply

    .Source = "INSERT INTO " & AccessTableName & " SELECT *" & " FROM " & worksheets("worksheetname").listobjects("nameoftab le").range

    you will need to supply the worksheetname and determine the nameoftable

    However I'm not convinced it will work since I don't think ADO will recognise an excel table as a valid ADO table

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-07-2016, 04:36 AM
  2. Cant Export data to specific excel sheet by ADODB
    By rangga_osh in forum Import/Export Data
    Replies: 4
    Last Post: 03-14-2013, 04:20 AM
  3. Export columns (table) from access to excel
    By dacodac in forum Programming
    Replies: 4
    Last Post: 01-30-2013, 04:27 AM
  4. Export to Excel from Pivot Table in Access 2010
    By nicon2k in forum Import/Export Data
    Replies: 0
    Last Post: 04-10-2012, 07:02 AM
  5. Export Access Pivot Table to Excel
    By graviz in forum Programming
    Replies: 1
    Last Post: 11-13-2009, 07:30 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