A few DB questions..

[PCRIDE]

Summary of DB
External linked Excel File - Read only table, populates raw data
Access local table - Analysis table, used to root cause the raw data above

Both of these are combined into 1 main table with a query and pulled into a Form

The Form
Main form - for the Read only data above
Subform - for the Analysis table above

everything works fine and I have a 1 to many relationship to link the form and subform. Yeahhaaa...

Ok, All my drop down fields choices in the Analysis table pull from an external Excel file using Named Ranges, works great..

Now if I need to add addtional choices I just update the Excel tables, all this works great..

The issue is when I need to add a field to the Analysis table, lets say I need to add another analysis category. I have to add the field in the Analysis table in design mode, I have to run the query that updates the main table, it prompts me to update x amount of records then warns me that it will delete all the data, thats fine, I do it and it refreshes. Now I need to update the query to pull in the new field. I am doing this manually.

Question
1) Need to run the query upon opening DB and slient update (no prompts)
2) How can I use an interface to add a field to the Analysis table then have it update the query with the new field?

 

[Trevor G]

Summary of DB
External linked Excel File - Read only table, populates raw data
Access local table - Analysis table, used to root cause the raw data above

Both of these are combined into 1 main table with a query and pulled into a Form

The Form
Main form - for the Read only data above
Subform - for the Analysis table above

everything works fine and I have a 1 to many relationship to link the form and subform. Yeahhaaa...

Ok, All my drop down fields choices in the Analysis table pull from an external Excel file using Named Ranges, works great..

Now if I need to add addtional choices I just update the Excel tables, all this works great..

The issue is when I need to add a field to the Analysis table, lets say I need to add another analysis category. I have to add the field in the Analysis table in design mode, I have to run the query that updates the main table, it prompts me to update x amount of records then warns me that it will delete all the data, thats fine, I do it and it refreshes. Now I need to update the query to pull in the new field. I am doing this manually.

Question
1) Need to run the query upon opening DB and slient update (no prompts)
2) How can I use an interface to add a field to the Analysis table then have it update the query with the new field?

Answer to question1, use a macro to run the query via an autoexec macro (which means it runs what you place in the autoexec when you open the database), in the macro you can setwarnings Off so you don't get the prompts.

question2, if I understand correctly you want to use a form that will add a field to your table then you would need some vba code, something like this. If you want to set the field by using your own name via a text box then you will have to add a text box and refer to it in the code.

Sub AddFieldCombined()
Dim db As Database
Dim tdf As TableDef
Dim fld As DAO.Field
Dim fld1 As DAO.Field
Dim fld2 As DAO.Field
Set db = CurrentDb
Set tdf = db.TableDefs("Combined")
Set fld = tdf.CreateField("BranchAndTeam", dbText, 50)
tdf.Fields.Append fld
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

 

[SydneyGeek]

If another category requires a new field, you should rethink your design.
Instead of having (for example)

<title>Excel Jeanie HTML</title><table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Calibri,Arial; font-size: 11pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td>Client</td> <td>Widget</td> <td>Gadget</td> <td>Doodad</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>Big Corp</td> <td style="text-align: right;">25</td> <td style="text-align: right;">35</td> <td style="text-align: right;">12</td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

You need

<title>Excel Jeanie HTML</title><table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Calibri,Arial; font-size: 11pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td>Client</td> <td>Item</td> <td>Amount</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td>BigCorp</td> <td>Widget</td> <td style="text-align: right;">25</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td>BigCorp</td> <td>Gadget</td> <td style="text-align: right;">35</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td>BigCorp</td> <td>Doodad</td> <td style="text-align: right;">12</td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

It takes more rows, but the design stays stable.

Denis