Save a created table

[hibbii]

I have a form that creates a table from various append queries. This table changes based on the quarter i choose from the combo box. Now what I am trying to do is find a way to automate the saving process so that for each quarter that is created I can save the table in a different name. Right now for whatever quarter I select the table is saved in "Calculated Schedule".

Is there a way to put a code in VB to do it or a separate command button.

Let me know if further clarification is needed.

Thanks.

[MMcCarthy]

I have a form that creates a table from various append queries. This table changes based on the quarter i choose from the combo box. Now what I am trying to do is find a way to automate the saving process so that for each quarter that is created I can save the table in a different name. Right now for whatever quarter I select the table is saved in "Calculated Schedule".

Is there a way to put a code in VB to do it or a separate command button.

Let me know if further clarification is needed.

Thanks.

This can be done but I wouldn't advise it as how many tables are you going to end up with over time.

Why not export the data to an excel file instead?

[hibbii]

That is probably true but for the time being I want to give it a shot. Do you mind going over how it can be done?

[MMcCarthy]

Hi,

You said in the thread that saving a created table was possible but not recommended. Could you please elaborate on that since that is what my boss wants to do.

Thanks.

Please don't PM me directly about coding issues but post to the forum. There are good reasons for this including the fact that other experts can add there comments on these issues.

To answer your question:

If you save a new table for every quarter then that will be four new tables a year. None of which has any relationship to the database structure but is only for archiving purposes. This increases the size of your database which does have a limit and I can't see any good reason for this.

The normal practice in cases like this is to export the data from the table every quarter to a excel file. This file can then be linked to the database using code and can be included in queries or for display purposes on a form datasheet, etc, As time goes on you can delete the link to files that are no longer relevant and yet retain the information in the excel file in case it is needed.

I can give you the code to do this if you're interested.

[hibbii]

Please don't PM me directly about coding issues but post to the forum. There are good reasons for this including the fact that other experts can add there comments on these issues.

To answer your question:

If you save a new table for every quarter then that will be four new tables a year. None of which has any relationship to the database structure but is only for archiving purposes. This increases the size of your database which does have a limit and I can't see any good reason for this.

The normal practice in cases like this is to export the data from the table every quarter to a excel file. This file can then be linked to the database using code and can be included in queries or for display purposes on a form datasheet, etc, As time goes on you can delete the link to files that are no longer relevant and yet retain the information in the excel file in case it is needed.

I can give you the code to do this if you're interested.

I am sorry about the PM, and yes if you could give me the code that would be great. Thanks for all your help.

[hibbii]

As another add-on question, can I transfer a table to a sreadsheet that is not yet created based on the quarter.

For instance, I want a spreadsheet for Q1 can I transfer the table to a spreadsheet and create the name at the same time.

[NeoPa]

Yes you can.
You specify the name of the workbook in your code and the worksheet name matches the name of the data source (Table or Query) used.
Here is a snippet of code using 'DoCmd.Transfer Spreadsheet()'.
This will not work for you directly, but should trigger some ideas.
Use any of it you want.

    'Delete temp file we will use, if it exists - if not, then not a problem
    On Error Resume Next
    Call Kill(strOut)
    On Error GoTo 0
    'Export to temp file - if this works then rename to required file
    strName = objRS.Name
    Set objRS = Nothing
    Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                                   TableName:=strName, _
                                   FileName:=strOut)
    On Error Resume Next
    Call Kill(strFile)
    On Error GoTo 0
    Name strOut As strFile
    If strType = "S" Then Call CurrentDb.QueryDefs.Delete(Name:=strName)
    ExportToExcel = strFile

[MMcCarthy]

I am sorry about the PM, and yes if you could give me the code that would be great. Thanks for all your help.

The following code will export your table to a file and then link that file to the database.

 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "TableName", "Full path to file", True ' this True means file will be given field names as headers
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel3, "NewLinkName", "Full path to file", True ' this True means file has headers for field names

[hibbii]

Yes you can.
You specify the name of the workbook in your code and the worksheet name matches the name of the data source (Table or Query) used.
Here is a snippet of code using 'DoCmd.Transfer Spreadsheet()'.
This will not work for you directly, but should trigger some ideas.
Use any of it you want.

    'Delete temp file we will use, if it exists - if not, then not a problem
    On Error Resume Next
    Call Kill(strOut)
    On Error GoTo 0
    'Export to temp file - if this works then rename to required file
    strName = objRS.Name
    Set objRS = Nothing
    Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                                   TableName:=strName, _
                                   FileName:=strOut)
    On Error Resume Next
    Call Kill(strFile)
    On Error GoTo 0
    Name strOut As strFile
    If strType = "S" Then Call CurrentDb.QueryDefs.Delete(Name:=strName)
    ExportToExcel = strFile

Thanks Neo, I am horrible with VBA could you help me adapt this into my database?

The problem I am trying to resolve is that how will I specify what workbook I can transfer my combo box results? If i create the table for Q1 how can I tell it to transfer to a workbook called "Final Q1" and if I make a table for Q3 how can I transfer to a different workbook called "Final Q3", etc. etc.

Wouldn't the transferspreads heet command put every quarter into the same workbook and same file?

[NeoPa]

Not without more specific information about your database.
I need to know the recordset (QueryName / TableName / whatever) you're exporting.
What you export when and under which circumstances.

You say you're not good at VBA - well you have a choice.
You can stay not good, or you can get better with experience.
We will certainly help here as much as we can, but that's no reason not to look at some of the sites that help beginners to become experienced users and eventually experts.

I suggest the link at the top of the Access forum is well worth exploring.
( Links to useful sites )

[hibbii]

Not without more specific information about your database.
I need to know the recordset (QueryName / TableName / whatever) you're exporting.
What you export when and under which circumstances.

You say you're not good at VBA - well you have a choice.
You can stay not good, or you can get better with experience.
We will certainly help here as much as we can, but that's no reason not to look at some of the sites that help beginners to become experienced users and eventually experts.

I suggest the link at the top of the Access forum is well worth exploring.
( Links to useful sites )

I am definately working on increasing my knowledge of VBA so I appreciate your encouragement.

I will give you the answers to your questions about my db.

I am have a table named "calculated_amo rt_schedules" which is sort of a temporary table depending on what quarters data I am looking for. This is created based off a combo box off my quarters table.

What I am looking for is create workbooks in excel with the file name designating the quarter I chose in the combo box. Say, I want FY06 Q3, that the name of the excel file will be "Calculated Schedule FY06 Q3", etc. Then I can link that table back to my database.