How to add a new field for existing table programmatically ?

[Chlaris]

Dear all,

I have a table and want to create a new field for that table.
The new field attribute is auto number.
How do I write the code ?
I'm using DAO.
Thanks.

Chlaris

 

[Tom van Stiphout]

On Thu, 28 Aug 2008 09:40:43 +0700, "Chlaris"

Here is the perfect link:
http://www.mvps.org/access/tables/tbl0016.htm

And bookmark that site!

-Tom.
Microsoft Access MVP

 

[Arvin Meyer [MVP]]

Something like this ought to do it:

Dim dbs As Database
Dim tdf As TableDef, fld1 As Field, fld2 As Field
Dim idx As Index, fldIndex As Field

' Return reference to current database.
Set dbs = CurrentDb

Set tdf = dbs.TableDefs("tblTest")
Set fld1 = tdf.CreateField("ContactID", dbLong)
fld1.Attributes = fld1.Attributes + dbAutoIncrField

' Create primary key index.
Set idx = tdf.CreateIndex("PrimaryKey")
Set fldIndex = idx.CreateField("ContactID", dbLong)

 

[Larry Linson]

Dear all,

I have a table and want to create a new field for that table.
The new field attribute is auto number.
How do I write the code ?
I'm using DAO.
Thanks.

Just as a matter of interest -- are you creating a wizard to create a table
at design time?

I ask because I've been in the business a long time, and used Access daily
since January 1993, and never had a requirement to add a field, especially
an autonumber field, at runtime.

If you aren't creating a wizard, and want to share what you have and what
you are trying to accomplish rather than how you are trying to accomplish
it, someone may be able to be of more help than assisting you to do
something that may not be "good practice" anyway.

Larry Linson
Microsoft Office Access MVP

 

[David W. Fenton]

Just as a matter of interest -- are you creating a wizard to
create a table at design time?

I ask because I've been in the business a long time, and used
Access daily since January 1993, and never had a requirement to
add a field, especially an autonumber field, at runtime.

Exactly. Adding fields (particularly Autonumbers) in a producation
app indicates a completely lack of planning for the project.

 

[MikeR]

Exactly. Adding fields (particularly Autonumbers) in a producation
app indicates a completely lack of planning for the project.

Never programmed anything for the government, huh?
Needs can and do change.
Mike

 

[David W. Fenton]

Never programmed anything for the government, huh?
Needs can and do change.

But you should never program the field addition in your production
app. You may log on and change the back end structure, but coding it
into the front end is just a recipe for disaster.

And, in any case, your comment doesn't negate my assertion, that the
practice indicates a complete lack of proper planning,

 

[MikeR]

But you should never program the field addition in your production
app. You may log on and change the back end structure, but coding it
into the front end is just a recipe for disaster. True.

And, in any case, your comment doesn't negate my assertion, that the
practice indicates a complete lack of proper planning,

Regulatory changes. *NO* analyst can anticipate that.

 

[Larry Linson]

Mike -- the point is that, anticipated or not, making changes to the back
end from a production front end is a "Risky Business", and _that_ indicates
improper planning.

STEP 1: With a split database, use your development backend (a copy of the
production backend) to make necessary table and relationship changes. Use
your development copy of the frontend to make necessary other changes, and
test.

STEP 2: Now, prepare for distribution: set a flag (you should have a flag in
the backend) that prevents users from logging in until they have the revised
version of the front end. Backup the backend, and then apply the
modifications that you developed in Step 1. Be sure the flag is set in this
copy to block users by version. Copy the modified backend to the shared file
for production, replacing the unmodified (but already backed up) backend.

STEP 3: Follow your procedures (see "Versioning" article at
http://accdevel.tripod.com or the "AutoFEUpdater" at
http://www.granite.ab.ca for two approaches to handling this) to distribute
a new front end to the users. The new frontend will have an update version
number to match the one checked in the "block" field in the backend, so it
will be usable with the modified backend.

Larry Linson
Microsoft Office Access MVP

 

[David W. Fenton]

Regulatory changes. *NO* analyst can anticipate that.

No dispute there. The question is *how* you implement the change.
I'm recommending doing it by hand, not putting it in the users'
front end (which would mean a massive waste of resources testing for
whether the code needs to run or not).

 

[Paul Shapiro]

I have been distributing backend updates for years via code in the frontend.
I don't have any direct access to most of my clients' backend databases. I
add a custom version property to both the backend and frontend databases,
and also to every table in the backend. Every time the frontend application
is launched, it compares the frontend version number to the backend. If the
backend database is a lower version, the frontend retrieves a list of
backend tables to be modified from a local table in the frontend, a table I
maintain and for which there isn't any client interface. The backend table
version numbers are compared to the required version number. If there are
any backend tables to be modified, the frontend contains the necessary code.
The user is prompted to approve the change, and if so, the code makes the
updates. Once the backend is successfully updated, the frontend sets the
backend table and database version number equal to the present frontend
version number, so future program invocations don't use any significant
resources at startup. It's a few lines of code to compare the 2 db version
numbers. I also display the program version number on the main menu form or
the application caption, so it's easy for me to verify versions when a
client has issues.

This has worked very well for me in small client offices. I don't remember
ever having a problem. The code for performing the backend updates can get
fairly complex, but the only alternative I see is requiring the client to
send me their backend database for manual updating. I look at this as a
reliable form of backend version control. It works especially well when I
have multiple clients using the same application. They don't all need the
same changes at the same time, but it's easy for me to keep the front and
back ends identical. When a client eventually gets an updated frontend, the
backend updates are automatic.

 

[Larry Daugherty]

Paul,

More power to you. I'm chicken about things like that but suitable
care should make it safe enouth. Are you making sure that you have
both pre-conversion and post conversion backups when all is said and
done?

--

Mike,

Have you checked and double-checked your assumptions about the use of
the Autonumber datatype? Its only valid use is to provide unique long
integers for use as surrogate Primary Keys. Autonumbers are *not*
guaranteed to be sequential. Autonumbers may go negative and random
while still providing uniqueness. Autonumbers can produce gaps in
numerical sequence due to things as simple as starting a new record
and then abandoning it.

Not everyone agrees with the "only valid use" statement above. They
tend to be folks who spend a lot of time tweddling and tweaking their
Autonumber datatype columns when they behave as they should but not as
the developer wanted. I go further and state that an Autonumber
datatype value should never be seen by anyone but the developer. If
you care what the value of the Autonumber happens to be then you're
mis-using it. Even as a developer, the only reason you would care
would be to assure yourself that parent and child records are related
properly. Microsoft proudly displays Autonumber values on forms in
demo applications. To my mind, that's poor practice.

Most people who miss-use Autonumbers are really trying to create and
manage a *sequence*. The best way to create and manage a sequence is
by using the DMax() function +1 on the field in question. That will
always return a value that is the highest current value in that
field/column the 'plus 1' creates the next number in sequence.. By
retrieving and inserting the new value in the last event before
writing a form's content you'll minimize the potential for a race
condition between multiple users. The beauty of that is that you can
control how your sequence behaves whereas the Autonumber datatype
follows its own, arcane, rules.

I found myself nodding along with some of David's observations and
conclusions for the simple reason that Autonumbers are suitable for
use as Primary Keys and nothing else. While there was no mention of
Primary Key in your thread, the inference has to be that there has
been / is no PK. That means that the schema was flawed at the outset.
Every table should have a primary key.

HTH
--
-Larry-
--

I have been distributing backend updates for years via code in the frontend.
I don't have any direct access to most of my clients' backend databases. I
add a custom version property to both the backend and frontend databases,
and also to every table in the backend. Every time the frontend application
is launched, it compares the frontend version number to the backend. If the
backend database is a lower version, the frontend retrieves a list of
backend tables to be modified from a local table in the frontend, a table I
maintain and for which there isn't any client interface. The backend table
version numbers are compared to the required version number. If there are
any backend tables to be modified, the frontend contains the necessary code.
The user is prompted to approve the change, and if so, the code makes the
updates. Once the backend is successfully updated, the frontend sets the
backend table and database version number equal to the present frontend
version number, so future program invocations don't use any significant
resources at startup. It's a few lines of code to compare the 2 db version
numbers. I also display the program version number on the main menu form or
the application caption, so it's easy for me to verify versions when a
client has issues.

This has worked very well for me in small client offices. I don't remember
ever having a problem. The code for performing the backend updates can get
fairly complex, but the only alternative I see is requiring the client to
send me their backend database for manual updating. I look at this as a
reliable form of backend version control. It works especially well when I
have multiple clients using the same application. They don't all need the
same changes at the same time, but it's easy for me to keep the front and
back ends identical. When a client eventually gets an updated frontend, the
backend updates are automatic.

 

[Paul Shapiro]

I have impressed upon the clients that they should be very cautious in
applying updates that include db structure updates. The code reminds them to
backup the backend db before proceeding. Most of them have automatic nightly
backups in place already. Most of them will call me to have telephone
confirmation as they proceed to allow the updates. So far, in 15 years or
more, there haven't been any serious problems.

I usually try to get a copy of their reasonably current data file for a
final test before distributing the update to them. When I feel extra
cautious, I use their data file to compare the schema to my master backend
schema. Access seems more stable now than at some points in the past, but
I've occasionally seen relationships and indexes either deleted or
duplicated.

The major drawbacks of the manual BE structure update that originally led me
to the automated process are still valid:
1. You have to make a very explicit list of the updates to be performed. My
code is 100% explicit. If I need that list anyway, the automation seems a
convenient way to maintain the list. Once the support routines were
developed, the code is pretty straightforward.

2. If I did not automate the updates, I would be responsible for maintaining
the client BE status for all clients. I don't want that job.

3. I don't like the timing requirements of having a client send me their BE
to be updated, and counting on them to make sure no one works in the live db
before I return the updated BE. I also don't like counting on the client to
send me the correct BE and to replace the returned one into the appropriate
folder.

While writing the data structure update code isn't the most fun I've ever
had, the alternatives seem less pleasant.

 

[David W. Fenton]

2. If I did not automate the updates, I would be responsible for
maintaining the client BE status for all clients. I don't want
that job.

Given the responsibility you have for keeping their application
work, I can't see how debugging the code to do the updates would be
less time and effort than just doing the updates themselves.

BTW, I require remote access from all my clients these days (with
Windows Terminal Server or VNC). Otherwise, they pay through the
nose for me to come onsite.

It allows me to provide much better customer suport, and the clients
are thrilled.

And it costs them NOTHING to provide it, other than the setup time
(which I usually do for them).

 

[Karen Anne]

Simple and elegant:

DoCmd.RunSQL "ALTER TABLE TypeYourTableNameHere ADD COLUMN [TypeYourFieldNameHere] (255);"