Problem with autonumber field

[RichO]

I maintain an Access DB for a friend who runs a business. The problem I am having is that the auto-number value on his machine is different than on mine so anytime I try to add data in testing, it tries to create a primary key value that already exists in the table.

Can I set the auto-number "counter" on my computer to a chosen value so I don't have these issues?

Thanks

 

[jdraw]

see if this helps

Look at resetting the seed.


But the numbers on 2 different versions of the database (on 2 different machines) will not be in sync.

 

[CJ_London]

Also consider splitting the database, then you can add test data to your backend and it won't affect your friend

https://support.office.microsoft.co...628-af71-2764685ae50a&ui=en-US&rs=en-GB&ad=GB

 

[RichO]

The database actually does have a back end with linked tables. I don't have all of his data, maybe about 20% of it for testing but wherever my primary key index is set falls right within that data

 

[gemma-the-husky]

if you are testing with testdata, why is it an issue?

when you release the new front end back to his real data, the dbs should work with the real data.

record ID numbers are immaterial

 

[RichO]

It works fine with his data but when I test new features or changes by attempting to add a record I get an ID conflict error message and I can't proceed.

 

[spikepl]

Again lots of complex solutions :-( ----

Make an append query that inserts a record with a number in the autonumber column that is one less than the value you wish to have. Yes - an append query WILL let you write to the autonumber column. Done!

 

[RichO]

Anytime a new record is written to the table it is done through standard form data entry. I use append queries all of the time but in this case it doesn't help.

 

[spikepl]

You need to be specific: "doesn't help" contains 0 information. see my signature.

Also, you need to read more carefully: normally append queries do not contain data to append in an autonumber column, becaue that makes that column redundant!

To change the autonumber you need to put a number in that column USING an append query.

 

[RichO]

Let me try to explain further. This is a standard form and the record source is the table. When you add a new record using the form (no query involved) there is a conflict in the autonumber field because the autonumber index on my computer is different than the index on his.

Say there are 1000 records in the table and the primary key (autonumber field) ranges from 1000 to 1999. Are you saying if I execute a single append query to that table it will reset the index so the next record I add using the form (no query) will be greater than 2000?

 

[gemma-the-husky]

Let me try to explain further. This is a standard form and the record source is the table. When you add a new record using the form (no query involved) there is a conflict in the autonumber field because the autonumber index on my computer is different than the index on his.

Say there are 1000 records in the table and the primary key (autonumber field) ranges from 1000 to 1999. Are you saying if I execute a single append query to that table it will reset the index so the next record I add using the form (no query) will be greater than 2000?

what we are saying is we can't understand why the values in your test system have any impact on the live system, unless for some reason you are trying to write a specific value into an autonumber field.

It sounds like a design/coding error in your database, to be honest.


in your test database, if you insert a record, you will get an ID value of the next autonumber, which might be 1087 for example

in the live data, the same insert might yield a different value. The actual value should be of no importance at all, providing that the test data is completely separate from the live data.

 

[ButtonMoon]

Say there are 1000 records in the table and the primary key (autonumber field) ranges from 1000 to 1999. Are you saying if I execute a single append query to that table it will reset the index so the next record I add using the form (no query) will be greater than 2000?

Yes, that's what Spike is saying. If you insert a value to an autonumber column by using an INSERT query then the numbering will restart from that number:

INSERT INTO Table1 (id) VALUES (2000);

where 2000 is the value from which you want the autonumber column to restart. If you have non-nullable (required) columns in your table then you will get an error if you don't supply values for those columns. Just ignore the error and allow the query to execute anyway.

The INSERT method does have the disadvantage that you have to insert a row, which you may not wish to do. There is an alternative method which is to change the autonumber seed value directly in a query:

ALTER TABLE Table1 ALTER COLUMN id AUTOINCREMENT (2000,1);

Again, you only have to do this once. The numbering will restart from that point forward.

Hope this helps.

 

[RichO]

OK I kind of figured out what is happening and maybe you can explain why.

There are 3 users of this database at 3 separate locations. The main user does all of the data entry and changes and the other 2 use the data for reference. I have it set up that once a day the main guy uploads the most current data to a web server as a file. With the click of a button the other users can download that file and it will update their table using TransferDatabase and an append query. This all works fine for them because they do not add or edit anything.

I was able to reset the index with an append query, however, as soon as I downloaded that file from the server and it updated my data with an append query, the index got reset and I was getting duplicate primary key errors when I tried to add a record using the form.

Looking at the table after the download and update, the highest primary key number was 148384. However when I try to add a record, it wants to give it the number 148156 which already exists. That's where the problem lies.

So if the highest key # in the table is 148384, why is the autonumber not set to start at 148385 after the append? There are gaps in the primary key where records get deleted at times.

 

[gemma-the-husky]

Looking at the table after the download and update, the highest primary key number was 148384. However when I try to add a record, it wants to give it the number 148156 which already exists. That's where the problem lies.

I just do not understand this. Are ALL the tables in a linked database, or do you have a table(s) in the code database that is somehow being affected by your database management.

modifying code should have no effect on the data.

 

[RichO]

There are some tables in the front end but they are either for temporary data or static data. None of them are linked in any way to the main table that I am having the problem with.

When the update is downloaded, the table from the downloaded mdb (tb_Jobs_Update) is transferred into the front end as tb_Jobs_New, then the append query is done from that into the back end table (tb_Jobs).

DoCmd.TransferDatabase acImport, "Microsoft Access", strPath & "DataUpdate.mdb", acTable, "tb_Jobs_Update", "tb_Jobs_New"
db.Execute "DELETE * FROM tb_Jobs"
db.Execute "INSERT INTO tb_Jobs SELECT * FROM tb_Jobs_New"
DoCmd.DeleteObject acTable, "tb_Jobs_New"

 

[gemma-the-husky]

well clearly the problem is being caused by that process.

I think you need to look at the data carefully to see what it actually does.