473,321 Members | 1,916 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Save a created table

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.
Nov 7 '06 #1
10 2380
MMcCarthy
14,534 Expert Mod 8TB
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?
Nov 7 '06 #2
hibbii
23
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?
Nov 8 '06 #3
MMcCarthy
14,534 Expert Mod 8TB
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.
Nov 9 '06 #4
hibbii
23
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.
Nov 9 '06 #5
hibbii
23
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.
Nov 9 '06 #6
NeoPa
32,556 Expert Mod 16PB
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.
Expand|Select|Wrap|Line Numbers
  1.     'Delete temp file we will use, if it exists - if not, then not a problem
  2.     On Error Resume Next
  3.     Call Kill(strOut)
  4.     On Error GoTo 0
  5.     'Export to temp file - if this works then rename to required file
  6.     strName = objRS.Name
  7.     Set objRS = Nothing
  8.     Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
  9.                                    TableName:=strName, _
  10.                                    FileName:=strOut)
  11.     On Error Resume Next
  12.     Call Kill(strFile)
  13.     On Error GoTo 0
  14.     Name strOut As strFile
  15.     If strType = "S" Then Call CurrentDb.QueryDefs.Delete(Name:=strName)
  16.     ExportToExcel = strFile
Nov 9 '06 #7
MMcCarthy
14,534 Expert Mod 8TB
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "TableName", "Full path to file", True ' this True means file will be given field names as headers
  3. DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel3, "NewLinkName", "Full path to file", True ' this True means file has headers for field names
  4.  
  5.  
Nov 10 '06 #8
hibbii
23
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.
Expand|Select|Wrap|Line Numbers
  1.     'Delete temp file we will use, if it exists - if not, then not a problem
  2.     On Error Resume Next
  3.     Call Kill(strOut)
  4.     On Error GoTo 0
  5.     'Export to temp file - if this works then rename to required file
  6.     strName = objRS.Name
  7.     Set objRS = Nothing
  8.     Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
  9.                                    TableName:=strName, _
  10.                                    FileName:=strOut)
  11.     On Error Resume Next
  12.     Call Kill(strFile)
  13.     On Error GoTo 0
  14.     Name strOut As strFile
  15.     If strType = "S" Then Call CurrentDb.QueryDefs.Delete(Name:=strName)
  16.     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?
Nov 13 '06 #9
NeoPa
32,556 Expert Mod 16PB
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 )
Nov 13 '06 #10
hibbii
23
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.
Nov 13 '06 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

11
by: deko | last post by:
I need to create different recordsets based on queries that use data from unbound fields in a form. I've discovered that I can't do this, and instead need to save the data in question (usually a...
0
by: ssg31415926 | last post by:
I've been trying to save a hashtable in an Application Settings file. I need to save settings for each tabPage on a form. Trouble is, the number of tabPages is determined at runtime, so I can't...
4
by: kev | last post by:
Hi folks, I have created a database to store information on equipments. During the first level of registration, there is a form that i need the user to fill up details on the equipment testing....
4
by: Whasigga | last post by:
Hi I've created a form that has 7 subforms. It is the same subform, bound to a table, just repeated. This form represents a week, and each subform is used to enter in data for each day of the...
2
by: Karl | last post by:
Using A2000 When I click the save icon in form design, Access closes immediately. No warning messges, nothing. This happens on only one form. I deleted the form and recreated it. I could save...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.