473,287 Members | 1,659 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Code to add a field to multiple tables in design mode

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

Dec 24 '06 #1
5 4058
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.com" <u6836@uwewrote in message
news:6b37ec654f421@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

Dec 24 '06 #2
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.field2

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

Dec 24 '06 #3
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.field2
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.LastUpd 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.com" <u6836@uwewrote in message
news:6b3957e2aa0a8@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.field2

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


Dec 24 '06 #4
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 subAddFieldToEveryTable()
On Error Resume Next
Dim db As Database
Dim tdf As TableDef

Set db = CurrentDb
For Each tdf In db.TableDefs
tdf.Fields.Append tdf.CreateField("MyNewField", dbText, 50)
db.TableDefs.Append tdf
Next
Set tdf = Nothing
Set db = Nothing
End Sub
'###########
"rdemyan via AccessMonster.com" <u6836@uwewrote in message
news:6b37ec654f421@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

Dec 24 '06 #5
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 subAddFieldToEveryTable()
On Error Resume Next
Dim db As Database
Dim tdf As TableDef

Set db = CurrentDb
For Each tdf In db.TableDefs
tdf.Fields.Append tdf.CreateField("MyNewField", dbText, 50)
db.TableDefs.Append 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.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1

Dec 26 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Rick Caborn | last post by:
Does anyone know of a way to execute sql code from a dynamically built text field? Before beginning, let me state that I know this db architecture is built solely for frustration and I hope to...
45
by: Steven T. Hatton | last post by:
This is a purely *hypothetical* question. That means, it's /pretend/, CP. ;-) If you were forced at gunpoint to put all your code in classes, rather than in namespace scope (obviously classes...
26
by: pb648174 | last post by:
I have a table called BidItem which has another table called BidAddendum related to it by foreign key. I have another table called BidFolder which is related to both BidItem and BidAddendum, based...
8
by: Vladimir | last post by:
Hello, I have a table in MS Access database. It has one field (with BYTE datatype) that has several properties set in Lookup tab of table Design View. Display Control = Combo Box. Row Source...
13
by: Peter | last post by:
Can anyone tell me how to change the data type of a field in a table created with a make table query? The field is a binary and must be changed to text. alternately does anyone know how to specify...
4
by: Wayne | last post by:
I'm trying to understand at what point the use of code tables contributes to the efficient running of a database when weighed up against the added work of implementing them in data entry and...
18
by: Dixie | last post by:
Can I set the Format property in a date/time field in code? Can I set the Input Mask in a date/time field in code? Can I set the Format of a Yes/No field to Checkbox in code? I am working on...
3
by: silvastein | last post by:
It would be great if someone can help me with this. It seems like it should either be easy (so far, not!) or maybe it isn't possible. Here goes: Table B has TextField1 that looks up values...
4
by: Don Do | last post by:
Help I built a form/subform/subsubform setup using the access forms wizard. I have a table1 = parent, table2 = child, table3 = (grandchild?). There will be multiple records in table2 that tie...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.