Run-time Error 3146 - ORA-01722 Invalid Number

[user9]

I have searched for an answer to this with no results specific to my situation.



I have a user database that has not had this issue since it was created over a year ago. Suddenly, two users get this error on most of the records they try to enter, but nobody else has this problem. I can't replicate it as I don't get this when I try to update the same records.

I verified that the table the form is writing to matches all field types (number/text/date/etc), and of course it's not every record that has the issue.

Any suggestions?

[ItsMe]

Need more info. Try to describe what the DB is doing when the User is interacting with it? Are you using ODBC to connect? Is this a stand-alone file on the local machine?

[orange]

Does this help?
http://www.orafaq.com/wiki/ORA-01722

[user9]

The user enters information into about 25 fields, then clicks a save button to transfer the information into a table. It is on this click that the user gets the error. Yes, there is an ODBC connection and these tables are remote.

On a record where one user is getting an error, three other users (including myself) do not get this error.

[user9]

Thanks orange, this is one of the links I used to troubleshoot prior to posting in this forum

[ItsMe]

It looks like orange did a little digging for you. Maybe these users are managing data that is unique from the other users and there is a misinterpretation of characters. It sounds like you may be using an unbound form. Perhaps a temp table would help to standardize characters, matching fields in Access table to fields in remote table. Save in local temp table before export.

[user9]

That's an interesting idea. I could export to a table without the same parameters to see what is actually being sent. I'll try that, thank you for the idea

[ItsMe]

Let us know...

[user9]

So I created local table1 as an exact copy of remote table2 (the original destination) and updated the code to point there. Success. Then I added a query to the process to update table2 directly from table1 and tested it successfully on my PC. However, it worked a couple times for the users and a couple times it gave the same error.

It appears to have to do with those users writing to the remote table2, but what is perplexing is that it doesn't error every time. If it was a connection/ODBC issue, it should fail every time.

[ItsMe]

You cannot take error codes too literally, at least their descriptions too literally.

I do not know anything about the Oracle so I cannot comment on that. However, matching data types between different flavors of DB's takes concentration.

Are you able to identify what field/record is throwing the error?
Did you do your best to get the Access data field to match the data type in the Oracle DB?

[user9]

I have not been able to identify the field in error. If you have a form with many fields that update into a table, is there a quick way to isolate what is causing the error?

The Access fields match the Oracle DB. For most users, there is no error. It's something about these two users' PCs or something they're doing that is not easily identified.

[ItsMe]

...If you have a form with many fields that update into a table, is there a quick way to isolate what is causing the error?...

Not that I am aware of. My approach is to use VBA to leave bread crumbs. In each control's AfterUpdate event handler I would place some VBA that would write to a log/table, identifying a successful update. Error trapping would log the error.

You are correct that these two users are doing something different. You were able to duplicate what is different when you got the error using the temp table. I suspect their data is different from other users. Perhaps you are storing large numbers into an Oracle data type that is not acceptable. It seems to me that the data types in the Access table match the User's input and Oracle is not understanding.

Are you, simply, using Text data types in all of your Access fields?

[orange]

Can you tell us more about your set up?
Since you are using Access and Oracle via ODBC, are all versions of Access the same?
Does each user have a copy of the Access front end on their own PC?
Is the Access front end the same for all users?

I haven't used Oracle for a long time, but it seems to me you are getting an error returned by Oracle. So something is being misunderstood/incorrectly converted/substituted at the Oracle end in my view.