Autonums -- duplicates and out-of-seq hits

[cp950]

I inherited an Access97 datatbase, where the primary transaction table has large records. its ID# is a Long-Integer AUTONUM. It is NOT a key or an index.

Occasionally, a record gets created where it has a dupliate ID#, somewhere in the middle, not the last-number. Almost always, the rest of the character are in an Oriental (Chinese, I think) font! There are no signs from the contents if these are corrupted records actually entered or created garbage caused some corruption. Access never claims that the database IS corrupted, though.

Occasionally at other times, the autonum will be eight digits long, where the current numbers are around 10,200. Those records have nulls or Chinese characters. Following records continue with the normal numbers, so I get this: 10200, 10201, 11123132, 10202, 10203...

Environment: The users upgraded to AccessXP, and these rarities still occur. The server is NT 4.2 and the workstations are Win98. Multiple anti-virus programs were tried and every one finds no infections. This database is split (data back-end on the server -- MDB) and (forms front-end on the desktop -- MDE). the data is compacted every two or four days.

Question 1) Does anyone have an idea how this might be occurring, and how to prevent?

Question 2) Is it safe to change the ID# to a KEY, leaving it as AutoNum?

Thanks to anyone who can help!

 

[WildHare]

Is this autonumber field being used as a join key in relationships somewhere?

If not, delete the sun of a gun from the table and continue on as directed.

If so, we may have some work ahead of us..

I'll wait for a response.

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[cp950]

WildHare:

Thanks for responding! Unfortunately, this field is used as their Invoice#. So obviously I cannot delete it. Would reforming it as LONG and defaulting it to a "DMax+1" do the trick?

There are NO defined relationships, either in the data half or the forms half.

How did you mean "Continue as directed" ?

 

[WildHare]

It's an invoice number, automatically assigned, but not used as a key or a join field? How odd...

I can not hazard a guess as to what is causing the corruption (save the possible use of your database by a person with the Chinese version of Windows installed?)

You can't change this guy to a key without it choking on the possible dupes or null values.

How important is this rather shaky "invoice number" field in relation to real stored information somewhere? I mean, are there paper artifacts which carry it somewhere? If so, changing it around (changing the values of the INVOICE NUMBER within the records) would probably NOT be a good idea.

I would have to know a little bit more about the processes involved, to be able to give you some options. Can you elaborate just a bit?

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[cp950]

JIM:

Thanks for helping!

Yes, the "invoice number" is used throughout the company's processes. Trying to change it to something else would be unacceptable to them, and I fear quite foolhardy. I fully plan on checking for unacceptable values (nulls, dups, etc.) and making the thing the key. Once I do that, I can start invoking relationships. I see no reason for this check&change not to work safely.

And to quote you: QUITE odd. I would never have designed a setup like this...

All the users are on one office. They all have slightly different PCs with different hardwares/memory, but all have the same version/build of Win98 and the same version/build of MSaccessXP. They all use the same language-sets. My tendancy is to mistrust Win98, especially when memory ranges from 512mb down to 96MB.

80% of the data-entry is done by one user. 19% done by text-import (done by two users). The last few entries are by the person I most trust to be accurate. Repeated attempts to track the data give me no pattern (date, user, running-programs, time-of-day). All I have to work with the two facts I originally stated (screwy auto-nums) and (Chinese characters).

Another possibility is that extensive notes can be appended (stored in a separate table), and there's many text fields in the main table. Could Access be wacking out on an oversized record?

Anything specific I can tell you?

Thanks again,
___CP___

 

[WildHare]

Hi CP - sounds like you got a winner here..
I don't think it's Access whacking out on an oversized record. The JET engine, while it's not exactly DB2, is at least somewhat competent in most areas.

I'd suspect the TEXT import process part of it, if you asked for my gut feeling.

FOr one thing, it's probably pretty difficult to hand-key Chinese characters, unless, as I noted, you have the appropriate code page, Windows version, and system fonts installed, not to mention a 5,000-key keyboard.

I don't know if it's possible, but it might be worth trying out the text-import process on a dummy table a couple of times, and seeing what you get. You never know what some unprintable characters in a text file might do to you when they get imported. I don't do a LOT of this, but when I do, I always import from text to a temporary table first, then run some cleansing processes on it to make sure the data is clean and correct before I load it to the main operational table.

Let me know how things are going.

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[MichaelRed]

two items of potential interest.

1.[tab]see faq 700-184 particularly if the db has ANY posasability of multiuser functionallity.

2.[tab]I would ALWAYS advise importing data into a TEMPRORARY recordset and doing V&V on the content. For records which "pass" the tests, import them into the active/live db and remove them from the TEMPORARY table. Return the ETEMPORARY tables' remaining contents to the source (GENERATOR) and ask for clarification and review of the issues.

IF you are feeling particularly 'helpful', you couls add a field to the TEMPORARY table re which V&V tests were failed for each record. This COULD be helpful in tracing hte source(s) of problems in the process, both at your process and at the source of the records.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[cp950]

OK, I am back.

I made a number of tests, and did a bit of research.

I have ruled out the import, as the days this last one appeared the imports-preson was in Omaha.

But the fact still sticks in my craw that the last chinese-character record I got had a whacked Autonum. ((to clarify, the record has an AutoNum field called [InvoiceNum])). Here's what I saw:
[InvoiceNum] <<other data>>
92014 ...normal...
92015 ...normal...
1295778842 Field contents unreasonable or CHINESE
92016 ...normal...
92017 ...normal...

We are actively planning a complete restructuring/reprogramming. That'll be a trip!

Side question: Could Win98 with less than 129mb RAM have caused this?

thanks to all -- GRAET deal of help! Pat yourselves on the back...

 

[danvlas]

Just a thought (I have never encountered what you describe here). Maybe the 'Chinese characters' are just some funky elements added by Access when the AutoNumber was just about to get duplicate???? Maybe this is Jet's way to prevent duplicates in an AutoNumber?

If this field hadn't been such a meaningful piece of data - invoice number, I guess you wouldn't have to worry about it.

Just to see what happens, I'd create a dummy related table, a form on the main table with a subform on the dummy table, linked by AutoChineseNumber and I'd try to enter data in the dummy. Then you could see if the values are passed and the link works. If not, you have a corrupted database and Access doesn't know it. If yes, create a new Long Integer field next to the AutoJapanese, run an Update query to transfer all numbers to the new field, then cleanup what's to be cleaned up and check Michael's FAQ to generate your own numbers in sequence in this new field. And you can also delete the AutoTaiwan field after that and rename the good one so that objects keep working.

But...very important...are you sure it's Chinese? Maybe it's Korean, which changes things dramatically LOL




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[MichaelRed]

to clear it up a small amount. the &quot;chinese' isn't in the autonumber field, but the remaining fields of the record.

the 'Chinese&quot; characters are (almost) alwas just &quot;Hi ASCII&quot; (actually they are ALWAYS just Hi ASCII - but we don't necessarily know the code page or font they were originated in).

I would STILL suspect the db has multiple users entering data &quot;simultaneously&quot; - in SOME manner, which is just a round about approach to saying read the docs and refernce the faq noted previously.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places