By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,377 Members | 3,038 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,377 IT Pros & Developers. It's quick & easy.

runtime error 3127 on two tables with the same structure

P: 80
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.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO MAX_FULLNAME_DUMP SELECT * FROM table WHERE len(Trim(Fullname)) in (Select MaxLen from Max_Fullname)
Sep 3 '08 #1
Share this Question
Share on Google+
4 Replies


puppydogbuddy
Expert 100+
P: 1,923
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.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO MAX_FULLNAME_DUMP SELECT * FROM table WHERE len(Trim(Fullname)) in (Select MaxLen from Max_Fullname)
Shouldn't "table" be Max_Fullname?

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO MAX_FULLNAME_DUMP 
  2. (SELECT * FROM Max_Fullname
  3. WHERE len(Trim(Fullname)) in 
  4. (Select MaxLen from Max_Fullname))
  5.  
Sep 4 '08 #2

P: 80
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.
Sep 11 '08 #3

P: 80
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.
Sep 11 '08 #4

P: 80
Okay, figured it out; stupid error. Instead of

Expand|Select|Wrap|Line Numbers
  1.  Set dbs = CurrentDb 
I had

Expand|Select|Wrap|Line Numbers
  1.  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.
Sep 11 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.