Messing With SQL

[Learn2010]

I have an Access database with SQL links. I have no control over the SQL. I was changing data in a table I wasn't using anymore by deleting all of it and using an append query to add the new data. I tried to append my own ID number to the AutoNumber field. It did not work. These ID numbers are used in three other tables so I needed them. I appended the records without the ID number to try something else that did not work.

Next, I deleted all that data, compacted and repaired the database, and thought the AutoNumber field would reset to 1, as Access does. It did not. Is there a way to reset that without having control of the table?

Thank you.

 

[Ranman256]

you cannot add your own # to an autonum field.

in order to get autonum to start a 1, you must delete ALL the records, then compact the db.

 

[Learn2010]

That doesn't seem to work with a SQL link.

 

[isladogs]

That doesn't seem to work with a SQL link.

No it won't work with any linked tables whether Access or SQL.
When you compact the front end, that's all you are compacting!

Stop worrying about where the autonumber starts from - they have no meaning other than being unique values

 

[MarkK]

No, you can't control the ID. You should assume when working with data that the value of the ID is meaningless, and you should not rely on any process to be able to reproduce its value in a different table at a different time.

If you are moving structured to data to new tables, you may need to write code that adds a parent row, captures the new parent ID, then adds child rows updating their foreign keys as you go.

hth
Mark

 

[Learn2010]

You solved that. That was the answer.