Code to add a field to multiple tables in design mode

[rdemyan via AccessMonster.com]

I have a need to add another field to all of my tables (over 150). Not data,
but an actual field.

Can I code this somehow. So the code presumabley would loop through all the
tables, open each table in design mode and then add the new field and set its
properties.

Thanks.

--
Message posted via http://www.accessmonster.com

['69 Camaro]

Re: Code to add a field to multiple tables in design mode

Hi.

I have a need to add another field to all of my tables (over 150).

Whoa! You need to add a column (presumably a foreign key) to more than 150
tables in your database? This sounds like a design problem. (One that you're
going to create for yourself, not one you're trying to work your way out of.)
Perhaps you can explain what it is that needs to be added to the present design
now that the organization's needs have changed since the original design was
implemented, and we can possibly offer other suggestions.

Can I code this somehow.

Yes. It's very easy. Each table needs a table lock while it's being modified,
so other users should be out of the database while this code runs to prevent
locking problems. But first let's discuss _why_ so many of these tables need a
new column, instead of only a half dozen or so tables needing a foreign key to
relate to a new table's primary key.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.


"rdemyan via AccessMonster.c om" <u6836@uwewro te in message
news:6b37ec654f 421@uwe...

I have a need to add another field to all of my tables (over 150). Not data,
but an actual field.
>
Can I code this somehow. So the code presumabley would loop through all the
tables, open each table in design mode and then add the new field and set its
properties.
>
Thanks.
>
--
Message posted via http://www.accessmonster.com
>

[rdemyan via AccessMonster.com]

Re: Code to add a field to multiple tables in design mode

Okay, but remember you asked for it :).

I'm creating my own table synchronization and opting out of Access
replication and syncing. Let's not get into why I want to do this, but I
have considered the problem carefully and feel comfortable that I can come up
with something that will work for my unique situation.

I'm going to take data that has been updated in the tables of a remote user
and write it to a parallel temporary database. The records to write will be
the incremental updates/additions. This small temporary database then can
be quickly file copied to a server and dropped in a folder for later updating
of the server backend file (homegrown indirect syncing).

The problem is deletions. So I thought I would add a field to the tables in
the temp database that indicates whether the record in any table is to be
added/edited or deleted from the corresponding table when this temp database
data is imported into the server backend.

I'm concerned about the fact that the tables in the small temp updateDB will
be different from the corresponding tables in the server backend by this one
field that indicates if the record should be added/updated or deleted.

Because I have so many tables, I want to be able to loop through the
tabledefs and use Select* or not have to specify each field specifically to
retrieve/update the records from any table so that I can do the Joins, etc.
I don't want the fact that the tables have one column difference to screw up
my ability to use Select * or not specify the fields specifically.

As an example of what I'm talking about

UPDATE table1
INNER JOIN table1 ON table1.PK = table2.PK
SET table1.field = table2.field2
WHERE table1.field <table2.field 2

I'm not sure if I'm going to be trying to link tables from the the files or
if I'll have to specify the files names in the action query (I don't know how
yet and not even sure if I can), but this is just an example. What happens in
this action query when table2 has that extra field that table1 doesn't??

Maybe things won't get screwed up anyway, but I'm not sure because I've never
done this before. So I thought I would pose this question.

Thanks.

'69 Camaro wrote:

>Hi.
>

>I have a need to add another field to all of my tables (over 150).

>
>Whoa! You need to add a column (presumably a foreign key) to more than 150
>tables in your database? This sounds like a design problem. (One that you're
>going to create for yourself, not one you're trying to work your way out of.)
>Perhaps you can explain what it is that needs to be added to the present design
>now that the organization's needs have changed since the original design was
>implemented, and we can possibly offer other suggestions.
>

>Can I code this somehow.

>
>Yes. It's very easy. Each table needs a table lock while it's being modified,
>so other users should be out of the database while this code runs to prevent
>locking problems. But first let's discuss _why_ so many of these tables need a
>new column, instead of only a half dozen or so tables needing a foreign key to
>relate to a new table's primary key.
>
>HTH.
>Gunny
>
>See http://www.QBuilt.com for all your database needs.
>See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
>http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
>

>I have a need to add another field to all of my tables (over 150). Not data,
>but an actual field.

>[quoted text clipped - 4 lines]

>>
>Thanks.

--
Message posted via http://www.accessmonster.com

['69 Camaro]

Re: Code to add a field to multiple tables in design mode

Hi.

Okay, but remember you asked for it :).

Yeah. But only _after_ you asked for a lot of trouble by trying to replace
replication with your own home grown data sync system. I will start off by
saying this is not a good idea, even for people with plenty of experience with
SQL. I don't want to be mean to you, but I have to tell you this. You aren't
there yet. The only way to get there is for you to do exercises just like this
one, until you become experienced. That's why I'm not going to lead you down
the garden path and give you the solution right away. I'll point out the
mistakes in your theories and your queries, make suggestions, and give you
example queries that are used in similar (though not identical) situations.

The problem is deletions. So I thought I would add a field to the tables in
the temp database that indicates whether the record in any table is to be
added/edited or deleted from the corresponding table when this temp database
data is imported into the server backend.

Oh, boy. Let's come back to this idea later. (NOTE: I wouldn't design it like
that.)

I'm concerned about the fact that the tables in the small temp updateDB will
be different from the corresponding tables in the server backend by this one
field that indicates if the record should be added/updated or deleted.

You don't need to worry about this because each column will need to be named
explicitly to update it with the SET keyword in the query.

Because I have so many tables, I want to be able to loop through the
tabledefs and use Select*

Sorry. Not gonna happen. Can't "SET * = SELECT * FROM . . . " You can append
records with "SELECT * " but updating records doesn't work with that type of
syntax.

UPDATE table1
INNER JOIN table1 ON table1.PK = table2.PK
SET table1.field = table2.field2
WHERE table1.field <table2.field 2

You have a typo. The JOIN should be on Table2, not Table1 joining itself.
Also, I can see that you didn't test this air code out, because once you fix the
table name, you'll find that only the records in both tables that have the same
primary key will be updated from the new record to the baseline record. If the
new table (table2) has any additional records, they'll be ignored. That's why
an INNER JOIN won't work. You need an OUTER JOIN.

The following is an example where the baseline table, tblMembers, is being
updated by records in a linked table, tblMembers_Link . Both tables have a
compound primary key, MbrName and UserID. Sometimes the columns in these tables
have zero-length strings (not a good design practice, but that's what we get
with legacy data sometimes). Because of the way this query is written, existing
records in the baseline table will be updated with the new data (if there is
any), and any new records in tblMembers_Link will be appended to the baseline
table. Therefore, it's an update _and_ an append query all rolled into one.

UPDATE tblMembers AS BL RIGHT JOIN tblMembers_Link AS NGR
ON (BL.MbrName = NGR.MbrName) AND (BL.UserID = NGR.UserID)
SET BL.UserID = IIF((ISNULL(NGR .UserID) OR (NGR.UserID = "")), BL.UserID,
NGR.UserID),
BL.MbrName = IIF((ISNULL(NGR .MbrName) OR (NGR.MbrName = "")), BL.MbrName,
NGR.MbrName),
BL.LastUpd = IIF((NGR.LastUp d NZ(BL.LastUpd, #1/1/1900#)), NGR.LastUpd,
BL.LastUpd);

I want you to build a query that updates one of your baseline tables with your
linked UpdateDB table of the same structure. Put some sample data into both
tables and test your query until you are satisfied that running the query will
update the baseline and add new records from the UpdateDB table. When you're
confident with your query, you can post it here.

Why am I making you do the work? Because you need the practice and you need to
work out some of the gotchas yourself if you're going to become good enough with
SQL to pull off tasks like this successfully without help. Replication is much
more complex than it looks. You will have to build 150+ update queries like
this one. You will also have to build 150+ delete queries. You need to
consider whether or not record conflicts have to be dealt with, and be able to
carry this out on 150+ tables.

Honestly, I'd recommend you stick with built-in replication instead of building
your own. But there are other alternatives to replication. Have you considered
Terminal Server? How about a Web application for the offsite personnel?
Perhaps Sharepoint Services?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.


"rdemyan via AccessMonster.c om" <u6836@uwewro te in message
news:6b3957e2aa 0a8@uwe...

Okay, but remember you asked for it :).
>
I'm creating my own table synchronization and opting out of Access
replication and syncing. Let's not get into why I want to do this, but I
have considered the problem carefully and feel comfortable that I can come up
with something that will work for my unique situation.
>
I'm going to take data that has been updated in the tables of a remote user
and write it to a parallel temporary database. The records to write will be
the incremental updates/additions. This small temporary database then can
be quickly file copied to a server and dropped in a folder for later updating
of the server backend file (homegrown indirect syncing).
>
The problem is deletions. So I thought I would add a field to the tables in
the temp database that indicates whether the record in any table is to be
added/edited or deleted from the corresponding table when this temp database
data is imported into the server backend.
>
I'm concerned about the fact that the tables in the small temp updateDB will
be different from the corresponding tables in the server backend by this one
field that indicates if the record should be added/updated or deleted.
>
Because I have so many tables, I want to be able to loop through the
tabledefs and use Select* or not have to specify each field specifically to
retrieve/update the records from any table so that I can do the Joins, etc.
I don't want the fact that the tables have one column difference to screw up
my ability to use Select * or not specify the fields specifically.
>
As an example of what I'm talking about
>
UPDATE table1
INNER JOIN table1 ON table1.PK = table2.PK
SET table1.field = table2.field2
WHERE table1.field <table2.field 2
>
I'm not sure if I'm going to be trying to link tables from the the files or
if I'll have to specify the files names in the action query (I don't know how
yet and not even sure if I can), but this is just an example. What happens in
this action query when table2 has that extra field that table1 doesn't??
>
Maybe things won't get screwed up anyway, but I'm not sure because I've never
done this before. So I thought I would pose this question.
>
Thanks.
>
'69 Camaro wrote:

>>Hi.
>>

>>I have a need to add another field to all of my tables (over 150).

>>
>>Whoa! You need to add a column (presumably a foreign key) to more than 150
>>tables in your database? This sounds like a design problem. (One that you're
>>going to create for yourself, not one you're trying to work your way out of.)
>>Perhaps you can explain what it is that needs to be added to the present
>>design
>>now that the organization's needs have changed since the original design was
>>implemented , and we can possibly offer other suggestions.
>>

>>Can I code this somehow.

>>
>>Yes. It's very easy. Each table needs a table lock while it's being
>>modified,
>>so other users should be out of the database while this code runs to prevent
>>locking problems. But first let's discuss _why_ so many of these tables need
>>a
>>new column, instead of only a half dozen or so tables needing a foreign key to
>>relate to a new table's primary key.
>>
>>HTH.
>>Gunny
>>
>>See http://www.QBuilt.com for all your database needs.
>>See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
>>http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
>>

>>I have a need to add another field to all of my tables (over 150). Not
>>data,
>>but an actual field.

>>[quoted text clipped - 4 lines]

>>>
>>Thanks.

>
--
Message posted via http://www.accessmonster.com
>

[tombsy]

Re: Code to add a field to multiple tables in design mode

nice one Clart

Heres the code using DAO (not ADO)
You may need to reference DAO 3.6 if not already done to get this working,
but its simple.

The error handler will cope with the system objects, which cant add a field
to.
NOTE: Backup your database first.....

Stick this into a module


'###########
Sub subAddFieldToEv eryTable()
On Error Resume Next
Dim db As Database
Dim tdf As TableDef

Set db = CurrentDb
For Each tdf In db.TableDefs
tdf.Fields.Appe nd tdf.CreateField ("MyNewField ", dbText, 50)
db.TableDefs.Ap pend tdf
Next
Set tdf = Nothing
Set db = Nothing
End Sub
'###########


"rdemyan via AccessMonster.c om" <u6836@uwewro te in message
news:6b37ec654f 421@uwe...

>I have a need to add another field to all of my tables (over 150). Not
>data,
but an actual field.
>
Can I code this somehow. So the code presumabley would loop through all
the
tables, open each table in design mode and then add the new field and set
its
properties.
>
Thanks.
>
--
Message posted via http://www.accessmonster.com
>

[rdemyan via AccessMonster.com]

Re: Code to add a field to multiple tables in design mode

Thanks!

tombsy wrote:

>nice one Clart
>
>Heres the code using DAO (not ADO)
>You may need to reference DAO 3.6 if not already done to get this working,
>but its simple.
>
>The error handler will cope with the system objects, which cant add a field
>to.
>NOTE: Backup your database first.....
>
>Stick this into a module
>
>'###########
>Sub subAddFieldToEv eryTable()
>On Error Resume Next
>Dim db As Database
>Dim tdf As TableDef
>
>Set db = CurrentDb
>For Each tdf In db.TableDefs
tdf.Fields.Appe nd tdf.CreateField ("MyNewField ", dbText, 50)
db.TableDefs.Ap pend tdf
>Next
>Set tdf = Nothing
>Set db = Nothing
>End Sub
>'###########
>

>>I have a need to add another field to all of my tables (over 150). Not
>>data,

>[quoted text clipped - 7 lines]

>>
>Thanks.

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200612/1