AOB
Registered User.
- Local time
- Today, 23:19
- Joined
- Sep 26, 2012
- Messages
- 615
Hi all,
I am in the process of developing a DB which will have multiple tables, one of which will contain exchange rates for a number of currencies.
My current plan is to have one field for each currency, and one record for each date. The rates are imported from a file each day and the table is updated using some VBA which generates the necessary SQL command (this works fine)
The list of currencies should remain generally static; however, for the sake of flexibility, I do have within the VBA some code which checks if a new currency is present on the file (via a recordset) and, if necessary, adds a new field to the table :
However, I'm concerned that when I eventually split the DB into FE and BE, Access will not allow this design change to the backend table at runtime (although I haven't split it yet so not sure if this will actually be a problem?)
The alternative is to have a unique record for each currency / date combination, which would reduce the number of fields to just 3, but would see a dramatic increase in the number of records added each day (instead of just 1, there would be as many as there are currencies on the file)
Can anybody advise :
AOB
I am in the process of developing a DB which will have multiple tables, one of which will contain exchange rates for a number of currencies.
My current plan is to have one field for each currency, and one record for each date. The rates are imported from a file each day and the table is updated using some VBA which generates the necessary SQL command (this works fine)
The list of currencies should remain generally static; however, for the sake of flexibility, I do have within the VBA some code which checks if a new currency is present on the file (via a recordset) and, if necessary, adds a new field to the table :
Code:
With tdf
[INDENT].Fields.Append .CreateField(rst.Fields("Ccy"), dbDouble)
With .Fields(rst.Fields("Ccy"))
[INDENT].Properties.Append .CreateProperty("DecimalPlaces", dbByte, 4)
.Properties.Append .CreateProperty("Format", dbText, "0.0000")
[/INDENT]End With
[/INDENT]End WithHowever, I'm concerned that when I eventually split the DB into FE and BE, Access will not allow this design change to the backend table at runtime (although I haven't split it yet so not sure if this will actually be a problem?)
The alternative is to have a unique record for each currency / date combination, which would reduce the number of fields to just 3, but would see a dramatic increase in the number of records added each day (instead of just 1, there would be as many as there are currencies on the file)
Can anybody advise :
- Am I going to run into problems adding fields to the backend table from a frontend at runtime in this manner?
- If so, what is the best way for me to structure the table for data such as this (I'm sure I am not the first to have data in this format!)
AOB
