How to Create a new Table from an existing Table in Database.

[saurabhshri1]

Hi Friends,

I am new to vba and access and is working on Populating the data of an excel sheet into an excess table.

Here is the Scenario.



I have created a databse named "Access_Converted" in a shared drive and in this database I have created a table called "Excel_to_Access".
Every month there comes a new version of this table, so what i want is that the entire structure of this "Excel_to_Access" table is copied to a new table. The name of this table is given by the user via inputbox on running the code.
I tried but i am not able to create a copy of the existing table.
Please refer the excel sheet and the Access_Converted database in the attachments.

Please help me guyz.

[rpeare]

Are you saying you do not know what the structure of your excel table is going to be from month to month and you want to be able to have something that will import the data into a table that is built to mimic your spreadsheet every month?

Or is is something simpler. If your excel spreadsheet the same from month to month and you just want to import the data into an existing table.

[saurabhshri1]

Are you saying you do not know what the structure of your excel table is going to be from month to month and you want to be able to have something that will import the data into a table that is built to mimic your spreadsheet every month?

Or is is something simpler. If your excel spreadsheet the same from month to month and you just want to import the data into an existing table.

I want that Table structure should be the same, every month the data in the sheet will change.

So i need to rename the existing table(say Access_Mar2013) and load the new data for March Month from excel sheet into it.

[rpeare]

if the structure is the same, why would you import every new month into a different table, that's really, really not a good idea particularly if you want to run any sort of history reports.

There are a number of ways you can accomplish what you want AND have all your history one one table so it's easier to query and keep track of. The very simplest way to do that is to simply have a 'date added' field in your table with a default value of date() so that when you do your import your 'new' records are automatically tagged with the current month (you can just as easily do this with a calculated value or the date of the file you're importing or other such mechanism). Or even, instead of your user input creating a table with the name they input have it become a value in a text field in the table excel_to_access so you have something to identify records by period. Your main issue here, and the reason I think you're trying to create separate tables, is that you have nothing in the data you receive to determine what time span its' for but you can create that artificially.

The very easiest way to do that is to create a module that does a docmd.transferspreadsheet into your table Excel_to_Access, but as I said put in a 'DateAdded' field and make the default value date(). Any time you do an import using your module any new records will get the current date so that you do have a method to break your data into time spans.