Results 1 to 12 of 12
  1. #1
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150

    MS Access - "Item Not Found In This Collection - REALITY: Item does exist in the collection

    Hi friends,

    If this isn't the second time Access VBA has failed me this weekend with strange behavior... You can read about another unsolvable code issue here if you'd like:

    https://www.excelforum.com/excel-pro...-the-file.html

    On to the problem... The code below is a snippet from a function that mass produces tables + fields for testing - all inputs are set by form objects, but that is beside the matter. The fundamental process that takes place is:

    1.) set db depending on what user chooses (current DB or from file path) 'THIS PARTICULAR PROBLEM IS WITH SETTING DB OBJECT TO REFERENCED FILE PATH (BACKEND)
    2.) check if table(s) exist
    3.) If yes, delete them.


    4.) Create new tables with specified name
    5.) Add fields
    6.) Complete


    CODE (REDUCED AND SANITIZED)


    Code:
    Private Sub TableCreation()
    Dim t As dao.TableDef
    Dim tableIt         As Variant 'ignore null
    Dim tName           As String
    Dim fType           As Integer
        
    If cbSource.Value = "[Path]" Then
        Set db = OpenDatabase(tbPath.Value)             ' SET TO BACKEND
    Else
        Set db = CurrentDb
    End If
    
    
    
    
    For i = 1 To tableIt
        x = 0
        If IsTable(tName & i, db) = True Then           'NOTE:  isTable is a custom function where args are table name and the db object to look in
            x = x + 1
        End If
    Next i
    
    
    If x > 0 Then
        If MsgBox("Table(s) already exist in database - Overwrite?", vbYesNoCancel) = vbYes Then
            
            For i = 1 To tableIt
            If IsTable(tName & i, db) = True Then db.TableDefs.Delete (tName & i)        'this executes fine...
            
            Next i
        Else
         Exit Sub
        End If
    End If
    
    
    db.Execute "CREATE TABLE " & tName & i & "(ID TEXT PRIMARY KEY);"             'AND THIS EXECUTES FINE - P.S. I deleted the loop container for demo purposes
    
    
    
    
    For i = 1 To tableIt
        MsgBox db.Name & "  " & tName & i          'RETURNS THE EXACT NAME OF THE TABLES IN MY DB OBJECT THAT WERE JUST CREATED (BACKEND)
        Set t = db.TableDefs(tName & i)          'RETURNS ITEM NOT FOUND IN COLLECTION
           ....
           ...
           ..
           .
           .
    End Sub
    So, to reiterate what the code is saying.... The DB object in this particular case is set to my backend file. The code looks for the table names. If they are found in the database object, then delete them. They are created fresh. And when setting the tabledef object, the items are somehow not found in the collection...

    The DB object has not changed and the tables with the exact name physically exist in the target database object. So I am confused here.



    Anyone have a clue?
    Thanks for any help in advance.

    Regards

  2. #2
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150
    Further Information:

    So, just by curiosity, I ran a msgbox loop on tabledef.name for each in the collection...

    And to no surprise the tables that I can see right in front of my eyes inside the database are not returning....

    hmmmmm

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    i isn't declared (as far as I can see), so when it errors what is i? It could be outside of the tabledefs.count which would explain the error message.
    You do have Option Explicit at the top of this module? Maybe you just eliminated the Dim statement for i...

    db.TableDefs(tName & i)
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150
    Micron,

    Thanks for the reply..
    Yes there is missing info. These exclusions are not to be concerned with, the code is reduced for demonstration. Obvious syntax errors have no impact on the problem at hand. I reduced a lot of the assignment at the top of the module.

    To explain about i:, i is a user-defined integer set in the form. Likewise, "tName" variables are user-defined string set in the form. Unless I am missing something, neither of these have any relation to the count of the db.TableDefs collection because I am referencing the TableDefs collection object via the table name...

    Code:
    db.TableDefs("Table1")
    versus...
    db.TableDefs(1)
    There is a bug here. I am looking right at the tables, right in front of my eyes. And somehow my front-end code can't find them?

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I missed that - thought it was the latter. Afraid I can't spot the issue having never done this so I'm limited. Probably worse, every time I wonder about an aspect of it, the relevant code is missing.

  6. #6
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150
    Micron, I assure you posting the complete code would only confuse you more (take no offense please) - Loops on top of loops. I made a best judgment call to reduce it. If that is in fact what is required, then I will do that.

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think your problem might be caused by deleting the tables and therefore altering the indexes in the tabledefs collection(s). I remember reading something to this effect in a a Access 97 Handbook but I can't seem to find anything online quick enough tonight. In any case can you change your loops to loop backwards and see if you still get the errors?

    Cheers,
    Vlad

  8. #8
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150
    Vlad,
    Thanks for offering your input.

    Interesting.. There is certainly something "behind the scenes" going on. Any resources you can find on the indexes, I would appreciate.

    I suppose there is no way I can loop backwards. My logic is such that the tables are deleted if exists. I will however, tell you something very interesting and that is i am currently testing with a back-up front-end prior to code changes. I can write tables to the current backend just fine... The code is slightly different, but these differences are negligible and have no relation to the issue at hand. The core code is the exact same. I am comparing side by side...


    p.s i've done some compact & repairs with no luck.


    EDIT:
    OK - so i've rolled back the code for this module from the backup - with the same code, this error still persists.

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    How about you try to add a tabledefs.refresh after you delete the (existing) tables:
    Code:
    If x > 0 Then
        If MsgBox("Table(s) already exist in database - Overwrite?", vbYesNoCancel) = vbYes Then
            
            For i = 1 To tableIt
            If IsTable(tName & i, db) = True Then db.TableDefs.Delete (tName & i)        'this executes fine...
            
            Next i
        Else
         Exit Sub
        End If
    End If
    db.Tabledefs.Refresh

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Also try to add the tabledefs.refresh after your createtable:

    Code:
    db.Execute "CREATE TABLE " & tName & i
    db.tabledefs.refresh
    Cheers,
    Vlad

  11. #11
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150
    How about you try to add a tabledefs.refresh after you delete the (existing) tables:
    Pardon my enthusiasm...
    ..
    ..
    ..

    YES!!!!!! Thank you so much Vlad! That appears to be the problem. I didn't realize there even was a refresh method!! Geeee, that was a long 4 hours of frustration. Thank you again, sir. I greatly appreciate your help!



  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Been there done that... many, many, many times.....
    Glad to hear you got it working!

    Cheers,
    Vlad

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

Similar Threads

  1. Item not found in this collection
    By Niko in forum Forms
    Replies: 12
    Last Post: 10-13-2017, 03:13 PM
  2. Error "item not found in this collection"
    By andy49 in forum Queries
    Replies: 7
    Last Post: 11-12-2016, 12:46 PM
  3. Item Not Found In This Collection (ListBox)
    By Voodeux2014 in forum Forms
    Replies: 8
    Last Post: 10-19-2015, 11:09 AM
  4. item not found in this collection
    By rockell333 in forum Queries
    Replies: 1
    Last Post: 09-24-2015, 03:20 PM
  5. Item Not Found In Collection For TableDefs
    By gammaman in forum Modules
    Replies: 2
    Last Post: 06-17-2015, 07:55 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