Sometimes I use Autonumber fields for ID fields. Furthermore,
sometimes I use those same fields in orderdetail type tables. So it's
important in that case that once an autonumber key value is assigned to
a record that it doesn't change. Occasionally I find that due to
corruption or an accidental deletion and restore of a record from a
backup the autonumber field needs to be tidied up. So when I create
(through AddNew) the autonumber key to be used for joins, I also save a
copy in a backup ID field (Long). I could get by with always using the
backup ID for the join but I don't like having backup ID's that are
different from the autonumber value. I decided that I really wanted to
regenerate the autonumber field to match the Backup ID values. I
couldn't get the 'force update on autonumber field to previously
deleted values' idea from a recent post to work so I created some code
to do it. It's still a little rough but might suffice to get someone
to point out an easier way. The code is in A97. I didn't have any RI
to deal with. The form shows the tables in the database and once the
table is selected, the fields populate two comboboxes for choosing the
primary key field and the backup ID field. txtNewTableName is for the
name of the new table with the repaired autonumber values. The main
idea is to use AddNew without an Update until the next backup ID is
reached.
'-------Form Code
Option Compare Database
Option Explicit
Private Sub cbxDatabaseTabl e_AfterUpdate()
Dim MyDB As Database
Dim tdf As TableDef
Dim fld As Field
If IsNull(cbxDatab aseTable.Value) Then
cbxIDFieldName. RowSource = ""
cbxBackupIDFiel dName.RowSource = ""
cbxIDFieldName. Value = Null
cbxBackupIDFiel dName.Value = Null
Exit Sub
End If
'Put the field names in cbxIDFieldName and cbxBackupIDFiel dName
Set MyDB = CurrentDb
cbxIDFieldName. RowSourceType = "Value List"
cbxBackupIDFiel dName.RowSource Type = "Value List"
For Each fld In MyDB.TableDefs( cbxDatabaseTabl e.Value).Fields
If Nz(cbxIDFieldNa me.RowSource, "") = "" Then
cbxIDFieldName. RowSource = fld.Name
Else
cbxIDFieldName. RowSource = cbxIDFieldName. RowSource _
& ";" & fld.Name
End If
If Nz(cbxBackupIDF ieldName.RowSou rce, "") = "" Then
cbxBackupIDFiel dName.RowSource = fld.Name
Else
cbxBackupIDFiel dName.RowSource = cbxBackupIDFiel dName.RowSource _
& ";" & fld.Name
End If
Next fld
Set MyDB = Nothing
End Sub
Private Sub cmdFixAutonumbe r_Click()
If IsNull(cbxDatab aseTable.Value) Then
MsgBox ("No table was selected.")
Exit Sub
End If
If IsNull(txtNewTa bleName.Value) Then
MsgBox ("No new table name was selected.")
Exit Sub
End If
If IsNull(cbxIDFie ldName.Value) Then
MsgBox ("No ID Field was selected.")
Exit Sub
End If
If IsNull(cbxBacku pIDFieldName.Va lue) Then
MsgBox ("No Backup ID Field was selected.")
Exit Sub
End If
Call FixAutoNumber(c bxDatabaseTable .Value, txtNewTableName .Value, _
cbxIDFieldName. Value, cbxBackupIDFiel dName.Value)
MsgBox ("Done.")
End Sub
Private Sub Form_Load()
Dim MyDB As Database
Dim tdfLoop As TableDef
Set MyDB = CurrentDb
cbxDatabaseTabl e.RowSourceType = "Value List"
For Each tdfLoop In MyDB.TableDefs
If Left(tdfLoop.Na me, 4) <> "MSys" Then
If Nz(cbxDatabaseT able.RowSource, "") = "" Then
cbxDatabaseTabl e.RowSource = tdfLoop.Name
Else
cbxDatabaseTabl e.RowSource = cbxDatabaseTabl e.RowSource _
& ";" & tdfLoop.Name
End If
End If
Next
Set MyDB = Nothing
End Sub
'-------End Form Code
'-------Module Code
Option Compare Database
Option Explicit
Public Sub FixAutoNumber(s trOriginal As String, strNew As String, _
strIDFieldName As String, strBackupIDFiel dName As String)
Dim MyDB As Database
Dim AutoRS As Recordset
Dim NewRS As Recordset
Dim strSQL As String
Dim tdfAuto As TableDef
Dim fldAuto As Field
Dim lngCount As Long
Dim lngI As Long
Dim lngKey As Long
Dim tdf As TableDef
Dim fld As Field
Dim idxAuto As Index
Dim idx As Index
Dim boolFound As Boolean
'Place contents of table called strOriginal into table called
'strNew whenever the new autonumber matches BackupID
Set MyDB = CurrentDb
'Make sure index names and fields match
For Each idxAuto In MyDB.TableDefs( strOriginal).In dexes
If idxAuto.Name <> "PrimaryKey " Then
boolFound = False
For Each fld In idxAuto.Fields
If idxAuto.Name = fld.Name Then
boolFound = True
Exit For
End If
Next fld
If boolFound = False Then
MsgBox ("An index name doesn't match a field name.")
Set MyDB = Nothing
Exit Sub
End If
End If
Next idxAuto
'Delete the new table if it already exists
For Each tdf In MyDB.TableDefs
If tdf.Name = strNew Then
MyDB.Execute "DROP TABLE " & strNew & ";"
Exit For
End If
Next tdf
Set tdf = MyDB.CreateTabl eDef(strNew)
Set tdfAuto = MyDB.TableDefs( strOriginal)
For Each fldAuto In tdfAuto.Fields
If fldAuto.Type = dbText Then
Set fld = tdf.CreateField (fldAuto.Name, dbText, fldAuto.Size)
Else
Set fld = tdf.CreateField (fldAuto.Name, fldAuto.Type)
fld.Attributes = fldAuto.Attribu tes
End If
tdf.Fields.Appe nd fld
Next fldAuto
MyDB.TableDefs. Append tdf
tdf.Fields.Refr esh
For Each idxAuto In MyDB.TableDefs( strOriginal).In dexes
If idxAuto.Name <> "PrimaryKey " Then
Set idx = tdf.CreateIndex (idxAuto.Name)
If idxAuto.Name = strIDFieldName Then idx.Primary = True
If idxAuto.Require d Then idx.Required = True
idx.Fields.Appe nd idx.CreateField (idxAuto.Name)
tdf.Indexes.App end idx
End If
Next idxAuto
tdf.Indexes.Ref resh
DoEvents
strSQL = "SELECT * FROM " & strOriginal & " ORDER BY " _
& strBackupIDFiel dName & ";"
Set AutoRS = MyDB.OpenRecord set(strSQL, dbOpenSnapshot)
strSQL = "SELECT * FROM " & strNew & ";"
Set NewRS = MyDB.OpenRecord set(strSQL, dbOpenDynaset)
If AutoRS.RecordCo unt > 0 Then
AutoRS.MoveLast
lngCount = AutoRS.RecordCo unt
AutoRS.MoveFirs t
For lngI = 1 To lngCount
lngKey = 0
Do Until lngKey = AutoRS(strBacku pIDFieldName)
NewRS.AddNew
DoEvents
lngKey = NewRS(strIDFiel dName)
Loop
For Each fldAuto In tdfAuto.Fields
If fldAuto.Name <> strIDFieldName Then
NewRS(fldAuto.N ame) = AutoRS(fldAuto. Name)
End If
Next fldAuto
NewRS.Update
If lngI <> lngCount Then AutoRS.MoveNext
Next lngI
End If
AutoRS.Close
Set AutoRS = Nothing
NewRS.Close
Set NewRS = Nothing
Set MyDB = Nothing
End Sub
'-------End Module Code
James A. Fortune
Nov 13 '05
26 3825
Steve Jorgensen <no****@nospam. nospam> wrote in
news:n6******** *************** *********@4ax.c om: On Wed, 15 Dec 2004 19:43:38 GMT, "David W. Fenton" <dX********@bw ay.net.invalid> wrote:
Steve Jorgensen <no****@nospam. nospam> wrote in news:t4****** *************** ***********@4ax .com:
On Wed, 15 Dec 2004 02:51:19 GMT, "David W. Fenton" <dX********@ bway.net.invali d> wrote:
rkc <rk*@rochester. yabba.dabba.do. rr.bomb> wrote in news:Ov**** *************** @twister.nyroc. rr.com:
> Tony Toews wrote: >> "Tony D'Ambra" <td*****@swiftd sl.com.au> wrote: > >>>The negative side is that the >>>applicat ion stands a much better chance of failing if the >>>AutoNumb ered values become corrupt. >> >> >> This was a problem in some versions of Jet 4.0 but SPs fixed >> those. While I still mostly work in A97 I've never had a >> problem with Autonumber values becoming corrupt. > > What are the symptoms of a corrupt autonumber field? Do the > values already stored mysteriously change or are duplicates > suddenly allowed in a primary key field? > > Or something else?
The most common is the "loss of seed value" error, where each new record gets the same Autonumber, sometimes a previously-used value, sometimes not.
This was a very common weakness in the first versions of Jet 4, one that I'd never seen in any previous version of Jet. It was fixed about SP4 or so, if I'm remembering correctly.
Of course, it wasn't until SP6 that Jet 4 was really stable.
Actually, the seed problem is much improved, but not fixed. In a networked environment with a large number of simultaneous users, it still happens fairly regularly in my experience.
I haven't seen it at any of my clients since SP4.
How many simultaneous users are you talking about?
Well, lots. The place I was working where it was happening routinely had over 100 users hitting the same back-end.
Heavens! I've never had anything that large, and wouldn't be very
comfortable with it at all!
Well, that's good to know as another reason to try to convince
clients to move to a server back end when they get into the 50+
users territory.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
> They also get re-used when someone dies.
Not deliberately. The location of the SSN FAQ has changed:
it is now here: http://archive.cpsr.org/cpsr/privacy/ssn/ssn.faq.html
Most of the links in that document are broken. The discussion
of problems with the use of SSN's as primary keys is here: http://archive.cpsr.org/cpsr/privacy...-addendum.html
(david)
"Trevor Best" <no****@besty.o rg.uk> wrote in message
news:41******** **************@ news.zen.co.uk. .. Rick Brandt wrote: "Bas Cost Budde" <b.*********@he uvelqop.nl> wrote in message news:cp******** **@news2.solcon .nl...
Tony Toews wrote:
how do you get a natural key for a person? SSN? No.
Hey! Why not? I am not familiar with SSN but there is a pendant structure in Holland, the number should be unique. Is SSN not?
From what I hear "it's supposed to be" would be the answer to your question. Fraud and screw-ups dictate otherwise.
They also get re-used when someone dies.
-- This sig left intentionally blank
"Tony D'Ambra" wrote Firstly, I find your use of "Eh?" offensive. Your MVP status does not exempt you from the rules of simple courtesy...
It's a Canadian speech habit thing that carries over into writing... not
intended to be offensive nor discourteous. Perhaps it has some different
meaning or usage in Oz?
"Steve Jorgensen" wrote Well, lots. The place I was working where it was happening routinely had over 100 users hitting the same back-end.
Ah, that is near-record territory. Consider yourself added to the "people
whose observations we can trust" when we talk about the upper limits of
multiuser. Hey, it's good company, including michka, Stephen Forte, Drew
Wutka, and Mike Groh...
Like David, I don't have personal experience with that size user audience.
But that is quite a lot of users, and likely to stress a poor little
"desktop database" somewhat. But, as David also said, it'd be worth a check
that all users are up-to-date with SPs for Jet 4. Wouldn't it be nice if
that were a solvable problem, and you solved it for your client?
Larry Linson
Microsoft Access MVP
Larry Linson wrote: "Tony D'Ambra" wrote
> Firstly, I find your use of "Eh?" offensive. > Your MVP status does not exempt > you from the rules of simple courtesy...
It's a Canadian speech habit thing that carries over into writing... not intended to be offensive nor discourteous. Perhaps it has some different meaning or usage in Oz?
Interesting spin. In my experience "Eh?" is usually tacked on to
the end of a sentence by afore referenced Canadians.
"rkc" wrote Interesting spin. In my experience "Eh?" is usually tacked on to the end of a sentence by afore referenced Canadians.
That, too. I took Tony's use to be more-or-less equivalent to my saying
something like: "Interestin g. My own observation is...".
Larry Linson
Larry Linson wrote: "rkc" wrote
Interesting spin. In my experience "Eh?" is usually tacked on to the end of a sentence by afore referenced Canadians.
That, too. I took Tony's use to be more-or-less equivalent to my saying something like: "Interestin g. My own observation is...".
Bejeezus, that's what I thought :-)
--
This sig left intentionally blank This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Ilan Sebba |
last post by:
I have a 'supertype' table with only one field: autonumber. Call this table
the 'parent' table. There are two subtypes, 'androids' and 'martians'.
Martian have only one thing in common: they give birth to identical mules.
So each android and a martian have primary key which is a foreign key to the
parent table. Now, I want to insert a new record in 'android' or 'martian'.
This can be done easily using MS-Access forms. But I want to do...
|
by: Lee C. |
last post by:
I'm finding this to be extremely difficult to set up. I understand
that Access won't manage the primary key and the cascade updates for a
table. Fine. I tried changing the PK type to number and setting
default value to a UDF that manages the auto-numbering. Access won't
take a UDF as a default value. Okay, I'll use SQL WITHOUT any
aggregate functions, for the default value. Access won't do that
either. Okay, I create a second...
|
by: Traci |
last post by:
If I have a table with an autonumber primary key and 100 records and I delete
the last 50 records, the next record added would have a primary key of 101. Is
there any way to have the primary key start at 51 after the last 50 records are
deleted?
Thanks!
Traci
|
by: yf |
last post by:
A KB article
"http://support.microsoft.com/default.aspx?scid=kb;en-us;209599" tells
that the maximum number of records that a table may hold if the PRIMARY
key data type is set to AUTONUMBER is 4,294,967,295.
Suppose the PRIMARY key data type is set to "RANDOM" AutoNumber.
Suppose an application
(a) successfully INSERTS "X" records, then
(b) successfully DELETES "Y" records (X >= Y), then
|
by: jimfortune |
last post by:
Sometimes I use Autonumber fields for ID fields. Furthermore,
sometimes I use those same fields in orderdetail type tables. So it's
important in that case that once an autonumber key value is assigned to
a record that it doesn't change. Occasionally I find that due to
corruption or an accidental deletion and restore of a record from a
backup the autonumber field needs to be tidied up. So when I create
(through AddNew) the autonumber...
| |
by: raca |
last post by:
Please look at this code fragment:
#region Custom Code - Generated_Methods
// Generated_Discussion_Methods
public abstract IDataReader GetDiscussion();
public abstract void DeleteDiscussion(int itemID);
#endregion // Custom Code - Generated_Methods
// Generated_NEWCLASS_Methods
public abstract IDataReader GetNEWCLASS();
|
by: petebeatty |
last post by:
I have created a SQL string the properly inserts a record in the table.
However, the insert does not occur at the end of the table. Instead
it inserts a record after the last record that I viewed. This would be
OK, except it assigns a autonumber to be one greater than the last
viewed record. This causes a duplicate autonumber. I know I can
change the autonumber index (Primary Key) to not allow duplicates.
How can I force the insert...
|
by: Alan Mailer |
last post by:
A project I'm working on is going to use VB6 as a front end. The back
end is going to be pre-existing MS Access 2002 database tables which
already have records in them *but do not have any AutoNumber* fields
in them.
Correct me if I'm wrong, but I'm assuming this means that I cannot now
alter these existing Access tables and change their primary key to an
"AutoNumber" type.
If I'm right about this, I need some suggestions as to the...
|
by: gtwannabe |
last post by:
I'm having a problem with a form that uses AutoNumber as the primary
key. I have an Abort button to delete the current record and close the
form. If AutoNumber is assigned, the code executes a SQL statement
that deletes the current record. I need to be able to detect when
AutoNumber is unassigned (a new blank record) so that I can simply
close the form without running the SQL delete statement.
Unfortunately, no tests I can think of...
|
by: ashes |
last post by:
Hi,
I am creating an ecommerce website using Microsoft Visual Studio, VB.Net and MS Access 2003. I am new to VB.Net
When someone wants to register on the website, they fill out a form and the contents of the form is inserted into the MS Access database.
The Customer table in the database already has 30 records (with CustomerIDs 1 - 30) in it (from when the database was first created). The CustomerID field in the database is an...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |