dcsimg
Results 1 to 15 of 15

Thread: Excel interfacing with Access via ADO - recordset error - Item cannot be found

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    7

    Excel interfacing with Access via ADO - recordset error - Item cannot be found

    Hi everyone!

    Apologies if this topic has been discussed previously, I couldn't find it.

    I'm new to the world of ADO and I have an issue that is leaving me flummoxed...

    I've created a database (it has fields, but no records) and I'm now trying to push data from excel to it.

    I've used ADO to create a recordset and then used AddNew to add a new record to the "Options" table. I'm then trying to add a value to this new record by using it's index, however I keep getting the below error:
    "Run-time error '3265':
    Item cannot be found in the collection corresponding to the requested name or ordinal."

    From what I've read people say it's either misspelling (I've checked, it's not) or I'm not specifying the field correctly.

    Any one got any idea?

    Cheers.


    Nic

    Code:
    Sub PushTableToAccess()
    
        Dim first_rw As Long:                   first_rw = 4
        Dim pow_ws As Worksheet:                Set pow_ws = ThisWorkbook.Worksheets("POW")
        Dim tbl_clms As Long:                   tbl_clms = pow_ws.Cells(first_rw - 1, 16384).End(xlToLeft).Column
        Dim cnn As ADODB.Connection
        Dim MyConn
        Dim rst As ADODB.Recordset
        Dim i As Long, j As Long
        Dim tbl_rw As Long
        
        pow_ws.Activate
        tbl_rw = Range("A65536").End(xlUp).Row
    
        Set cnn = New ADODB.Connection
        MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
        
        With cnn
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .Open MyConn
        End With
        
        Set rst = New ADODB.Recordset
        rst.CursorLocation = adUseServer
        rst.Open Source:="Options", ActiveConnection:=cnn, CursorType:=adOpenDynamic, LockType:=adLockOptimistic, Options:=adCmdTable
        
        'Load all records from Excel to Access.
        For i = 4 To tbl_rw
            rst.AddNew
            For j = 1 To 13 'tbl_clms
                rst(Cells(1, j).Value) = pow_ws.Cells(i, j).Value
            Next j
            rst.Update
        Next i
        
        ' Close the connection
        rst.Close
        cnn.Close
        Set rst = Nothing
        Set cnn = Nothing
    
    End Sub
    Last edited by duftnich03; Aug 29th, 2018 at 08:56 AM.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,118

    Re: Excel interfacing with Access via ADO - recordset error - Item cannot be found

    Welcome to VBForums

    When the error occurs, what is the value of Cells(1, j).Value ? You can check by either selecting it with the mouse (a tooltip should appear with the value), or by adding it as a Watch

    Whatever the value is, it is that field which is the problem.

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    7

    Re: Excel interfacing with Access via ADO - recordset error - Item cannot be found

    Thanks

    I'd come to the same conclusion that rst(Cells(1, j).Value) = pow_ws.Cells(i, j).Value errors out due to Cells(1, j).Value.

    What am I missing though? As I understand it Cells(1, j).Value is merely the row of the new record I've created and the field reference (j) of that new record?
    Last edited by duftnich03; Aug 29th, 2018 at 09:08 AM.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,118

    Re: Excel interfacing with Access via ADO - recordset error - Item cannot be found

    pow_ws.Cells(i, j).Value is the value you are trying to put into the field

    rst(Cells(1, j).Value) is the field you are trying to put it in, with Cells(1, j).Value being either the name or index of the field (hopefully the name).

    What you are missing is knowing what the value of Cells(1, j).Value is when the problem occurs... without knowing that we can only guess as to what the cause of issue is, and even an educated guess is very likely to be wrong.

  5. #5

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    7

    Re: Excel interfacing with Access via ADO - recordset error - Item cannot be found

    I feel my brain is on the cusp of understanding, but it is missing something that it needs...

    My excel data looks like:
    Name:  Excel.PNG
Views: 72
Size:  3.6 KB

    My Access data looks like:
    Name:  Access.PNG
Views: 71
Size:  1.9 KB

    So rst(Cells(1, j).Value) should be the 'Field_ID' value, which according to my excel data should be 'SS19Clothing1' in the first instance and so on as it cycles through.

    Thanks for your help!

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,118

    Re: Excel interfacing with Access via ADO - recordset error - Item cannot be found

    Don't guess at what it is, find out for certain:
    Quote Originally Posted by si_the_geek View Post
    When the error occurs, what is the value of Cells(1, j).Value ? You can check by either selecting it with the mouse (a tooltip should appear with the value), or by adding it as a Watch

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,435

    Re: Excel interfacing with Access via ADO - recordset error - Item cannot be found

    By any chance: Did you confuse Row- and Column-Index?
    Cells(Row, Column) is the Syntax
    With Cells(1,j) you're always looking in the first row of the sheet

    EDIT: acc. to your screenshot: have you tried Cells(3,j)?

    Just looked at it again: I'm pretty sure it's Cells(1,j) and here the "1" for your Row-Index.
    Change it to 3 or first_rw-1
    Cells(3,j) or Cells(first_rw-1,j)
    Last edited by Zvoni; Aug 29th, 2018 at 10:07 AM.
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  8. #8
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,231

    Re: Excel interfacing with Access via ADO - recordset error - Item cannot be found

    Hi,

    why not create a -LinkTable- of the Excel sheet in Access.

    then you have the complete Excelsheet there and copy what you want to a Table in Access

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  9. #9

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    7

    Re: Excel interfacing with Access via ADO - recordset error - Item cannot be found

    Hi All

    Apologies for late response, I had a bereavement.

    Chris - I had considered this, but The Excel file will operate as a window into the data for users who won't see the Access data or don't use or understand Access as a program. As such the data will be input/edited in Excel and then pushed to the Access table when the Excel user clicks a button.

    The first field in my Access table is a primary key field and is setup as 'short text', so the 'SS19Clothing1' value I'm importing from Excel should be fine. Does ADO require it to be an integer? I assume not as, although the preferred method, it's not a requirement usually within databases?

    Cheers.


    Nic

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,435

    Re: Excel interfacing with Access via ADO - recordset error - Item cannot be found

    Quote Originally Posted by duftnich03 View Post
    The first field in my Access table is a primary key field and is setup as 'short text', so the 'SS19Clothing1' value I'm importing from Excel should be fine. Does ADO require it to be an integer? I assume not as, although the preferred method, it's not a requirement usually within databases?
    No, the only requirement for a primary key is to be unique per row for the table. A primary key can even consist of 2 or more fields.
    Why so many (read most) of DB-Developers use an (unsigned) Integer as a primary key (usually with Auto-Increment): When INSERT-ing a new row you don't have to check if it already exists.
    Otherwise you have to implement some Error-trapping.

    EDIT: There is a downside to Primary Key as unsigned integer with Auto-Increment: You have an ironclad upper limit how many rows you can insert into the table.
    Reaching this limit, you usually "vacuum" the table/database, and do a re-indexing/re-creation of the table.
    Personally i've never reached that limit, and right now i wouldn't even know, how to proceed if such a table is still "full" after vacuum/re-index/re-creation.
    Last edited by Zvoni; Sep 10th, 2018 at 04:25 AM.
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  11. #11

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    7

    Re: Excel interfacing with Access via ADO - recordset error - Item cannot be found

    Right, I've solved it!

    The issue was below:
    Code:
        'Load all records from Excel to Access.
        For i = 4 To Rw
        rst.AddNew
            For j = 1 To 13
                rst(Cells(1, j).Value) = Cells(i, j).Value
            Next j
            rst.Update
        Next i
    The line rst(Cells(1, j).Value) = Cells(i, j).Value is the issue, the rst(index) part is wrong and should be the title row in Excel.
    I assumed it was referring the to recordset I had just created itself, but infact it does not it is the field name we are about to update.

    so the line should read rst(Cells(3, j).Value) = Cells(i, j).Value (in my case as my field names are in row 3 in Excel).

    Posting my solution here in case anyone else is being as daft as I was!

    Cheers all.


    Nic

  12. #12
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,435

    Re: Excel interfacing with Access via ADO - recordset error - Item cannot be found

    You do realize, that is exactly what i wrote in Post #7?
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  13. #13

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    7

    Re: Excel interfacing with Access via ADO - recordset error - Item cannot be found

    Zvoni

    Bugger, you are right and you did.

    As I said I've been distracted with a bereavement in my immediate family and on the day you posted I had just found out my son died.

    Thanks for you help though, it is appreciated.


    Nic

  14. #14
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,435

    Re: Excel interfacing with Access via ADO - recordset error - Item cannot be found

    Sorry mate,

    my condolences....
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  15. #15

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    7

    Re: Excel interfacing with Access via ADO - recordset error - Item cannot be found

    Cheers, you weren't to know.

    Again, thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width