A few DB questions..

PCRIDE

Well-known Member
Joined
Jan 20, 2008
Messages
892
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?
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,466
Members
414,069
Latest member
StudExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top