edit a query dynamically using vba? 4

[rxsims]

Hello,

I am dynamically adding fields to tables, and I need to add those same fields into existing queries using the same procedure.

I have no problem adding the fields to tables, but can't find any postings (here or elsewhere) on how to dynamically add a field to an existing query.

This is as far as I got:
Dim FieldName as String

Database is already open

Set qry = db.QueryDef("queryName")
??? Now I need to add FieldName to queryName

Thanks.

 

[dhookom]

I can't imagine the reason for having to add fields dynamically to queries (or tables for that matter). Normally you should add records, not fields.

You can modify the SQL property of saved queries with code like:

   Dim qry as DAO.QueryDef
   Dim db as DAO.Database
   Set db = CurrentDb
   Set qry = db.QueryDef("queryName")
   qry.SQL = "SELECT ...."

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

Depends on how complex the query SQL is. If it's fairly simple such as

Select fld1, fld2, fld3 From myTable Where fld1 < 9

then

Set qry = db.QueryDef("queryName")
qry.SQL = Left$(qry.SQL, Instr(qry.SQL, " From") -1) & _
          ", SomeNewField" & _
          Mid$(qry.SQL, Instr(qry.SQL," From"))

Not something I would want to do in a production system however. Thinks like sub-queries in the select will cause this to malfunction.

 

[PHV]

I'm with Duane:
what is the reason to have dynamically added field ?
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

Dynamically changing the schema of a database during runtime almost always indicates a poor understanding of relational database design. If you tell us what you are trying to do, we can probably give you a better alternative.

 

[rxsims]

Thanks for your responses so far.

For this particular application, I am tracking raw Ingredients going into a recipe, with data being entered at various time frequencies from multiple sources. I will then be tracking how much of the ingredients I am using, and also comparing that usage as reported from each source.

My tables are set up with like this:

Date: Date/time
Ingredient1: number
Ingredient2: number
etc.
.
Ingredientn: number.

I have 7 different tables set up to track ingredient usage in these recipes (different inputs for the data - I'm trying to compare the results from those different inputs).

I also have reports and forms based on the qeuries I want to change dynamically.

If later I need to add a new type of ingredient to the recipe(Ingredient(n+1)), I am now dynamically adding the Ingredient(n+1) field to each of the tables.

I now need to add the Ingredient(n+1) field into the queries my forms and reports are based on. I want to do this using code, as I will be adding the same field into 7 tables, 3 queries, 3 forms, 2 reports (so far!).

 

[Golom]

You are paying the price for un-normalized tables and, I predict, that this process will eventually become so top heavy with code to compensate for those tables that it will be unmanagable.

If at all possible, rethink your table structure to get them into normal form.

 

[PHV]

Did you even read the link I suggested you ?

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

Hint, you want a table structure something like:

[tt]
tbl_Recipes tbl_IngrediantsInRecipe tbl_Ingrediants
RecipeID---------RecipeID |----IngrediantID
IngredieantID---------|
[/tt]

Presto chango, no more rewriting code every time an ingrediant is added (or removed) from a recipe!

 

[rxsims]

HI Again,

PHV - thanks for the article, and I know where you and Golom are coming from in terms of me normalizing my tables now. JoeAtWork best describes what I had originally. I have the tbl_Recipes, tbl_Ingredients, and tbl_IngredientsInRecipe much the same way.

The reason why I haven't normalized my other tables more is because I am doing calculation and comparison among tables and it seemed easiest to me to have the same field in each table for those calculations and comparisons. I erred on the side of creating many tables so any complex calculations are done when the data is entered into forms instead of when I run queries for reports.

I currently have 6 tables:
(1). tbl_IngredientInventoryMaster

(2). tbl_IngredientInventoryDaily -> updated by information from (3) tbl_IngredientUsageDaily (see below)

(4) tbl_IngredientInventoryByRun -> updated by information from (5)tbl_IngredientUsageByRun (see below)

where (currently) each table has the same structure, so I can do for each field in the tables:

tbl_IngredientInventoryDaily = tbl_IngredientInventoryDaily - tbl_IngredientUsageDaily

tbl_IngredientInventoryByRun =
tbl_IngredientInventoryByRun - tbl_IngredientUsageByRun

Also, tbl_IngredientUsageByRun is calculated from a table equivalent to what JoeAtWork describes, i.e. tbl_IngredientUsageByRun = (6) tbl_IngredientsInRecipe multiplied by (amount of time recipe is being produced)

 

[rxsims]

I should have noted, that new ingredients are rarely needed to be added. Maybe one ingredient per year. That is another reason why I wasn't as worried about normalizing the tables more. It wasn't a problem for me until I realized I had to change the queries, forms and reports to include a new field each time a new ingredient was added. If I can make my tables more effient though, I definitely will.

 

[dhookom]

I would definitely normalize. Also, tables with the same structure are rarely if ever adviseable.

Duane
Hook'D on Access
MS Access MVP