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.

 

[Pat Hartman]

With Jet and ACE, you just use an append query. But even SQL Server allows you to append rows with autonumber values provided they don't duplicate any existing values. In either RDBMS, you must use an append query but SQL Server requires you to set a flag first.

I have an app that includes a bunch of test data that the client can play with. So, I gave them a way of reverting to the intitial values. Here is the code to copy my saved versions of a few tables to the production database.

''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblAuditParms" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qReset011AppendAuditParms"
            qd.SQL = "SET IDENTITY_INSERT " & "tblAuditParms" & " OFF"
            qd.Execute
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblListValues" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qReset016AppendListValues"
            qd.SQL = "SET IDENTITY_INSERT " & "tblListValues" & " OFF"
            qd.Execute
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblDocuments" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qReset017AppendDocuments"
            qd.SQL = "SET IDENTITY_INSERT " & "tblDocuments" & " OFF"
            qd.Execute
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblMembers" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qRESET020AppendMembers"
            qd.SQL = "SET IDENTITY_INSERT " & "tblMembers" & " OFF"
            qd.Execute
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblDependents" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qRESET030AppendDependents"
            qd.SQL = "SET IDENTITY_INSERT " & "tblDependents" & " OFF"
            qd.Execute

There are many valid reasons for needing to use predefined autonumbers starting with conversions. If you can't copy the autonumber of the parent, all the child links break and it's a lot more work to do the conversion.