UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Getting Next Autonumber With Adox Is Slow On Encrypted Back End, Access 2016    
 
   
jxaccess77
post Nov 8 2019, 10:17 AM
Post#1



Posts: 6
Joined: 6-August 19



Hello,

I use the following code to determine the next autonumber in a table. (inspired from Allen Browne)

CODE
Public Function NextAutonumber(ByVal pTable As String, _
        ByVal pAutonumField As String) As Long

  
    Dim cat As New ADOX.Catalog 'Root object of ADOX.
    Dim tbl As ADOX.Table       'Each Table in Tables.
    Dim col As ADOX.Column      'Each Column in the Table.
    

    Set cat.ActiveConnection = CurrentProject.Connection
    Set tbl = cat.Tables(pTable) ''''''''''''''''''' This is the very slow step lasting ~ 15 sec
    Set col = tbl.Columns(pAutonumField)
    NextAutonumber = col.Properties("Seed")

    Set cat = Nothing
    Set tbl = Nothing
    Set col = Nothing

End Function


This code is run to check the autonumber on a Back End. When the back end is encrypted, the execution of the line highlighted is slow (~ 15 sec), whereas without changing anything with the database, if the back end is decrypted, the execution is almost instantaneous.

I use the references to Microsoft ADO Ext 6 for DLL and Microsoft ActiveX Data Objects 6.1 Library.

Does this sound like a known issue? Would you have an idea of a workaround, as I would like to keep the database encrypted?

Thanks for your help.

I use Access 2016 32 bits on Windows 10 64 bits.









Go to the top of the page
 
Doug Steele
post Nov 8 2019, 11:34 AM
Post#2


UtterAccess VIP
Posts: 22,228
Joined: 8-January 07
From: St. Catharines, ON (Canada)


welcome2UA.gif

I'm curious as to why you need to know what the next autonumber value will be. Isn't it sufficient to know what value was assigned at the time of insertion?

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
jxaccess77
post Nov 12 2019, 04:50 AM
Post#3



Posts: 6
Joined: 6-August 19



As your answer suggests, I could retrieve the autonumber by merely creating a dummy record, reading the ID and deleting it.

I wanted to use a "cleaner way" if possible.

Anyway here is the background:
I have a backup table that I compare row by row with the current table. If a record from the backup is not in the current table, I want to insert it with its ID.
I thus specify the ID when inserting. The issue is that next time I add a new record, the Autonumber is corrupted (it creates a new ID following my previous insert).
The strategy I use to counteract for this issue, is
1) Get the Next Autonumber of the table and save it in a variable
2) Insert the missing row with its ID
3) Add a new record
4) Manually change the ID of this new record by assigning the value in 1
5) Then delete this record

The result is that next time I add a new record, the numbering is equal to the number in (1) plus 1 which is OK. Ideally, I would like to actually insert the record (2) without affecting at all the autonumbering, so that I don't need the steps 3,4,5. That would be cleaner. However, I am not sure it is possible.



The code is below:

CODE
Set rs0 = db.OpenRecordset(TableName)
myquery = "SELECT * FROM " & TempTableName & " ORDER BY ID"
Set rs2 = dbTemp.OpenRecordset(myquery)

' step (1)
tempAN = NextAutonumber(TableName, "ID")

' step(2)
rs0.AddNew
For ifield = 0 To rs2.Fields.count - 1
        rs0.Fields(ifield) = rs2.Fields(ifield)
Next
rs0.Update

' step (3)
rs0.AddNew

' step (4)
rs0.Fields("ID") = tempAN 'Autonumber backup defined above
rs0.Update

' step (5)
rs0.FindFirst "ID = " & tempAN
rs0.Delete



The code above is equivalent to

CODE
Set rs0 = db.OpenRecordset(TableName)
myquery = "SELECT * FROM " & TempTableName & " ORDER BY ID"
Set rs2 = dbTemp.OpenRecordset(myquery)

' step (1)
rs0.AddNew
tempAN =rs0.Fields("ID").Value

' step(2)
For ifield = 0 To rs2.Fields.count - 1
        rs0.Fields(ifield) = rs2.Fields(ifield)
Next
rs0.Update

' step (3)
rs0.AddNew

' step (4)
rs0.Fields("ID") = tempAN 'Autonumber backup defined above
rs0.Update

' step (5)
rs0.FindFirst "ID = " & tempAN
rs0.Delete


which does not require the use of the sub using ADOX.

I guess the remaining questions are more general:
A) Why does the subroutine get so much slower when the BE is encrypted in this case?
B) Is there a way to write down an equivalent subroutine, that does not touch the Autonumber, just reads its next value, without using ADOX?
C) Is there a way to insert in a table a record (that has been previously deleted) with a specified autonumber (known to be free, since the record had been deleted before), a way that does not change the seed of the autonumbering for further records, i.e. so that steps 3,4,5 are not necessary?

Thanks for your comments and help.




This post has been edited by jxaccess77: Nov 12 2019, 05:32 AM
Go to the top of the page
 
isladogs
post Nov 12 2019, 05:09 AM
Post#4


UtterAccess VIP
Posts: 1,896
Joined: 4-June 18
From: Somerset, UK


Whilst I'm also puzzled at the need for doing this at all, wouldn't it be sufficient to just find the latest autonumber and add 1?
Whilst I know that could go wrong e.g. after compacting, if might be sufficient for your needs.

OR replace the autonumber with long integer number datatype and add 1 to the value each time a new record is added (using DMax...+1)
It will then definitely be sequential

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
jxaccess77
post Nov 12 2019, 05:43 AM
Post#5



Posts: 6
Joined: 6-August 19



How would we retrieve the latest autonumber? It is not necessarily the highest ID value, since records can be deleted as time goes...
Do you have a method?

With respect to the need of getting access to the next autonumber, I guess the answer is: No, there are workarounds for that (adding, reading ID, deleting records), not as clean as the ADOX method above , but it would work.

I am just wondering whether this is the cleanest way to do it.
Ideally I would like to be able to reinsert a record previously deleted to a table, without disturbing the autonumbering and without deleting and adding the whole table. In a clean way, so to speak.


Thanks.
Go to the top of the page
 
cheekybuddha
post Nov 12 2019, 06:33 AM
Post#6


UtterAccess Moderator
Posts: 11,909
Joined: 6-December 03
From: Telegraph Hill


I'm struggling to understand why you need to do this too.

In the back up table, just use a Long Integer primary key - not an autonumber.

Then you can insert the missing records using the actual autonumber id from the table being backed up.

Does it matter that you have missing numbers in the sequence? If so, then an autonumber is not the right type for you anyway.

--------------------


Regards,

David Marten
Go to the top of the page
 
isladogs
post Nov 12 2019, 06:52 AM
Post#7


UtterAccess VIP
Posts: 1,896
Joined: 4-June 18
From: Somerset, UK


I think we're all responding in a similar way

QUOTE
How would we retrieve the latest autonumber? It is not necessarily the highest ID value, since records can be deleted as time goes...
Do you have a method?


As already mentioned, the seed is reset after compacting so it can go out of sequence
The only way would be to add a 'datestamp' field which could be automatically populated when the record is created.
Then use a DMax on that field to find the latest record
But of course if out of sequence, the next number wouldn't be that ID value + 1.

Autonumber fields have absolutely no significance other than setting a unique value for each record.
If you want to have code of this kind, use a number field instead as outlined in my previous post and further explained by David just now


--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
jxaccess77
post Nov 12 2019, 07:30 AM
Post#8



Posts: 6
Joined: 6-August 19



QUOTE
Does it matter that you have missing numbers in the sequence? If so, then an autonumber is not the right type for you anyway.

It does not matter at all to have missing numbers in the sequence.

Let me illustrate with an example:
I have a table with 4 records (primary ID, 1,5,7,8). Let's say that if I add a new record, the next autonumber is 9.
Let's say I delete record with ID=7. The next autonumber is still 9.
Later on, I reinsert the record with ID=7 and specify the record to have ID = 7. I need this record to still have its ID equal to 7, because the primary key is what I use to relate this record to other records in other tables. Now the next autonumber is 8.
Here is the issue, the next autonumber is equal to an existing record (record with ID=8). There is a conflict here. Please correct me if this is not the case.


QUOTE
As already mentioned, the seed is reset after compacting so it can go out of sequence

I understand that, but I guess that going out of sequence does not create that kind of conflict illustrated in the example above. I have no issues going out of sequence. I just want to maintain self consistency because primary keys are used to reference related records among tables.

Maybe my issue is that I use autonumber as a primary key and to relate corresponding information among various tables? The beauty of the autonumber is that Access manages it and increments it every time a new record is created. Do you suggest that now on every form I manage manually the ID of each record (when it is added) and dissociate it from the autonumber value?

Thanks
Go to the top of the page
 
Doug Steele
post Nov 12 2019, 08:00 AM
Post#9


UtterAccess VIP
Posts: 22,228
Joined: 8-January 07
From: St. Catharines, ON (Canada)


I understand what you're saying about using the value of the Autonumber field as a foreign key in other tables: that's what most of us do.

The thing is, you don't need to know that value until you go to insert the row into the related table, and since you must have already inserted the record into the parent table, you should know what it is.

BTW, your comment "I could retrieve the autonumber by merely creating a dummy record, reading the ID and deleting it." wouldn't work unless you compacted the database afterwards, since Access doesn't reuse Autonumber values.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
jxaccess77
post Nov 12 2019, 08:11 AM
Post#10



Posts: 6
Joined: 6-August 19



QUOTE
BTW, your comment "I could retrieve the autonumber by merely creating a dummy record, reading the ID and deleting it." wouldn't work unless you compacted the database afterwards, since Access doesn't reuse Autonumber values.


The work around I mentioned is:
1) Add new record
2) read ID value
3) delete record created in 1

I think it does work in the sense that now I know that the next autonumber aftre step (3) is the value read in (2)+1. Correct?
Go to the top of the page
 
nuclear_nick
post Nov 12 2019, 08:23 AM
Post#11



Posts: 1,820
Joined: 5-February 06
From: Ohio, USA


My turn!

Yes, that could be a way. You could also look for the max value currently being used in the autonumber field and add 1, because, like Doug pointed out, Access does not re-use autonumbers.

So something like this...
CODE
SELECT MAX(<autonumfield>) AS AutoNum FROM <table>

… or similar, may work.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
isladogs
post Nov 12 2019, 09:15 AM
Post#12


UtterAccess VIP
Posts: 1,896
Joined: 4-June 18
From: Somerset, UK


Can you tell me what you have against the method others and I have suggested more than once.
That is using Number instead of Autonumber and incrementing using DMax+1.
If so, problem solved

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
Doug Steele
post Nov 12 2019, 09:46 AM
Post#13


UtterAccess VIP
Posts: 22,228
Joined: 8-January 07
From: St. Catharines, ON (Canada)


QUOTE
I think it does work in the sense that now I know that the next autonumber aftre step (3) is the value read in (2)+1. Correct?

As many of us have been trying to tell you, you should never make assumptions about how values are assigned to Autonumbers. Yes, the next autonumber should be the value read in (2)+1, but it's possible that something could prevent that from happening (especially if this is a multi-user application!)

Colin: Your approach would have the same issue for a multi-user application.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
nuclear_nick
post Nov 12 2019, 10:20 AM
Post#14



Posts: 1,820
Joined: 5-February 06
From: Ohio, USA


Sorry Colin... noticed that you'd already mentioned it.

I would think I'd also have a problem with this...
QUOTE
Later on, I reinsert the record with ID=7 and specify the record to have ID = 7.


The question would be... WHY? I could see, maybe in testing, you'd do something like that on purpose. And if I did delete #7 and had referential integrity, I wouldn't have records in the BE anymore for me to need to re-insert #7... it could become #9 without issue.

Unless you're not using referential integrity...

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
jxaccess77
post Nov 12 2019, 12:55 PM
Post#15



Posts: 6
Joined: 6-August 19



First, thanks all for your feedback,

Let me try to explain better on a simplified example why the autonumber question arises in the first place:
I have 2 tables my main database db, A and B.
Table A:
Fields: ID (Autonumber), System Name (string)
Table B:
Fields: ID (Autonumber), ParentID: Number, Part (string).
The relation between both tables is given by B.ParentID =A.ID. There is referential integrity, i.e. when I delete one record in A, the children records in B are deleted.

Now, this database is filled user by a user using 2 continuous forms. FormA, FormB. On FormB and FormA, I have 2 buttons: "Save and Close" and "Discard changes and close".
I want the user to be able to discard changes entered if he wants.
To do this I create a temporary database dbTemp that copy all the tables when the user loads formA.
If the user presses the button "Discard changes and close", I want the system to restore previous values for all records changed in table A and all related records in tableB.
I achieve this by comparing row by row db.A and dbTempA and db.B and dnTemp.B. If the rows are different, I copy all the information from db TempA into db.A for that specific record. I first scan A. Then I scan B, and update db.B with original rows in dbTemp.B.


I want to restore not only the information but also the autonumber for these records, because I first restore A and then B but I lose inbetween the information of the autonumbers of the rows I recovered in A. I guess I need to code this differently to buil a translation table Old Autonumber-> New Autonumber once A is updated and propagate the changes to table B.

QUOTE
Can you tell me what you have against the method others and I have suggested more than once.
That is using Number instead of Autonumber and incrementing using DMax+1.
If so, problem solved


I have nothing against it, except, that it sounds to me that then all relations between tables are managed by this long integer, and that I need to manually manage the incrementation of this number every time there is an update in every form. It sounds like trying to reproduce by extra code what Autonumber does for me by default. I understand that this customer counter would be more flexible as I would control the seed. I guess it is safer since then the database does not make any assumptions about Autonumbers.

Also, I understand the questions about the need for getting the next autonumber. Independently of that, my initial question was about the impact of encryption on ADOX use in the code I posted in the first post. If you have an idea about that, I would be glad to hear your opinion.


Thanks again.
This post has been edited by jxaccess77: Nov 12 2019, 01:05 PM
Go to the top of the page
 
isladogs
post Nov 12 2019, 01:22 PM
Post#16


UtterAccess VIP
Posts: 1,896
Joined: 4-June 18
From: Somerset, UK


Hi Doug
I use the DMax + 1 approach where appropriate in a number of tables in multi-user applications.

As long as the ID is only assigned at the moment the new record is saved, there shouldn't be an issue
Its never been a problem in my apps - probably for that very reason

@OP
I still disagree with you on this issue.
I'm not an expert on ADOX as I rarely use it. So no suggestions on your code. Sorry I can't help directly

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
nuclear_nick
post Nov 12 2019, 01:26 PM
Post#17



Posts: 1,820
Joined: 5-February 06
From: Ohio, USA


There's a different way to do that, that may not involve deletion.

You can mark the records for deletion without actually deleting them, awaiting confirmation. Upon confirmation, you either go ahead and delete the records or unmark them for deletion.

I don't delete much, I use a 'status' flag on most important records. Most of the time that flag is 'active' (-1) or 'inactive' (0), and I can filter or remove records based on the flag. I've way too many times had users in the old days... "Oh, I didn't mean to hit the delete key, I must have done that on accident..."

We used to call it 'fat fingering', but that was considered bad, so we called it 'chubby digits'... now it's just 'incorrect data'...

<sigh>

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
tina t
post Nov 12 2019, 03:07 PM
Post#18



Posts: 6,183
Joined: 11-November 10
From: SoCal, USA


QUOTE
We used to call it 'fat fingering', but that was considered bad, so we called it 'chubby digits'... now it's just 'incorrect data'...

<sigh>

lol! i'm with you, Nick. i rarely actually delete table records, preferring to mark them as deleted and filter them out of queries, forms, reports as appropriate. i just tell the users the records are deleted, with a warning before "deletion", and a passive block - in that the warning msg has two buttons, with the default choice always being the "nothing happens" button. the user has to make a specific decision to click the correct button to get a record to be "deleted" (or do anything else with potentially bad consequences).

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2019 - 08:13 AM