Save a created table | Newbie | | Join Date: Oct 2006
Posts: 23
| | |
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.
|  | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,875
| | | re: Save a created table
Quote:
Originally Posted by 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. 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?
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: Save a created table
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?
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,875
| | | re: Save a created table
Quote:
Originally Posted by hibbii 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.
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: Save a created table
Quote:
Originally Posted by mmccarthy 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.
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: Save a created table
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.
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,722
| | | re: Save a created table
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.TransferSpreadsheet()'.
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
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,875
| | | re: Save a created table
Quote:
Originally Posted by hibbii 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
-
-
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: Save a created table
Quote:
Originally Posted by 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.TransferSpreadsheet()'.
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 transferspreadsheet command put every quarter into the same workbook and same file?
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,722
| | | re: Save a created table
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 )
| | Newbie | | Join Date: Oct 2006
Posts: 23
| | | re: Save a created table
Quote:
Originally Posted by 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 ) 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_amort_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.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,419 network members.
|
