Use vba to change table design

[Peter Kinsman]

I have a database holding a number of tables which are linked using an
Autonumber primary index in the first field.
Changes to the operation of the company mean that fields will be added to
some tables. Because the old system will continue to be used while the new
system is being developed, I need to be able to export data from the old to
the new.
Because of the record number linking, I proposed doing this in stages using
vba:
1) Copy the old database in its entirety
2) Import any new tables and generate their data
3) Add any new fields to existing tables
I am happy with stages 1) and 2) but not sure about stage 3). Will I have
to open the table in Design View and then use SendKeys to make the
amendments?

Many thanks

Peter Kinsman

 

[Steve]

For #3, start by looking at TableDefs in the help file. Continue reading
from this subject how to add fields to a table.

Steve
(e-mail address removed)

 

[Tony Toews [MVP]]

I have a database holding a number of tables which are linked using an
Autonumber primary index in the first field.
Changes to the operation of the company mean that fields will be added to
some tables. Because the old system will continue to be used while the new
system is being developed, I need to be able to export data from the old to
the new.
Because of the record number linking, I proposed doing this in stages using
vba:
1) Copy the old database in its entirety
2) Import any new tables and generate their data
3) Add any new fields to existing tables
I am happy with stages 1) and 2) but not sure about stage 3). Will I have
to open the table in Design View and then use SendKeys to make the
amendments?

You're getting too complicated.

1) Don't ever consider using SendKeys. It'll cause way too many
complications.

2) When working on site I distribute updates to my clients once or
twice a day. Or maybe every three days depending on what I'm doing.
I work on my own BE on my system so response time is better and
accidents don't happen to live data.

After hours I update the tables, fields, relationships and indexes.
If I need to clean up some tables then I also would run the various
action queries at this time.

Then I make a new copy of the FE available on the server. And in the
morning the free Auto FE Updater utility copies down the FE to the
users.

For more info on the errors or the Auto FE Updater utility see the
free Auto FE Updater utility at
http://www.granite.ab.ca/access/autofe.htm at my website to keep the
FE on each PC up to date.

I also ensure the users are kicked out after a half hour.

HOW TO: Detect User Idle Time or Inactivity in Access 2000 (Q210297)
http://support.microsoft.com/?kbid=210297
ACC: How to Detect User Idle Time or Inactivity (Q128814)
http://support.microsoft.com/?kbid=128814

However we found that the code which runs on the timer event must be
disabled for the programmers. Otherwise weird things start happening
when you're editing code.

Also print preview would sometimes not allow the users to run a menu
item to export the report to Excel or others. So you had to right
click on the Previewed report to get some type of internal focus back
on the report so they could then export it. This was also helped by
extending the timer to five minutes.

The downside to extending the timer to five minutes was if a person
stays in the same form and at the same control for considerable parts
of the day, ie someone doing the same inquiries, the routine didn't
realize that they had actually done something. I'll be putting in
some logic sometime to reset this timer whenever they do something in
the program.

Tony