Changing structure of multiple tables
- Thread starter luapnotro
- Start date
- Local time
- Yesterday, 19:54
- Joined
- Feb 19, 2002
- Messages
- 47,578
You would need to do it with VBA and DAO. I don't have any code samples, but you can find some in help. Look up "fields collection".
Wouldn't it be better to change your design so that you don't need 80 identical tables? I'm guessing that you have created a database that looks like a spreadsheet.
Wouldn't it be better to change your design so that you don't need 80 identical tables? I'm guessing that you have created a database that looks like a spreadsheet.
Pat Hartman said:
Thanks for that but I was hoping to do this in Access itself - it can't be an unusual thing to want to do with multiple tables.
No, it doesn't look like a spreadsheet at all - the tables contain data for 80 students (currently in 13 fields but I want to extend that to 16). I took this design decision for speed reasons - each table has new data inserted on a daily basis for each student.
Paul.
neileg
AWF VIP
- Local time
- Today, 00:54
- Joined
- Dec 4, 2002
- Messages
- 5,975
Looks like a spreadsheet approach to me!luapnotro said:
You should have one table with a field that distinguishes which of 80 students the record relates to. There's no speed issue in terms of data entry, and data extraction should be much easier.
neileg said:
Well, apart from my obvious difficulty in changing the structure of 80 tables instead of one
what is the advantage of a single table? I'm not duplicating any data by doing it this way and I haven't noticed there to be any added complexity to querying each table as and when required (it's a VB6 app BTW). OTOH you're clearly much more experienced than me so any words of wisdom will definitely be listened to!Paul.
neileg
AWF VIP
- Local time
- Today, 00:54
- Joined
- Dec 4, 2002
- Messages
- 5,975
Presumably you have to hard code the student IDs somewhere, or harvest them from all the tables so that you query the right table.
If you need to add a new student, you need to create a new table. With one table you just need to add a new ID.
If you need summary data, there's only one table to query.
And then there's the need to change 80 tables if you want to add a field....
If you need to add a new student, you need to create a new table. With one table you just need to add a new ID.
If you need summary data, there's only one table to query.
And then there's the need to change 80 tables if you want to add a field....
neileg said:
No, I have another table (just the one this time!) with personal details on each student. The 80 other tables carry details of their daily work and performance - each one referenced by the student ID from the personal details table.
neileg said:
Adding a new table is just a few lines of code and I doubt I will ever need to summarise anything for all students.
Sounds like I haven't made too terrible a decision (I'm just not being very PC
)Paul.
- Local time
- Today, 00:54
- Joined
- Sep 12, 2006
- Messages
- 16,048
anyway
you need to amend the tdf (tabledef) for each table, and then apply commands as appropriate
dim fld as field
set fld = tdf.createfield(name,type)
tdf.fields.append fld
As you have tabnles for each student, you need to do this for each student. As others have said, it would have made much more sense to have a single table for all students.
if you have a split dbs its a bit more code, as you will need to open the back end from the front end to add the fields
you need to amend the tdf (tabledef) for each table, and then apply commands as appropriate
dim fld as field
set fld = tdf.createfield(name,type)
tdf.fields.append fld
As you have tabnles for each student, you need to do this for each student. As others have said, it would have made much more sense to have a single table for all students.
if you have a split dbs its a bit more code, as you will need to open the back end from the front end to add the fields
