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)
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...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
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