Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL INSERT Autonumber trouble ......

Posted on 2001-07-19
10
Medium Priority
?
1,223 Views
Last Modified: 2008-06-24
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?
0
Comment
Question by:rickblyth
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 5

Expert Comment

by:KMAN
ID: 6299356
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.

K
0
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6299440
Let's say you have a table:

table1
    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.

0
 
LVL 6

Accepted Solution

by:
cjswimmer earned 100 total points
ID: 6299442
oops, forgot a quote:

INSERT INTO table1 (FirstName, LastName) VALUES ("Joe", "Schmoe")
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:cjswimmer
ID: 6299448
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.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6299891
One additional remark:
When your autonumber is a key and used in other table(s), then this won't work....

Nic;o)
0
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6299903
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?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6299958
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.

Nic;o)
0
 
LVL 1

Expert Comment

by:leolkk
ID: 6301628
rickblyth,

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

It is common method to deal with autonumber in MSAccess and SQL Server.
0
 

Author Comment

by:rickblyth
ID: 6301895
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.

Rick
0
 
LVL 3

Expert Comment

by:BGillham
ID: 6333658
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
        .Open
        .BeginTrans
        .Execute SQL, , adCmdText + adExecuteNoRecords
        With rs
            .ActiveConnection = cnNML
            .Open , , , , adCmdText
            AutoID = rs(0).Value
            .Close
        End With
        .CommitTrans
        .Close
    End With
    Set rs = Nothing
    Set cn = Nothing
    'If we get here ALL was Okay
    ExecuteID = AutoID
Exit Function
LocalError:
    LastErrorNo = Err.Number
    LastErrorMsg = Err.Description
    If cn.State = adStateOpen Then
        cn.RollbackTrans
        cn.Close
    End If
    Set rs = Nothing
    Set cn = Nothing
    ExecuteID = False
End Function
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question