Specific Autonumber Creation 3

[john2thompson]

Hello,

I need help in creating a record in an Access table with a specific autonumber.
I need to add this specific autonumber because the record that needs to be added was formerly in the table (before being deleted by a user!) and hence has relationships with about another 20 tables elsewhere in the database.

I've tried several methods including:
-Using Append Query (using INSERT INTO) in Access
-Using Append Query in code (using <dbName>.Execute and -OpenRecordset() methods)
-Using Recordsets in code (with .AddNew & .Update)
-Detaching all relationships from the table and adding manually
-Converting autonumber to a integer and manually adding

...but to no avail!

Using the INSERT INTO queries produces Key Violation errors (even without any relations set up). When running through code, no runtime errors occur, but the ID is not added to the table.

Anyone got any other suggestions about the causes of this, and any potential solutions.

Cheers,
John

 

[jdegeorge]

You can't add or edit the autonumber field.

Jim DeGeorge

 

[john2thompson]

I thought this too, but we have another procedure to carry over an archived record from an old database into the current database.
This uses code to create an SQL string and INSERTS INTO the current database (using <dbName>.Execute) the full record including the Autonumber. I have also tried to copy this procedure (which, incidentally works!) for the purposes I need it for, but again, no luck!

 

[nicsin]

Have you considered inserting the record with a new autonumber and then updating its relations with the new autonumber? I know it is a lot of work (depending on the database) but it could be done...

 

[jdegeorge]

Ah. An example of when relational databases can bite you in the butt! Is it only the one record or a few records that's missing? If so and you know the OLD autonumber ID, why don't you replace that reference in the other tables with the NEW autonumber ID? It may be backdoor-ish, but it'll work.

Jim DeGeorge

 

[jdegeorge]

Hey nicsin...

I guess we were typing at the same time! GMTA!!!

Jim DeGeorge

 

[nicsin]

Seems like it

john2thompson,

what do you think about it?

 

[john2thompson]

Yeah, thought of those methods, but wanted to only use this method as a last resort really, as it would take a couple of hours looking in the 200+ tables that this New ID number could potentially be included in!

 

[jdegeorge]

Unfortunately, it's your only option. I'm sure there's some way to do this in VB using table names, etc. but I don't know how. But, I'm sure there's a wizard on line that's just chomping at the bit to help. Good luck!

Jim DeGeorge

 

[nicsin]

well, I don't think there is another way! Hopefully you will have a relationship model and know which tables references this one. If not

Good Luck!

 

[john2thompson]

Well, thanks for the help people, really appreciated. Guess I will have to do it the hard way, *sigh*.

Thanks again,
John

 

[jdegeorge]

Enjoy a star on me, Nicsin!

Jim DeGeorge

 

[nicsin]

Star.....

Well, I guess you deserve it as much as I do!

Take care

 

[john2thompson]

Well, finally managed to solve the problem. In the end it wasn't a problem with either the code, query or relations. The problem lay with the setup of the table.

The table I was updating only had one field as a primary key, hence I assumed this would be the only field that would be mandatory for this table. I even checked every field in the table to make sure that no others were 'required'. However, one field in the table had a 'format' pre-specified, so although it wasnt a primary key or 'required' it still needed to have some data stored in it to match the format.

After entering the necessary data, the append query worked perfectly.

Thought I would let you all know, just in case any of you might fall into this trap in future!

 

[nicsin]

Well done john2thompson! Thanks for the tip.

 

[jdegeorge]

Enjoy a star on me! The best solution to a problem is the one you find yourself.

Jim DeGeorge

 

[nicsin]

Well, that's exactly how I got my first star. Remember Jim...thread701-679984?

 

[jdegeorge]

Yes, I do... ;-)

Jim DeGeorge

 

[fmarckus]

Hi John

It's a common trap to fall into with Access. I always have an autonumber field called AutoID in each table and a seperate Primary key field such as CustomerID. When a record is added to the table, I use VB to make CustomerID = AutoID, thus ensuring the primary key is unique. This means that if you ever need to transfer data from one table to an empty table of the same structure, you don't have to worry about which Auto Numbers are generated, because they aren't related to other tables. If you paste a large number of records into a new table, you will have to syncrhonise the AutoID with the CustomerID to ensure your VB code does not try to create duplicate keys. I do this in code as shown below. The following sub adds a new AutoID field to an existing table which effectively frees your current primary key field which you should change to a long integer. Please note you will have to temporarily delete the relationships to the table you are adding the AutoID field to to make sure no related records are deleted in the process.


Sub AddAutoNumber(TableName As String, KeyName As String)

On Error GoTo err_trap

Dim dbs As Database, rst As Recordset
Dim tdf As TableDef, fld As Field, fldIndex As Field, idx As Index
Dim strSQL1 As String, strSQL2 As String
Dim strSQL3 As String, strSQL4 As String
Dim strTempName As String, booAutoTrue As Boolean
Dim intCount As Integer, intMaxFld As Integer
Dim intOffset As Integer, lngLastID As Long

Set dbs = CurrentDb

With dbs
Set tdf = .TableDefs(TableName)
strTempName = tdf.Name & &quot;~tmp&quot;
intCount = 0
intMaxFld = tdf.Fields.Count
booAutoTrue = False
strSQL1 = &quot;SELECT &quot;

'build SQL strings
For Each fld In tdf.Fields
If fld.Attributes And dbAutoIncrField Then booAutoTrue = True
intCount = intCount + 1
strSQL1 = strSQL1 & &quot;[&quot; & tdf.Name & &quot;].[&quot; & fld.Name & &quot;]&quot;
strSQL2 = strSQL2 & &quot;[&quot; & fld.Name & &quot;]&quot;
strSQL3 = strSQL3 & &quot;[&quot; & strTempName & &quot;].[&quot; & fld.Name & &quot;]&quot;
strSQL1 = strSQL1 & IIf(intCount < intMaxFld, &quot;, &quot;, &quot; &quot
strSQL2 = strSQL2 & IIf(intCount < intMaxFld, &quot;, &quot;, &quot; &quot
strSQL3 = strSQL3 & IIf(intCount < intMaxFld, &quot;, &quot;, &quot; &quot
Next

If booAutoTrue Then
MsgBox &quot;Table already contains Autonumber!&quot;, vbInformation
Exit Sub
End If

'create temporary table and insert existing data
strSQL1 = strSQL1 & &quot;INTO [&quot; & strTempName & &quot;] FROM &quot; & &quot;[&quot; & tdf.Name & &quot;];&quot;
.Execute strSQL1

'empty original table
strSQL1 = &quot;DELETE * FROM [&quot; & tdf.Name & &quot;];&quot;
.Execute strSQL1

'create indexed autonumber field
Set fld = tdf.CreateField(&quot;AutoID&quot;, dbLong)
fld.Attributes = fld.Attributes + dbAutoIncrField
tdf.Fields.Append fld
Set idx = tdf.CreateIndex(&quot;AutoIndex&quot
Set fldIndex = idx.CreateField(&quot;AutoID&quot;, dbLong)
idx.Fields.Append fldIndex
idx.Unique = True
tdf.Indexes.Append idx

'copy data back into original table
strSQL4 = &quot;INSERT INTO [&quot; & tdf.Name & &quot;] ( &quot; & strSQL2 & &quot &quot;
strSQL4 = strSQL4 & &quot;SELECT &quot; & strSQL3 & &quot;FROM [&quot; & strTempName & &quot;] &quot;
strSQL4 = strSQL4 & &quot;ORDER BY &quot; & KeyName & &quot;;&quot;
.Execute strSQL4

'refresh tables collection and delete temporary table
With .TableDefs
.Refresh
.Delete (strTempName)
.Refresh
End With

'add records in order to syncronise ID fields
Set rst = .OpenRecordset(tdf.Name)
With rst
.MoveLast
lngLastID = .Fields(KeyName)
intOffset = lngLastID - !Autoid
For intCount = 1 To intOffset
.AddNew
.Fields(KeyName) = lngLastID + intCount
.Update
Next
.Close
End With

'delete additional records
strSQL1 = &quot;DELETE * FROM &quot; & tdf.Name & &quot; WHERE &quot;
strSQL1 = strSQL1 & &quot;([&quot; & KeyName & &quot;] > &quot; & lngLastID & &quot;&quot;
.Execute strSQL1

End With

Exit Sub

err_trap:
MsgBox &quot;Error adding autonumber field in table &quot; & TableName & &quot;.&quot;, vbCritical

End Sub