runtime error 3127 on two tables with the same structure

[rando1000]

I'm trying to add a field to a bunch of tables that are used to pull the longest record for each field. The program has been working fine for over a year. Now, when I add the field, I get the 3127 error. However, when I check the table for the missing field, the field is there.

In fact, if I go into vba, return the variable containing the full SQL string, copy that into a query Window back in access, and run it with no modifications, IT RUNS! What the heck? Obviously, the two tables are the same, else a query window would error out too.

Here's the SQL statement, thought it works.

INSERT INTO MAX_FULLNAME_DUMP SELECT * FROM table WHERE len(Trim(Fullname)) in (Select MaxLen from Max_Fullname)

[puppydogbuddy]

I'm trying to add a field to a bunch of tables that are used to pull the longest record for each field. The program has been working fine for over a year. Now, when I add the field, I get the 3127 error. However, when I check the table for the missing field, the field is there.

In fact, if I go into vba, return the variable containing the full SQL string, copy that into a query Window back in access, and run it with no modifications, IT RUNS! What the heck? Obviously, the two tables are the same, else a query window would error out too.

Here's the SQL statement, thought it works.

INSERT INTO MAX_FULLNAME_DUMP SELECT * FROM table WHERE len(Trim(Fullname)) in (Select MaxLen from Max_Fullname)

Shouldn't "table" be Max_Fullname?

INSERT INTO MAX_FULLNAME_DUMP 
(SELECT * FROM Max_Fullname
WHERE len(Trim(Fullname)) in 
(Select MaxLen from Max_Fullname))
 

[rando1000]

Yes, but that code is only wrong in my example. I guess I started to genericize the code and only changed one instance. My working code reflects your suggestion.

[rando1000]

Okay, no something is really odd. I'm getting runtime 3078 errors for files I KNOW are in the database, and table-already-exists errors for tables that have been deleted. I check the MsysObjects table and it's correct, but my form doesn't seem to be seeing what objects exist correctly.

[rando1000]

Okay, figured it out; stupid error. Instead of

 Set dbs = CurrentDb 

I had

 Set dbs = "C:\MyDatabase.mdb" 

Since the changes I was making have to be made in a second copy to keep the live copy working, I was looking at tables in a totally different database. Duh.