New Computer or Backup Version... AutoNumber not followed (1 Viewer)

[ilcaa72]

hello i am having some odd (what I think) behavior with my autonumber. But i cant isolate where it is coming from.

How i use autonumber:
I created a table with an autonumber. I add data to this table and column 1 is my autonumber ID. I then do an append query to add that data to an archive table, I also then delete the data from the 1st input table so it is clean for next time. (i need to do it this way).

When i use the input table again, my first entry starts where the autonumber left off last time (even though i deleted the data), this is exactly what i want.

The problem...
over the weekend i stored the DB in a cloud solution, then I started working on this DB again from my laptop (a different computer). I also made a backup copy and started using the db from the backup copy. When i start to enter new data, the autonumber will include numbers already used. I dont know if its because I am using a new machine with different Access (2016 vs 2013) or what, but my autonumber is acting quite wonky, has new values but includes old values, this wont let me append to 2nd table since it has ' no duplicates' which is what i want...

I saved the db again online and opened it up in the original computer today, and the same behavior happpend! some new values but some older values..

anyone have a similiar experience with autonumber and differnet machines or versions of Access?

Should i create my own AutoNumber by finding Max() value of 2nd table and adding 1..

thanks

 

[isladogs]

This is quite common behaviour.
For example it can happen after a compact & repair.

There are plenty of other threadfs on this topic if you do a search so I wo'nt go through thingsd written in detail elsewhere

In one sense it doesn't matter as the autonumber field is just intended as a unique identifier so its value shouldn't matter.

However if it does matter to you, 'change' the PK field type to e.g. number & add code to increment the value by 1 for each new record.

I said 'change' in quotes because you can't change an autonumber field once it is populated. You would need to create a new ID field then copy the existing autonumber data to it. After that delete the existing ID field

Hope that helps

Colin

 

[ilcaa72]

thanks Colin

i did several searches but couldnt find anything, it was difficult trying to come up with a search term to define it..

thanks for letting me know its common, ill test some options out.

 

[almahmood]

Comapct & Repair is absolutely the reason like Colin said. Uncheck Compact on Close from the File-->Option-->Current Database-->Application options.

 

[ilcaa72]

so NEVER compact and repair the DB using Access?

 

[isladogs]

At the risk of starting lots of posts disagreeing with each other, compact & repair is a very useful & at times essential tool to use.
However be aware of some possible side effects such as this.
I also would recommend not to compact on close - just do it when the db is getting too big, slow or starting to show unwanted behaviour.

Here's one of many threads on this topic
https://www.access-programmers.co.uk/forums/showthread.php?t=293051

Just do a search for autonumber

 

[HiTechCoach]

You definitely need to compact often.

If compacting is causing an issue then that usually indicates there is a flaw or bug in your design.

I think you "bug" is that in your append query includes the autonumber field from your first table.

If the table you are appending to already has an autonumber field then you do NOT want to over-write it.

When you append the data to without including the original autonumber field the target table will automatically set the autonumber for each record.

 

[ilcaa72]

thanks for the advise on compact/repair. its a very small db, i was having weird behavior with my DB so i tried it.

I think you "bug" is that in your append query includes the autonumber field from your first table.

I originally had the 2nd table do the autonumber, but the order items come in is important. So i removed autonumber in 2nd table and placed it in 1st table. The append now brings the autonumber into the 2nd table and I delete data from 1st table. so far so good.

thoughts?

 

[almahmood]

Compact & Repair is required so, we cannot stop doing that but it has to be periodically like once in a month or weak.
We can do this in two different ways; one with alter command and another using custom AutoNumber.
1) When you are archiving your data you need to execute ALTER Table command and assign your next AutoNumber value. See below example:

CurrentDb.Execute "ALTER TABLE YourTableName ALTER COLUMN YourAutoNumberColumnName AUTOINCREMENT(50000,1)", dbFailOnError

once you execute this one line code your new AutoNumber value will be 50000 and you can change it to your actual AutoNumber value.

2) This is a work around like custom AutoNumber which means you will assign your AutoNumber while saving a record. But in order to that, we must know how you populate your parent table? If you update this using any Import, you can write a VBA code to update your unique number. Or if you are updating records through a data entry Form, I would suggest you to write your own Function like GetNextAutoNumber() which will return your next unique number based on your Archived and parent tables. In this case you have to change the datatype to Number from AutoNumber.

Hope this works for you.

 

[HiTechCoach]

I originally had the 2nd table do the autonumber, but the order items come in is important. So i removed autonumber in 2nd table and placed it in 1st table. The append now brings the autonumber into the 2nd table and I delete data from 1st table. so far so good.

thoughts?

IMHO, you are not using the autonumber data type properly.

To keep the order items cam in add a "TimeStamp" field of datatype data/time with a default value of Now().



.

 

[ilcaa72]

thanks Hi TechCoach

so there will not be a formal numbers (245, 246, 247) representing the autonumber, it will be a current date/time as the autonumber? this sounds like i can then check the last 30 values by using a .MoveLast and .Previous Loop without any issues

i actually posted for another issue I was having, the last value visually in the open table was not the value that was being returned by my VBA code, even though my autonumber suggested it was, i found about about set theory and db's.

if interested. https://www.access-programmers.co.uk/forums/showthread.php?t=293072

 

[HiTechCoach]

thanks Hi TechCoach

so there will not be a formal numbers (245, 246, 247) representing the autonumber, it will be a current date/time as the autonumber?

That is not what I was recommending.

My bad. You are probably not familiar with Microsoft SQL Server.

FYI: A timestamp field is used in Enterprise level RDMS's like MS SQL Server. This concept also works well in Access.

I am suggesting that you add an additional field that is the timestamp. Not as a replacement of the autonumber field. It would be in addition to the autonumber field. You would have both.

You will append he data from table 1 with the timestamp and without the autonumber to table two. Table 2 will assign its own autonumber to the the records as they are appends. This is what I originally recommended.

You stated you need the original autonumber from table 1 to tell you the order received. The timestamp will tell you the order received in place of the autonumber.

This way when you move the data between tables and the auto number can change. While the timestamp never changes.

Timestamp as the primary key and Multi-User.

I have not ever investigated the use of a timestamp as the primary key.

I have never tried having Access assign Now() as the auto number.

My concern is will it work in a multi-user application.

If the database is NEVER allow multi-user data entry then it probably would work.

Again, I have never tested the method of using Now() as the primary key. I can't verify it is a reliable solution.