Initialising autonumber primary key fields

[Stapes]

Hi

I want my order numbers to start from 11100, so I ran the following
SQL procedure:

ALTER TABLE [Orders] ALTER COLUMN [PK_Order] COUNTER(11100, 1);

Unfortunately, this would not work unless you delete the relationships
the field has, then re-create them afterwards.

This is too much to expect my client to do correctly each time he
wants to initialise his database, so is there a way I can do all three
operations in VB code. i.e:

1. Delete the relationship
2. Run the SQL
3. Re-create the relationship

Stapes

 

[Allen Browne]

Would this ADOX code let you set the seed as you wish:
http://allenbrowne.com/func-ADOX.html#SetSeed

 

[Jeff Boyce]

You've described "how" you are trying to do something.

If you'll explain a bit more about "why" (i.e., what business purpose is
being served) you/your client feel it is necessary to "re-initialize your
database", folks here may be able to offer alternate approaches.

In a well-normalized relational database, it is rarely necessary to
"re-initialize", at least as I understand the term. Perhaps you could
include the definition you are using...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Sheridan]

If sequential numbering is important I'd advise against using an autonumber
column, whose purpose is to guarantee unique values, not necessarily
sequential ones. Instead, compute the next number in sequence when a new row
is inserted into the table.

There are various ways of doing this. You'll find one at the following link
which also prevents conflicts if two or more users are attempting to insert a
new row in a multi-user environment, and allows the number to be reset at
which the sequence will start when the next row is inserted.


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

 

[Stapes]

Would this ADOX code let you set the seed as you wish:
   http://allenbrowne.com/func-ADOX.html#SetSeed

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I want my order numbers to start from 11100, so I ran the following
SQL procedure:

ALTER TABLE [Orders] ALTER COLUMN [PK_Order] COUNTER(11100, 1);

Unfortunately, this would not work unless you delete the relationships
the field has, then re-create them afterwards.

This is too much to expect my client to do correctly each time he
wants to initialise his database, so is there a way I can do all three
operations in VB code. i.e:

1. Delete the relationship
2. Run the SQL
3. Re-create the relationship

Stapes- Hide quoted text -

- Show quoted text -

Thanks Allen, that looks good. I've got some of those functions
working, but SetSeed keeps failing with:

-2147467259 Invalid argument

on the line:

cat.Tables(strTable).Columns(intCol).Properties("Seed") = lngID

This line worked though:

Debug.Print strTable & " Seed=" &
cat.Tables(strTable).Columns(intCol).Properties("Seed")

So i think the fault could be with lngID.

Any ideas?

 

[Stapes]

You've described "how" you are trying to do something.

If you'll explain a bit more about "why" (i.e., what business purpose is
being served) you/your client feel it is necessary to "re-initialize your
database", folks here may be able to offer alternate approaches.

In a well-normalized relational database, it is rarely necessary to
"re-initialize", at least as I understand the term.  Perhaps you could
include the definition you are using...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi

I want my order numbers to start from 11100, so I ran the following
SQL procedure:

ALTER TABLE [Orders] ALTER COLUMN [PK_Order] COUNTER(11100, 1);

Unfortunately, this would not work unless you delete the relationships
the field has, then re-create them afterwards.

This is too much to expect my client to do correctly each time he
wants to initialise his database, so is there a way I can do all three
operations in VB code. i.e:

1. Delete the relationship
2. Run the SQL
3. Re-create the relationship

Stapes- Hide quoted text -

- Show quoted text -

My client asked me to start with the following:

Sales Orders: S11100

Invoices: 31000

Quotes: Q2100

Purchase O: P11000

However, if a record is added, then cancelled, it reverts to no 1.
The client also envisages archiving the data periodically and starting
afresh.

 

[Allen Browne]

What version of ADOX are you using? IME it's a pretty flaky library (and not
consistent between versions.)

I wrote that and tested it with 2.8, i.e.:
Microsoft ADO Ext. 2.8 for DDL and Security

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"Stapes" <[email protected]> replied in message

Thanks Allen, that looks good. I've got some of those functions
working, but SetSeed keeps failing with:
-2147467259 Invalid argument

on the line:
cat.Tables(strTable).Columns(intCol).Properties("Seed") = lngID

This line worked though:
Debug.Print strTable & " Seed=" &
cat.Tables(strTable).Columns(intCol).Properties("Seed")

So i think the fault could be with lngID.

Any ideas?

 

[Stapes]

What version of ADOX are you using? IME it's a pretty flaky library (and not
consistent between versions.)

I wrote that and tested it with 2.8, i.e.:
    Microsoft ADO Ext. 2.8 for DDL and Security

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stapes" <[email protected]> replied in message
Thanks Allen, that looks good. I've got some of those functions
working, but SetSeed keeps failing with:
    -2147467259 Invalid argument

on the line:
    cat.Tables(strTable).Columns(intCol).Properties("Seed") = lngID

This line worked though:
    Debug.Print strTable & " Seed=" &
        cat.Tables(strTable).Columns(intCol).Properties("Seed")

So i think the fault could be with lngID.

Any ideas?

Yes, I'm on 2.8

 

[Jeff Boyce]

Expecting MS Access' Autonumbers to be sequential is a little like believing
the weather report ... it could happen, but don't count on it. Autonumbers
are intended to be used as unique row identifiers, and are general unfit for
human consumption (... as you've learned!).

Instead, if you (your customer) must have a sequential numbering system,
you'll have to 'roll your own'. You can search at mvps.org/access for
"Custom Autonumber" (a misnomer) to get an idea of how to approach this.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

You've described "how" you are trying to do something.

If you'll explain a bit more about "why" (i.e., what business purpose is
being served) you/your client feel it is necessary to "re-initialize your
database", folks here may be able to offer alternate approaches.

In a well-normalized relational database, it is rarely necessary to
"re-initialize", at least as I understand the term. Perhaps you could
include the definition you are using...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi

I want my order numbers to start from 11100, so I ran the following
SQL procedure:

ALTER TABLE [Orders] ALTER COLUMN [PK_Order] COUNTER(11100, 1);

Unfortunately, this would not work unless you delete the relationships
the field has, then re-create them afterwards.

This is too much to expect my client to do correctly each time he
wants to initialise his database, so is there a way I can do all three
operations in VB code. i.e:

1. Delete the relationship
2. Run the SQL
3. Re-create the relationship

Stapes- Hide quoted text -

- Show quoted text -

My client asked me to start with the following:

Sales Orders: S11100

Invoices: 31000

Quotes: Q2100

Purchase O: P11000

However, if a record is added, then cancelled, it reverts to no 1.
The client also envisages archiving the data periodically and starting
afresh.