Thread: Update command is not passing change back to database ... sometimes.

No, you use rows for everything... One Items table with the various item specs. Another table Prices with a date field, a foreign item key field and the price listed for that item at that date. Then you query them:

SELECT * FROM [Items] INNER JOIN [Prices] ON [Items].[ItemId] = [Prices].[ExItemId] WHERE [Prices].[DateOfPrice] = CONVERT(DateTime, '05/04/2012')

This makes it completely scalable, and you can add more features in the future simply by adding more relationships to other tables containing whatever additional info you want to add. For example let's say you also wanted to track the source (let's say supplier name) of each daily price, you could add a Sources table with fields for the supplier's contact info, and add an ExSourceId field to your Prices table, and modify your query like so to retrieve the contact info of the price source for one particular item on one particular day:

SELECT *

FROM [Items] INNER JOIN [Prices]

                ON [Items].[ItemId] = [Prices].[ExItemId]

             INNER JOIN [Sources]

                ON [Sources].[SourceId] = [Prices].[ExSourceId]

WHERE [Prices].[DateOfPrice] = CONVERT(DateTime, '05/04/2012')

  AND [Items].[ItemId] LIKE 'PART10293'

Hmmmm, I hadn't thought of it that way. Though with 250 items being tracked (roughly), that would mean 250 rows added into the Prices table every day, that would be roughly 91250 rows a year. I think that's over the Access limit. lol

Maybe I should make another run at MySQL.

I have seen SQL tables with over 700K rows, that shouldn't be an issue. (After checking for SQL Express the only limit on rows per table is total available storage. For 10GB in SQL Express, with the maximum row size of a little less that 8K, that means at least 1.2 million rows. Realistically if you store 50 bytes per row it's more in the range of 20 million rows...) .And for the last time, yes, switch away from Access...

And welcome to the wonderful world of actually relational databasing!

You are wasting your time and ours fighting this. I am telling you that there is absolutely never, EVER a need to add and remove columns in the normal running of an application. Stop trying to prove that wrong and start looking for a way to represent your data in such a way you can use your database the way it was designed and intended to be used. If you want to store a list of items then you do it in a table where there is one row per item. If you want to be able to indicate which items are being tracked then you either use a column in that items table that you can set to true or false or else you have another table for tracked items where there is one row per tracked item. If you use a second table then it will contain a foreign key from the first table. If you need to keep a history then you have another table for that where there is one row per history record. Again, there would be a foreign key from one of the other tables.

One of the specific intentions behind relational databases is that you will only ever need to add rows and not columns. I have created many, many applications with many different database schemas and not once have I ever needed to add and remove columns on the fly. I don't know any other developer who has either, because relational databases are specifically designed so that you don't. Do you really think that your scenario is so special that noone who designs RDBMS or the databases in them has considered it? Every time I have encountered someone who believed as you do, i.e. that they needed to add and remove columns on the fly, what was actually needed was to add an extra table with a foreign key.

If you provide a single, full and clear description of the physical system that you want to model, I will tell you how I would design a database to do it. I can guarantee you 100% that it will not involve the need to add or remove columns at run time.