Error 3163, But Size Isn't an Issue 1

[JeremyNYC]

I've got a database that's been in production for two years. This issue has never come up before. I have made no changes to any code or tables affected by any of this.

In code I have the following:
strSql = "INSERT INTO TblMemoRecipient (ActionID, " _
& "EmployeeID, DiscrepancyID, CC, MemoCycle) " _
& "VALUES (" & lngActionID & ", " & rst2!EmployeeID & ", "
& lngDiscrepancyID & ", " & rst2!CC _
& ", " & intMemoCycle & &quot"
debug.print strSql
Call db.Execute(strSql, dbFailOnError)

The results of the debug are this:
INSERT INTO TblMemoRecipient (ActionID, EmployeeID, DiscrepancyID, CC, MemoCycle) VALUES (93301, 2, 44561, False, 1)

The execute give me this error:
Error 3163: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

Here's a description of tblMemoRecipient:
FieldName Type Size
ActionID Long Integer 4
EmployeeID Long Integer 4
DiscrepancyID Long Integer 4
CC Yes/No 1
MemoCycle Long Integer 4

If I copy the SQL into the SQL view of a new query, I get the same error.

If I create a new record I can use values much bigger than those in the SQL statement in each of the Long Integer fields without a problem.

I have compacted and repaired the back end.

I have checked through archives here and at comp.databases.ms-access and found nothing that directly applies to this situation--all of the examples I saw involved the fields actually being too small.

Help files tell me I should be able to get numbers over 2 billion in these fields.

Any help would be very welcome.

Jeremy

PS: Maybe size is an issue, and I've just been deluding myself. I certainly would be happy to be wrong about this.

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[JeremyNYC]

OK, so this is odd. I fixed it by compacting and repairing the front end. I guess that wouldn't be all that odd except for the fact that the version my users had hadn't been touched (it's an mde when they get it) for over a week. This means that the front end had some odd corruption in it that only triggered when executing an Update SQL statement on one of the tables in the back end, after one of the values reached a certain size.

I've never seen anything like it.

I'd still love to hear from anyone who has any other ideas about what went wrong here.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[willir]

Jeremy
I needed a link to your site (for Litwin), and picked (one of the very few) problems you had posted instead of the (zillion) solutions you had provided.

I suspect the problem was with one of the foreign keys. The fact that the problem occurred with the SQL statement clearly indicates this was a data issue at the table level, and had nothing to do with your code and the dynamic data. This is supported by the fact that the DB repair resolved the problem. If the problem was with the primary key, I think you would have had a bigger mess.

One type of problem could have been that DiscrepancyID was trying to use an integer field size instead of a long interger and 44561 is bigger than the 32,767 limit.

Any of the other fields would have passed just about any other integrity test. (Exceptions - would be the use of logical field for the intergers.)

In hindsite, it would have been interesting to test if the field was looking for an int instead of a long int.

I guess the next issue was if the error was preventing an update for the one record, or the entire table was impacted.

I surmise only adding a new record was impacted and that all is well in the land of Alphabet City.

The only times I have seen database corruption issues are...
- databases approach max size
- ungraceful exits from the application
- hardware damage - drive, static (and steam but that is another story)

And of course, we have now heard of the technical problems with autonumber in a multiuser environment. (Maybe Microsoft will get wise and use a serial number system akin to Oracle and Infomix)

Richard

 

[JeremyNYC]

Richard,

Heh! Thanks for thinking to check out what I'd asked about, and for responding. I honestly don't remember what I figured out about this, though I would assume that it was exactly what you said.

I actually just washed my hands of this client, leading to a bit of a celebration (it was getting really political there) and a bit of worry (they were my reliable cash producing client), so it's funny to get this today, the day after I finished with them.

But in any case, thanks much for the response. That Litwin article really does come in handy for a lot of people, eh?

Cheers, and keep up the great work.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.