[RESOLVED] Primary Key Auto-increment

[MintFlavor]

Hello

I am trying to insert records in a table using recordset (ADO).

Does anyone have handy codes that will cause my new record's primary key to auto-increment? That would help me loads.

Thanks

[si_the_geek]

In the table design set the data type of the field to AutoNumber (or Identity), and simply "ignore" that field when adding a record.

[MintFlavor]

Great if it works like this. Thanks

[Hack]

Just keep in mind that if you delete a record, you won't get the deleted record id back.

[MintFlavor]

I have another problem now..

I now have to save that primary key (which you tell me will automatically be generated by the db) as a secondary key in another table.
(Using recordset in vba)

How to get this value? How to go about this?
Do I have to keep my first recordset open and go to last record?

[jp140768]

Most versions of SQL, allow you to Select @@Identity straight after the Insert, which will tell you the value of the last insert.

If using SQL Server, you can also use IDENT_CURRENT, AND IDENT_SCOPE. IDENT_SCOPE is good if multiple people could insert records into your table.

HTH

[techgnome]

IDENT_SCOPE? Or did you mean SCOPE_IDENTITY?

-tg

[MintFlavor]

Using Ms Access folks..

I was wondering if, since relationships have been created for the secondary keys, wouldnt it automatically fill them in if i created a record in the table to which it is related?

Illustrating this:
tblProject and tblTeam are related: ProjectID is found in tblTeam and related to the primary key (ProjectID) of tblProject
(Normally, my ProjectID will be autom. incremented coz it's an Autonumber field - see Post #2)

Now, if I created a record in tblTeam and did not specify anything for field ProjectID, wouldnt it be automatically be filled in by the db? (since it will detect the relationship)

i don't know...........

[GaryMazzone]

No it will not just automaticly fill in for you. You must explictly post the ProjectID into the Team table. The DB can't just assume that what Project the team will be working on. If you don't supply it the fields will be left blank (if allowing nulls is set for the field in the DB).

[MintFlavor]

No it will not just automaticly fill in for you. You must explictly post the ProjectID into the Team table. The DB can't just assume that what Project the team will be working on. If you don't supply it the fields will be left blank (if allowing nulls is set for the field in the DB).

Plz, then how should I go about that?

[GaryMazzone]

There are many ways to do what you want to do. First question is How do you reate the two Items (Project and Team) are they created together or seperatly? Do you have a table of personnel that you draw on to make teams?

If you make the Project first (which I would assume you have to too allow the project to be a Foregin Key into the Teams table) Save the Project. Requery the Database and get back that last inserted ProjectID (the project you just added) then use that to place into the Teams table.

I saw from the posting above that you use recordset add functionallity that I never have used. I always write the SQL statement my self and perform an Execute on the connection object (strictly ADO code only).

[si_the_geek]

Same here.. but you may be able to read the value from the recordset immediately after the add.

If not, open a recordset with this SQL statement:

Select @@Identity

..this will return a single field containing the last auto-generated value (for ProjectID in this case).

[MintFlavor]

'SAVE TO TBLTEAMBUILDER
Dim cnt as Long
'cnt = size of array StaffList

Set MyRSTeamBuilder = New ADODB.Recordset

' open the recordset.
MyRSTeam.Open "tblTeamBuilder", conn, adOpenDynamic, adLockOptimistic

With MyRSTeamBuilder
' For each staff in array StaffList
.AddNew
' set the value for the fields
'.Fields("ProjectID").Value = get value from last record in tblProject
'.Fields("StaffCode").Value = StaffList (x)
'.Fields("TeamID") = get value from last record in tblTeam
.Update
' Move Next until end of StaffList
End With

MyRSTeamBuilder.Close
Set MyRSTeamBuilder = Nothing


Set conn = Nothing

Thanks a lot for all your helpful posts. If possible would u ppl be so kind to help me write my codes correctly with all the new syntax u suggested to me.

[si_the_geek]

Reading from the recordset is simple enough, so you can probably work that out yourself.. so I'll explain how to use the SQL I posted. You need to run it as soon as possible after adding the record, and I would recommend storing it to a variable, eg:

Dim RSTemp as ADODB.Recordset
Dim lngProjectID as Long

  Set RSTemp = New ADODB.Recordset
  RSTemp.Open "Select @@Identity", conn, adOpenForwardOnly, adLockReadOnly
  lngProjectID = RSTemp.Fields(0).Value
  RSTemp.Close
  Set RSTemp = Nothing
  'lngProjectID now contains the ID

[MintFlavor]

Hi
thanks it's working.. It's great for ppl caring to help others in this forum.
Cheers to them.

[FunkyDexter]

If you make the Project first (which I would assume you have to too allow the project to be a Foregin Key into the Teams table) Save the Project. Requery the Database and get back that last inserted ProjectID (the project you just added) then use that to place into the Teams table.

I see you've gone with @@identity which is the right thing to do but I just wanted to warn against the above technique. It's fine in a single user enviroment but you should be very careful using it in a multi user enviroment. If your inserts interleave with another users inserts it can pick up the wrong project and assign your team to the other users project in error.

[si_the_geek]

I think it's actually ok, as MintFlavor is using Access.

I can't remember who told me (perhaps TG?), but apparently Access treats @@identity in the same way that SQL Server treats @@scope_identity

[MintFlavor]

Just to clear a doubt: Can't an applic created with ms access be deployed for a multi-user access? I guess it can right?

I assume the program proceeds by opening a table exclusively when a user adds in a new record. hence @@identity should work correctly isnt it?

[si_the_geek]

Just to clear a doubt: Can't an applic created with ms access be deployed for a multi-user access? I guess it can right?

It can indeed.. but you don't want to be using Access if you are expecting lots of users (generally if you have more than 5 users, you should use a different database system).

I assume the program proceeds by opening a table exclusively when a user adds in a new record. hence @@identity should work correctly isnt it?

Not really.. the program should not open the table exclusively, it should only block other users from editing exactly the same record (and only if you have specified that).

I don't know the details of how @@identity works in Access, but I presume there is a separate copy for each user, rather than one copy for the whole database.

[jp140768]

MyRSTeam.Open "tblTeamBuilder", conn, adOpenDynamic, adLockOptimistic

If you replace the adOpenDynamic with adOpenKeyset, you can get the value from the identity field immediately after the Update statement, by just referring to it.

eg Identity = MyRsTeam..Fields("ProjectID").Value

HTH