"Field is too small ..." when running append query in Access 2003

[Jon Ley]

Re-post of question from a couple of weeks ago (got no responses).

I have an append query that is inserting data into a temporary table. When
running it from certain machines, which just happen to have Access 2003
installed, I get error 3163 (The field is too small to accept the amount of
data you attempted to add. Try inserting or pasting less data). If however I
open this query in datasheet view, I can successfully select all the records
and copy/paste them to the temp table.

Running the same append query, with the same data, from Access 2002 works
fine.

What's going on?!

 

[[MVP] S.Clark]

If it's not corruption, then I'd have to guess that one of the source data's
fields has data that is longer than the destination table's field.

 

[Jon Ley]

Steve,

Thanks for taking the time to look at my post. Not sure where any corruption
could be. The system is a front end / back end set up, with the latest front
end in a central location that users copy to their local machines (so the
central one never gets opened once it's been released). The user in question
has re-copied the front end but still gets the same problem. Other users
running the same front end do not get the problem. I don't think it's data
corruption, again because other users can successfully run the same append
query on exactly the same data. Also, for the same reason, I know that it's
not an issue with data lengths being mismatched. The only common difference
that I can find between machines that will run it and ones that won't is the
Access version - works on 2002 but not on 2003.

Any further thoughts?

Regards,

Jon.

 

[John Spencer]

When things work on one computer and don't on another I always suspect that
I have a references problem.

To do its job, Access (like most modern programs) makes use of various
external program and object libraries that provide functionality that may be
shared among applications. For example, Access always uses the Visual Basic
for Applications library, the version-appropriate Access Object Library, and
the OLE Automation library. References to the specific library files,
including their locations, are stored with your database. But these library
modules may not be in the same location on different machines, especially if
they have different versions of Microsoft Office. If you move a database
from one machine to another, these references may be "broken"; that is, one
or more of the library files may not be where the stored reference says it
is.

When this happens, you need to take steps to let Access repair the broken
reference(s) ON THE COMPUTER WHERE THE FAILURE IS OCCURING.

Here are MVP Doug Steele's instructions for how to do it:

*** Quote ***

Any time functions that previously worked suddenly don't, the first thing to
suspect is a references problem.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Just so you know: the problem will occur even if the library that contains
the specific function that's failing doesn't have a problem.

**** End Quote ****

So, follow those instructions and see if your problem goes away.

Check out the following reference
http://www.trigeminal.com/usenet/usenet026.asp?1036

 

[tcoles]

Jon Ley,
Did you confirm that this problem was a references issue or otherwise
resolve this issue? One of my client's is experiencing the same problem
except it is happening on Access 2002 -- works with one computer but raises
an #3163 error on another. They have a rather mixed environment Office
2002/2003 and do not have a strict Windows Update process so I am trying to
find out what versions of Jet they have. Also this cropped up the day after
they performed a Compact of the back-end file, don't know if that is just
pure coincidence or not.

/tcoles

 

[tcoles]

It turns out I had somewhat similar problem but in Access 2002 (however in a
multiuser environment with a couple folks just recently installing Office
2003).

The difference was that the action queries (there were both maketable and
append query into temporary front-end tables causing the same error#3163) did
not work even trying to open in datasheet view (clicking on "Open" in the
Access database window). However, when I clicked on Design View and then
exitted out and then opened datasheet view the query ran successfully without
error, and also VBA execution of the queries worked fine from then on.

These queries worked fine quite some time without problems; however only
just stopped working after two changes to environment occurred -- back-end
file was compacted and a couple people started using Access 2003 with their
front-ends (which were designed in Access 2002). Not all users had this
problem but every computer that did, opening/closing the queries in design
view (without making any changes) caused the error to disappear. Why would
this make a difference? Any clues?

 

[Jon Ley]

I hate these problems! It appears to have resolved itself now, so I cannot
say for sure what the problem was. It *could* have been references - I had to
issue a new version of my front end as I made some changes to a home-grown
ActiveX dll. The only change from a references point of view is that I am now
referencing the new version of my dll. Nothing else changed (there were other
minor program changes, but nothing that should have impacted on this problem).

So I am now happy - my program is working; but I can't really offer any more
help to anyone else having the same issue, other than to suggest trying the
references route first.

Regards,

Jon.