0

Currently in one of our legacy system's database has one of its columns set to AutoNumber. I am investigating if there is a work around to circumvent this to allow the program to enter its own values into this column.

Perhaps some way to programatically disable the AutoNumber-ing process then re-enabling it again?

Improve this question

3 Answers 3

Reset to default
5

What is the problem? You can insert your own numbers into an autonumber field as long as they are unique, but if you want your own numbers, you should not be using an autonumber.

Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

I get this message : {"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."}
I understand what you are saying, as I stated in my question I was wondering is there a work around for this issue. If so please let me know. Believe me, if it were up to me I would also not opt for this as well.
1. You do not need to insert a number at all : UPDATE MyTable (MyText) VALUES ('Text') or 2. UPDATE MyTable (Id, MyText) VALUES (-99, 'Text') ie a number that does not exist in the table. Option 2 is likely to play havoc with subsequent numbers.
@Benjamin Wong Compact and repair the database. It will reset the numbers and is a good idea anyway.
@Benjamin Wong I have been using autonumbers for quite some time, and as long as all you ask of them is that they be unique, they will generally behave.
|
0

Use Alter Statements and Adox for changing the fieldtype from autonumber to integer

Hope this helps :)

Improve this answer

12 Comments

Pardon my ignorance as I am more used to working with SharePoint and SQL Server and my issue is also quite novel for me as well. Perhaps you can provide a sample or point me to some literature I can go thru?
Sure let me surf the correct resource for you. The problem you stated definately has a solution. I just lost my bookmarks. Give me 10 minutes at max.
check this link allenbrowne.com/func-adox.html. This is in VB. Can you convert the code in C#??
For alter statement use this :-ALTER TABLE tablename ALTER COLUMN columnname Number(10);
This is a really bad idea. It will cause problems with your database.
|
0

No you can't modify the type of the column of the database through a C# program. You have to change the Column type of the Column in Design View of the Table column in the MS-Access.

Update: You can change the type of Column if it is an AutoNumber but after adding the data, you wont get back to AutoNumber.

Improve this answer

6 Comments

That will not work even in Access if data has already been added to the autonumber field.
BTW I cannot see why you should not use the DAO libraries to change data types.
@Renmou What do you mean by DAO libraries? Are you referring to the .NET ADO libraries?
Sigh, that is what I feared. So the auto-number increments would be broken even if I managed to do it?
Why do you care about the values of an Autonumber field? You don't seem to understand the purpose of it.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.