-
November 24th, 1999, 11:52 PM
#1
Access : How to know what is the next autonumber generated for my primary key
I have a column whose column type is autonumber. I want to know what will be the next autonumber generated. Like if I am deleting some records in between , I am loosing the track of the count..
Is there any way for knowing the number automatically.
RM
-
November 25th, 1999, 03:23 AM
#2
Re: Access : How to know what is the next autonumber generated for my primary key
Hi,
One can never be totally categoric, but I think that knowing in advance what will be the next generated key is not possible. Anyway, in a database, a key should never be anticipated, a primary key is made to be searched, not anticipated.
If you use the Recordset.AddNew
method to insert your data, you can retreive the key value even if you have not call the Recordset.Update
method.
I suppose this not very helpfull for you, sorry for that, but may I ask you why you want to know in advance the value of the next generated key ?
Regards.
Hastur.
-
November 26th, 1999, 06:11 AM
#3
Re: Access : How to know what is the next autonumber generated for my primary key
Hi,
Thanks for your kinda reply. The reason for knowing the next auto number in advance is that, this ID should be stored in the child table; and the queries are sending in a batch mode and hence I cannot presume the ID which is being generated in the PARENT table is 1 plus to the maximum number in the database (in case if the user deletes the last ten records updated).
Regards
RM
-
November 26th, 1999, 02:11 PM
#4
Re: Access : How to know what is the next autonumber generated for my primary key
I think I had similar scenario:
On the same screen I have several controls displaying parent record (Order)and Grid for child records (Order Details). Whith displaying this hierarchy I did not have any problem. But when I needed to add a new Order with Details I had to do it step by step. My boss hates to make extra clicks (Update button for Orders). So, I analized if all required fields were filled on either KeyPress or some other events (I don't remember) and when everything was entered for Order I added the new record into the Order table, came to that record, got its ID and used it in a OrderDetail record as foreign key. Before new record for Order entered Grid control is disabled. After new Order is in a table, Grid is enabled and allows to enter details.
Normal DB design (referential integrity) doesn't allow you to enter a new record into the OrderDetails table for not existing Order. So, even if you know what ID will be generated, you cannot use this ID in an OrderDetails table. Or don't pay attention on DB design rules and create ugly DB (BTW it's much easier). You will probably have a lot of problems later (orphaned records, duplicates, details from John's order belong now to Steve's order and so on)
Vlad
-
November 26th, 1999, 07:19 PM
#5
Re: Access : How to know what is the next autonumber generated for my primary key
If you are using DAO, you can use the LastModified property to move to the record you just inserted, and then read the ID property. If you are using ADO, you can create a new recordset, ordered by DateModified field (if you have one), and then read the ID of the first record.
Charlie Zimmerman
http://www.freevbcode.com
-
November 29th, 1999, 03:57 AM
#6
Re: Access : How to know what is the next autonumber generated for my primary key
Hi,
I suggest you, in your case, to forget the SQL queries and to use Visual Basic. Create a workspace object, and instanciate it to the current workspace (Workspaces(0)), create a recordest pointing to the table in which you want to insert your datas. You can insert data in a recordset by calling the method MyRecordset.AddNew, and validate it with a MyRecordset.Update. However, you can read the identity key even if you have not called the .Update method, and use it as you wish.
If you need to perform a lot of inserts which should be all cancelled if an error occurs, then call the method MyWorkspace.BeginTrans before your inserts, and the method MyWorkspace.CommitTrans after. Do an error interruption routine (On Error Goto MyLabel), and call the method MyWorspace.RollBack in it.
So you algorithm may look like this :
[Begin Sub]
On Error Goto MyError
MyWorspace.BeginTrans
[For Statement]
MyRecordset.AddNew
MyRecordset.Fields("MyField1") = "MyValue"
MyRecordset.Fields("MyField2") = "MyOtherValue"
...
MyKey = MyRecordset.Fields("MyIdentKey")
MyRecordset.Update
MyRecordset2.AddNew
MyRecordset2.("MyChildKey") = MyKey
MyRecordset2.Fields("MyWhatEver") = "WhoKnows"
MyRecordset2.Udpate
[Next Statement]
MyWorkspace.CommitTrans
[Exit Sub]
MyError:
MyWorkspace.RollBack
[End Sub]
In this algorithm, some datas are inserted in a child table, according to the new ident key in the parent table. If an error occurs, all inserts in the two tables are cancelled.
Hope this can help you.
Best regards.
Hastur.
-
December 10th, 1999, 11:15 AM
#7
Re: Access : How to know what is the next autonumber generated for my primary key
if the key is generated adding 1 to the last inserted, you can add & update your recordset, so close the recorset and open it again ordering by key.
Now using .movelast you can go to the last record, retrieve the key and key+1 will be the next.
All this using DAO.
I'm sorry for my english.
Bye
Something over there is coding....
... and you don't know!
-
December 10th, 1999, 06:02 PM
#8
Re: Access : How to know what is the next autonumber generated for my primary key
If you do not have many updates, and (as i think to have understood) your field is a key field...something like 'SELECT MAX(myautofield) From MyAutoTable' will work fine...
If you have many updates, forget the autonumber feature and use an external counter table...
-
December 14th, 1999, 10:45 PM
#9
Re: Access : How to know what is the next autonumber generated for my primary key
I realy cannot se a problem here, or am I missing something?
if you have tblOder and tblOdeDitales then:
- Open recordset for tblOder
rst.OpenRecordset(.....)
workspace.BeginTrans
rst.AddNew
rst.Update ' after this line you logicaly created record and can get assigned value of ID
dim a as long
a=rst.Fields("ID") ' that is a future ID
workspace.rollback 'cancelling update, but you still have ID
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|