• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1227
  • Last Modified:

SQL INSERT Autonumber trouble ......

I'm trying to INSERT a new record into a table using the DoCmd.RunSQL command in Access. The table I'm inserting into contains a field which is an AutoNumber. If I omit the AutoNumber field in the VALUES section, I get an error message, but I cannot specify a value for it as it is an AutoNumber. How do I insert a new record this way using the next available AutoNumber value?
1 Solution
I have no trouble doing what you are trying to do... Perhaps try CurrentDb.Execute instead of DoCmd.RunSQL, they are somewhat similar in their final outcome.

BTW, you CAN set a value in an AutoNumber field with an Insert, but I tend to let the AutoNumber do its job.

Let's say you have a table:

    ID - autonumber
    FirstName - text
    LastName - text
    BirthDate - date

when you're doing the insert, specify the fields that you want to insert and exclude the Autonumber field like:

INSERT INTO table1 (FirstName, LastName) VALUES ("Joe", Schmoe")

you can leave out whatever fields you want as long as their are not required by the table.  In the example above I left out the birthdate.  If the birthdate was a required field, an error would occur.

oops, forgot a quote:

INSERT INTO table1 (FirstName, LastName) VALUES ("Joe", "Schmoe")
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Access will automatically set the next autonumber value so you don't need to list it in the field list in the first part of the INSERT statement.
One additional remark:
When your autonumber is a key and used in other table(s), then this won't work....

why not?  If is an autonumber then it must be on the one side of the one to many relationship if it's in a relationship at all, right?  I'm pretty sure you're just not allowed to enter on the 'Many' side referring to a 'One' side value that doesn't exist.  Even that is only true if referential integrity is enforced.  Is that what you were talking about Nico?
It's a problem when the key has already been used as a foreign key.
A newly generated key won't be the same as the original key and extra effort will be needed to adjust the foreignkey to the newly created key.
But I guess rickblyth is just inserting one new "not yet related" entry.
Your solution will solve his problem as he will have deleted the value without removing the corresponding target field.


insert into table1(firstname, lastname) values ("leo", "lee")

It is common method to deal with autonumber in MSAccess and SQL Server.
rickblythAuthor Commented:
Cheers guys, will try it out and get back to you. Incidentally, the table is not realted to any others so there should be no poblem with referential integrity etc. Get back to you soon.

Try this function:

Public Function ExecuteID(SQL As String) As Long
    'Tested with ACCESS 2000 and SQL7.0 using ADO2.5
    On Error GoTo LocalError
    Dim cn      As ADODB.Connection
    Dim rs      As ADODB.Recordset
    Dim AutoID  As Long

    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    LastSQL = SQL  'Populate the LastSQL Property
    'Prepare the RecordSet
    With rs
        .CursorLocation = adUseServer
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Source = "SELECT @@IDENTITY"
    End With

    With cn
        .ConnectionString = GetCNString(False, False)
        .CursorLocation = adUseServer
        .Execute SQL, , adCmdText + adExecuteNoRecords
        With rs
            .ActiveConnection = cnNML
            .Open , , , , adCmdText
            AutoID = rs(0).Value
        End With
    End With
    Set rs = Nothing
    Set cn = Nothing
    'If we get here ALL was Okay
    ExecuteID = AutoID
Exit Function
    LastErrorNo = Err.Number
    LastErrorMsg = Err.Description
    If cn.State = adStateOpen Then
    End If
    Set rs = Nothing
    Set cn = Nothing
    ExecuteID = False
End Function
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now