DAO adding 1 new field to 2 existing tables

[JonAccess]

Before the experts ask why I would want to add fields via VBA, I am not building a typical database. I am importing large amounts of data into Access, having Access manipulate the data via queries and such, and exporting out. I use Access for the same steps pretty much everytime.

(Bare with me - I don't have access to my database right now)
My issue is that I am getting an error message in my code on the 2nd "add field to table" section of code. The error is 3265 Item not found in this collection.

I used code similar to this after I ran a make table query using db.execute "myquery" method:

Dim db As DAO.Database 
Dim tdf As DAO.TableDef 
Dim fld As DAO.Field      
Set db = CurrentDb() 

Set tdf = db.TableDefs("MYtable")  
tdf.Fields.Append tdf.CreateField("MYfield", dbText, 30)

That code works fine, but I run another make table query and run the same type of code to add a field as I previously did. The error stops on

Set tdf=db.TableDefs("MyOtherTable")

I figure I have to empty tdf but i tried setting tdf = nothing but it didnt help. Do i have to db.close and db = nothing too???

What am I missing? Thanks in advance!

 

[Galaxiom]

Check the spelling of "MyOtherTable"

 

[JonAccess]

Thanks for the reply. I checked the spelling numerous times to no avail. What I did as a temporary fix was:

 dim db as DAO.database
dim fld as DAO.Field
dim tdf as DAO.TableDef
dim db2 as DAO.database
dim fld2 as DAO.Field
dim tdf2 as DAO.TableDef

And I used the first set of variables with the first "add field" code and the 2nd set of variables with the 2nd "add field" code. It actually worked like that, but I know that is bad practice. Any ideas?

FYI - I'll double check tomorrow morning with a fresh set of eyes about the spelling to be sure.

 

[vbaInet]

How about:

Dim db As DAO.Database 
Dim tdf As DAO.TableDef 
Dim fld As DAO.Field

Set tdf = CurrentDb.TableDefs("MYtable")  
tdf.Fields.Append tdf.CreateField("MYfield", dbText, 30)

Set tdf = CurrentDb.TableDefs("MyOtherTable")

 

[JonAccess]

Thanks! looks a lot cleaner. Unfortunately...

Dim db As DAO.Database 
Dim tdf As DAO.TableDef 
Dim fld As DAO.Field
 
Set tdf = CurrentDb.TableDefs("MYtable") 
[B][COLOR=darkred]tdf.Fields.Append tdf.CreateField("MYfield", dbText, 30)[/COLOR][/B]

... Gives me an error. It fails on red line and gives error "Object invalid no longer set"

I replace CurrentDb with the "db" and it works. ( I haven't made it to the second "add field" code yet)

Any ideas?

 

[vbaInet]

Try this and if it works we can start cutting down:

Dim db As DAO.Database 
Dim tdf As DAO.TableDef 
Dim fld As DAO.Field
    
Set db = CurrentDb() 

Set tdf = db.TableDefs("MYtable")  
tdf.Fields.Append tdf.CreateField("MYfield", dbText, 30)

tdf.Fields.Refresh
db.TableDefs.Refresh

Set tdf = Nothing
db.Close
Set db = Nothing

Set db = CurrentDb()

Set tdf = db.TableDefs("MyOtherTable")

By the way, have you got any other code between the failing line and the line above it?

 

[JonAccess]

Your code works like a charm when I only run those 2 peices. But when I put it in the middle of my code, it gives me the "Item not found in this collection" error on the red line below. Here is my whole code, because i know something bad happens in between the two sections of code you provided.

Private Sub Query_1_Click()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
 
Set db = CurrentDb()
 
db.Execute "(1) 7601---itcl", dbFailOnErrorDim [COLOR=seagreen]' Update Query[/COLOR]
db.Execute "(2) 7601---price", dbFailOnErrorDim [COLOR=seagreen]' Update Query[/COLOR]
db.Execute "(3) 7601 by Item Class", dbFailOnErrorDim [COLOR=seagreen]' Make Table Q[/COLOR]
 
Set tdf = db.TableDefs("7601 by item class") [COLOR=seagreen]' Add Field to made table[/COLOR]
tdf.Fields.Append tdf.CreateField("GM%", dbText, 30) 
 
db.Execute "(4) 7601 by Item Class---GM %", dbFailOnErrorDim [COLOR=seagreen]' Update[/COLOR] [COLOR=seagreen]new field[/COLOR]
db.Execute "(5) 7601 by Part Number", dbFailOnErrorDim [COLOR=seagreen]' Make Table Q[/COLOR]
 
[COLOR=darkred][B]Set tdf = db.TableDefs("7601 by part number") [/B][/COLOR][COLOR=seagreen]'Add field to made[/COLOR] [COLOR=seagreen]table[/COLOR]
tdf.Fields.Append tdf.CreateField("GM%", dbText, 30)

(inherited query names)

Anything obvious? In the mean time I'll try to empty and refresh those variables while running the full code.

 

[vbaInet]

So what I would like to see is a consolidated code, your code and mine and tell us where it fails. That would be more helpful.

 

[JonAccess]

The code I posted is the consolidated code. Nothing is left out (well except stuff AFTER the failed line) and I know the rest of the code works because it's just a bunch more db.execute.

So the code runs up until it hits that line i colored in red. All my code is in there before the error. Is that what you were unclear on? Not being sure if there was code in between that I left out?

 

[vbaInet]

There are 6 lines of code I included in my last set of code. Add those lines, test and post back your findings.

 

[JonAccess]

vbaInet -
Your original code worked so I simply got rid of stuff until I couldn't anymore. the culprit was setting db = Currentdb() again (shown in blue below). Thank You! I'll mark as answered.

- Just curious, should I "db = Nothing" before setting back to Currentdb() even if it isn't "needed"?

Successful Code:

Private Sub Query_1_Click()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
 
Set db = CurrentDb()
 
db.Execute "(1) 7601---itcl", dbFailOnErrorDim [COLOR=seagreen]' Update Query[/COLOR]
db.Execute "(2) 7601---price", dbFailOnErrorDim [COLOR=seagreen]' Update Query[/COLOR]
db.Execute "(3) 7601 by Item Class", dbFailOnErrorDim [COLOR=seagreen]' Make Table Q[/COLOR]
 
Set tdf = db.TableDefs("7601 by item class") [COLOR=seagreen]' Add Field to made table[/COLOR]
tdf.Fields.Append tdf.CreateField("GM%", dbText, 30) 
 
db.Execute "(4) 7601 by Item Class---GM %", dbFailOnErrorDim [COLOR=seagreen]' Update[/COLOR] [COLOR=seagreen]new field[/COLOR]
db.Execute "(5) 7601 by Part Number", dbFailOnErrorDim [COLOR=seagreen]' Make Table Q[/COLOR]
 
[COLOR=blue][B]Set db = CurrentDb() [/B][/COLOR]
 
[COLOR=darkred][COLOR=black]Set tdf = db.TableDefs("7601 by part number")[/COLOR][/COLOR][COLOR=seagreen]'Add field to made[/COLOR] [COLOR=seagreen]table[/COLOR]
tdf.Fields.Append tdf.CreateField("GM%", dbText, 30)Set db = CurrentDb()

 

[JonAccess]

How do I mark answered???

 

[vbaInet]

Rather than just db = Nothing, close it before setting it to Nothing.

db.Close
set db = nothing

That functionality doesn't exist on here

 

[JonAccess]

Thanks a million