VDA and table design

[dz2k7]

VBA and table design

Hi,

I just need a general idea how to manage table design with VBA modual in Access.
I want to be able to add columns, rename columns or delete columns of some table in Access using some automated procedure.
Basically i need to keep historical data and idea is to drop oldest column in a table, add new column, fill it up with the data, and rename all columns, so i would have rolling table.

I found

docmd.RunCommand acCmdRenameColumn

But i don't know how to use it right, how to tell it about the column names i wanna operate. And it tells me something like "the table is unavaliable now". Can't find a proper help too.

Thanks

 

[KenHigg]

fyi: Adding/deleting columns as a production procedure like you've described hints that you may not have an optimal table model...In other words the practice in general is bad juju. IMHO...

 

[MSAccessRookie]

I have never needed to delete a column in a table due to expiring data. Can you give an example of the design for this table? It sounds like it might be set up like a spreadsheet, and if so, I would venture a guess that most of the experts here would suggest evaluation for the purpose of redesign.

 

[dz2k7]

Re: VBA and table design

ok
spreadsheet does not work for me.
I have 2 milion lines.
how do you guys keep rolling historical data?

 

[georgedwilkinson]

A couple of things you could look at:
1. ADOX. You'll need to read up on it yourself. It is a complex topic. Essentially, it is an active x object for manipulating tables.
2. DDL - Data Definition Language. This is a robust extension to the SQL language that allows you to make just about any change to a table, index, databse, or just about any database object. It is a complex topic. You can invoke DDL via the VBA DoCmd.RunSQL() command.
3. Query defs. This is essentially using Access to store the sql of your DDL. Not a lot different that #2.

None of this is easy. I fear that if you needed to ask, the learning curve may be a bit much. You'll need to read up on any of them.

 

[KenHigg]

For starters you can either add a field that marks a record as archived or move the entire record to a duplicate 'archive' table.

 

[georgedwilkinson]

ok
how you guys keep rolling historical data?

In the same table with all the other data of the same type. Use a to and from date and/or a valid flag to indicate whether the data is current or historic.

 

[MSAccessRookie]

For starters you can either add a field that marks a record as archived or move the entire record to a duplicate 'archive' table.

I have always done something like this. Then on a cyclical basis (daily, weekly, monthly, quarterly, or annually, depending on the project), I had a maintenance procedure move any marked records to an archive table.

 

[dz2k7]

A couple of things you could look at:
1. ADOX. You'll need to read up on it yourself. It is a complex topic. Essentially, it is an active x object for manipulating tables.
2. DDL - Data Definition Language. This is a robust extension to the SQL language that allows you to make just about any change to a table, index, databse, or just about any database object. It is a complex topic. You can invoke DDL via the VBA DoCmd.RunSQL() command.
3. Query defs. This is essentially using Access to store the sql of your DDL. Not a lot different that #2.

None of this is easy. I fear that if you needed to ask, the learning curve may be a bit much. You'll need to read up on any of them.

When i change the disign manually in access disign view does it actually usues ADOX or DDL?

 

[georgedwilkinson]

To my knowledge, neither. It is manual.

And in light of your conversation with others on this thread, I highly recommend NOT pursuing code based updates of the structure of your table. There are some (limited) good reasons for it, but not in an operational system.

 

[gemma-the-husky]

you really dont want to do this

assuming you have some spreadsheet, date column thing going here - you want to delete a column, move everything left, and a new last column, right?

thats not the access paradigm

access just stores all your data vertically, in a single column, with an added date field

---------
now in this way, you NEVER have to delete old data, and you can reconstruct a table for any period, based on first principles, as it were.

Presentation is divorced form Data Storage/Structure, and getting the data structure is the key to it all

Access will handle millions of rows (not just the 64000 excel limit), subject to maximium overall size of 1gb/2Gb, depending on Access version - and even then you can get round these limits!

 

[dz2k7]

you really dont want to do this

Sure i don't want to
But i have 2 more million lines every week.
By 52 weeks it will be 104 million records.

i can't handle them everytime i run my queries.
I have the best PC in a company but it's just getting red

 

[georgedwilkinson]

Sure i don't want to
But i have 2 more million lines every week.
By 52 weeks it will be 104 million records.

This is terrifying. You should be using a real RDBMS for this.

 

[DJkarl]

Sure i don't want to
But i have 2 more million lines every week.
By 52 weeks it will be 104 million records.

i can't handle them everytime i run my queries.
I have the best PC in a company but it's just getting red

At 52 weeks you will have roughly the same amount of data in the table whether you store it vertically as suggested or in a spreadsheet like you want to. The only difference is instead of having one record with 52 columns of data you have 52 records with 1 column.

I would go with the second route, for updating then you could just use a query to delete the oldest date in the table and append the new information. Much simpler than trying to alter the table structure.

If you then want to display your table in a spreadsheet like format you could use a PIVOT query to show your results.

 

[dz2k7]

Re: VBA and table design

ok
i got the concept.
so the suggestion is to append all my weekly data to one table but add one column having a date of each download.
then every week i should calculate how old is each record in weeks and put that an additional column having values 1, 2, 3 ...
then i delete records older then 52
then i build a crosstub table having 1, 2, 3 ... on the top.

the only thing i need is how to calculate how old is the record in weeks

is it should be something like

= datediff("w", tbl.date, now())

Thank you guys for your help

 

[MSAccessRookie]

Re: VBA and table design

ok
i got the concept.
so the suggestion is to append all my weekly data to one table but add one column having a date of each download.
then every week i should calculate how old is each record in weeks and put that an additional column having values 1, 2, 3 ...
then i delete records older then 52
then i build a crosstub table having 1, 2, 3 ... on the top.

the only thing i need is how to calculate how old is the record in weeks

is it something like (date - today())/7

please help
this is a last thing i need

DateAdd("w", 52, Date()) will give you the date 52 weeks ago. Perhaps you can start from there.

 

[KenHigg]

Would that be:

DateAdd("w", -52, Date())

???

 

[MSAccessRookie]

Would that be:

DateAdd("w", -52, Date())

???

Oops! You are absolutely correct. In my haste to post an answer, I copied the formula and forgot to change it to -52. Thanks for the good catch

 

[KenHigg]

Oops! You are absolutely correct. In my haste to post an answer, I copied the formula and forgot to change it to -52. Thanks for the good catch

We'll attribute it to your 'Rookie' sub-title. I can write my errors off to 'old-timers'

 

[gemma-the-husky]

is this a commercial organisation

if you are really trying to manage 2m lines a week, surely your organisation can afford some professional help to get this sorted - it must be important to you

perhaps you don't need to store the data line by line - perhaps you could collapse it into appropriate sub totals for the purpose of your dbs