Results 1 to 11 of 11
  1. #1
    Demarc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    21

    Assign an excel Array as a new record in Access table

    Hi there,



    I am trying to move six one dimensional arrays from excel and place them as a new record in their corresponding tables in Access. I know how to assign each piece of data individually but each array, and their corresponding tables in Access are about 200 columns and it would be very tedious.

    The information in the arrays is not taken from tables but from all over a very complicated worksheet.

    Is there a way to assign the array directly? i.e.

    tblName.newrecordset = MyArray

    If there is and someone can point me in the direction of some example code that would be great, other wise what would be the best way to achieve this task?

    Kind regards,
    Marcus

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    can you not just import the excel data via Docmd.Transferspreadsheet?

  3. #3
    Demarc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    21
    Not sure what that is but but gives me something to google as I cant find anything about transferring arrays.

    Cheers,
    Marcus

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101

  5. #5
    Demarc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    21
    Thanks Gicu I should be able to get something from these links that works.

    Kind regards,
    Marcus

  6. #6
    Demarc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    21
    Ahhhh, why is this so difficult!!

    I'm sure there is an easy answer but I am somehow missing it. When I try to add the array I need to a table it adds in diagonally, i.e. instead of placing 200 records in one row of the record set it places each one in a new column but 1 row below the last.

    This is the code I am using:

    Code:
    Call openConnection
    
    cn.Open strConnection
    Set rs = New ADODB.Recordset
    rs.Open "tblNL1Dash", cn, adOpenKeyset, adLockOptimistic
    
    For i = LBound(DashArray) To UBound(DashArray)
        With rs
            .AddNew
            .Fields(i) = DashArray(i)
            .update
        End With
    Next i
    
    Call closeConnection
    I also tried to write the array to a worksheet and then read it into the Access table using this code with the same result:

    Code:
    Set WriteRng = NDM.Range("NDM_tblNL1Dash").offset(1, 0).Resize(1, DashCtr)
    WriteRng = DashArray
    
    Call openConnection
    
    cn.Open strConnection
    Set rs = New ADODB.Recordset
    rs.Open "tblNL1Dash", cn, adOpenKeyset, adLockOptimistic
    
    For i = LBound(DashArray) To UBound(DashArray)
        With rs
            .AddNew
            .Fields(i) = NDM.Range("NDM_tblNL1Dash").offset(1, i - 1)
            .update
        End With
    Next i
    
    Call closeConnection
    I simply want the data written into on new record set into the table. Any ideas???

    Also in the sites that Gicu provided above I found this code:

    Code:
    rs.AddNew Array(Field1,Field2), Array(Value1,Value2)
    and tried to add the array directly using:

    Code:
    Call openConnection
    
    cn.Open strConnection
    Set rs = New ADODB.Recordset
    rs.Open "tblNL1Dash", cn, adOpenKeyset, adLockOptimistic
    
        rs.AddNew DashArray
        rs.update
    
    Call closeConnection
    This would be my preferred solution however I get an error "Arguments are of the wrong type ......."

    Any ideas on how to make this option work.

    Kind regards,
    Marcus

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Marcus,

    As you are discovering Excel and Access are 2 separate animals built on different object models.
    I am not an Excel person, but I'd like to see an example of the data you have and what you want to "convert" it to.
    I don't need an exhaustive Excel file, but a few records/samples of what in your array, and what you perceive it should appear like in Access.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Marcus,

    Try this:

    Code:
    Call openConnection
    
    
    cn.Open strConnection
    Set rs = New ADODB.Recordset
    rs.Open "tblNL1Dash", cn, adOpenKeyset, adLockOptimistic
    With rs
    .AddNew
    
    For i = LBound(DashArray) To UBound(DashArray) 
    .Fields(i) = DashArray(i)
    Next i
    .update
    End With
    
    Call closeConnection

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    Demarc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    21
    Sorry I didn't get back to you, been traveling and then this COVID-19 issue has had me away from the computer for a little bit.

    Vlad, that did the trick, works perfectly!! Thanks for your help.

    Orange, the data was a simple Array of various types (integer,string,double,date,etc,etc ...... x 200) that I read into the array in excel and I just wanted it place into an Access table that I had created, written out in the same order as the array as one recordset from left to right. Ideally I wanted to just assign the array but looping works fast enough in this instance.

    Thanks everyone for you help and again sorry for the delay in getting back.

    Kind regards,
    Marcus

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Marcus, glad to hear you got it working!

    Stay safe!

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 1
    Last Post: 11-15-2019, 04:04 PM
  2. Replies: 3
    Last Post: 02-26-2019, 07:27 PM
  3. Replies: 16
    Last Post: 08-14-2015, 05:32 PM
  4. Replies: 5
    Last Post: 03-21-2015, 01:42 PM
  5. Replies: 7
    Last Post: 04-17-2012, 11:53 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