Deleting and Creating a Table

[azizrasul]

Is there a way of coding the deletion of a table in MS Access and recreating it using data in the MS Excel file. I want to count the number of rows in a column (which can vary day to day) and create a table called "tblKPIs" with fields equal to the number of rows e.g if I have 10 rows then the fields will be F1, F2, ... F10.

Alternatively I don't have to delete the table and just have a 100 fields (as I know the number of rows will not exsceed this number) but delete the data before exporting to the table.

 

[Norie]

How do you intend to calculate the Actual values?

If you were to do it in Access with a separate field for each date you would probably have to repeat the same calculation 3* times.

That doesn't sound particularly efficient to me, and sort If defeats the purpose of using Access.

* That's 3 times for this example with only 3 dates.

It will be X times for X dates.

 

[azizrasul]

The idea is to use SQL in VBA code to loop through the months and capture the Actual values and enter them in the table using SQL again in the VBA code. Once I have deleted the table and recreated it, I want to invoke the code (or macro) in the Access db to populate the empty columns in the table. I have still to write this code. At the moment I'm concentrating on the 'grand plan'.

Once the table is populated, I want to copy the data in the table back into the Excel file. All the data is held in the Access db.

 

[Norie]

Aziz

Why do you need Access for that?

You could probably do it with 'standard' VBA.

Can you post an example If the SQL you would use?

By the way I'm not trying to give a hard time I just think you might not quite understand the differences between Access and Excel.

 

[azizrasul]

Well I haven't got to that stage yet but it would be something like

UPDATE tblGroupSessions INNER JOIN Table1 ON tblGroupSessions.sessionstartdate = Table1.F1 SET Table1.F1 = [sessionbookings]
WHERE (((Table1.F1) Is Null));

in a loop using F3, F5, etc. fields.

If there is a VBA way doing this, I'm open to suggestion. As all the queries would be created on the 'fly' and created dynamically depending on the number of columns, the table would automatically be populated by invoking the necessary function|sub|macro.

 

[Norie]

Aziz

So you would run that query, with the field names changed as time as the no of dates?

If you didn't have a separate field for each date you could probably do that in one query.

 

[azizrasul]

Yep, run the query and get back the results to the Excel sheet.