473,327 Members | 2,065 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,327 software developers and data experts.

Create a carbon-copy of a table with vba?

Hi All,

Is it possible to create a carbon-copy of a table using VBA? I have a table called 'Junction' that stores the structure of a machines assemblies and subassemblies, and I need to create a copy of this table, like a freeze-frame type of thing, about every 2 months in order to store the history of the machines contents. I need this to happen on a button click event, and the name of the 'carbon-copy' table can be something other than Junction (eg. Frozen Junction mm dd yyyy).

Any ideas? Is this possible?

Thanks!
Nov 22 '07 #1
8 4956
DoCmd.CopyObject , "Frozen Junction " & Format(Date, "mm dd yyyy"), acTable, "Junction"
Nov 22 '07 #2
missinglinq
3,532 Expert 2GB
That will do the task you requested, but how big is this table? I ask because this method will mean that your database will be adding 6 new tables a year, which could quickly add to the apps size. You might also think about using DoCmd.OutputTo to output your table to a snapshot file. The files would be stored outside of the database itself.

Linq ;0)>
Nov 22 '07 #3
Two more ideas for a different approach:

Create a new blank database, call it C:\History.mdb and then use this:

DoCmd.CopyObject "C:\History.mdb", "Frozen Junction " & Format(Date, "mm dd yyyy"), acTable, "Junction"

which will keep your production file smaller, or...

if this is only done 6x per year, you could just use Windows Explorer to make a copy of the production database. Or, with Access 2003 you could click Tools ~ Database Utilites ~ Backup Database and it automatically appends the date to the file name.
Nov 22 '07 #4
These are excellent ideas. I didn't think of the size of the table....it is currently 2000 records and has the potential to grow to 20 000.

Is this going to cause major problems? The idea was to be able to do some statistical analysis on the frozen tables, and also comparing the different frozen tables. The statistical analysis would need to access some information from other tables within the database (a table that stores multiple price quotes for each part, and a table that stores all of the parts in the database). For this reason I was hoping to keep these frozen tables in the same database....is this going to make the database to large and seriously slow things down?

Any help is greatly appreciated.

Thanks
Nov 23 '07 #5
You might also think about using DoCmd.OutputTo to output your table to a snapshot file. The files would be stored outside of the database itself.

Linq ;0)>

Can I then have an Access form based off of this snapshot file? I need to view the contents of this table, and also do some statistical analysis on it and possibly other snapshot files. Would I need to import it as a temporary table type-of-thing in order to view the contents with an Access form?
Nov 23 '07 #6
missinglinq
3,532 Expert 2GB
If you're going to want to actually use the archived data the snapshot file isn't the appropriate approach. I would move copies of the tables to a second database and do your statistical analysis there. From the "Archive" db you can, I believe, link to whatever you need from the "Current" db.
I've never done this, but I'm sure others here have and will be happy to help you if need be!

Linq ;0)>
Nov 23 '07 #7
The theoretical limit for an Access file is 2 GB, but in practice when you hit 200 MB you start to have serious problems with performance and stability. As Linq said, keeping those frozen tables inside the main file will eventually break the application.

What is the maximum row size in the table? Multiply that by the expected number of rows & you'll get an idea of how much bigger it will get. (There is some overhead per table and per row, but we're looking for a rough idea.) If you're not sure how to calculate that, post your table structure & I can help figure that out.

Here is another option: Similar to my original idea, create a single Frozen Junction table in the C:\History.mdb file, remove any unique indexes from it, and add a date column with a default of =Date. Then you can attach to that table, append from the real table to that one to store your history, and run queries against the linked table.
Nov 23 '07 #8
Thank you mitch and link for this information. It seems that my desire to do some analysis between 'frozen' tables will be more difficult than I thought. I have been taking your advice into consideration, and I'm still thinking about it...only have 2 weeks left on this project so it might not be possible in the end. I'm sure I'll be making more posts if I give the History database a shot.

Thanks again for your help.
b
Dec 5 '07 #9

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

Similar topics

8
by: Raymond H. | last post by:
Hello, 1- How to see, in a Label, the URL of a link that the mouse pass over? (in the WebBrower control in a vb projet). 2- How to create a menu and a submenu via a button Command1? and...
1
by: fortepianissimo | last post by:
This is a question only relevant to Mac OS X. Could someone offer a simple example how to use Carbon.File module (perhaps Alias.FSResolveAlias()?) to resolve an alias? Basically I'd like to load...
3
by: M. Mehta | last post by:
It seems that you can not create a materialized view if you are using outer joins...can someone please verify this? Thanks M. Mehta Please follow my example below: created 2 tables:
4
by: MD | last post by:
I am trying to create a dynamic SQL statement to create a view. I have a stored procedure, which based on the parameters passed calls different stored procedures. Each of this sub stored procedure...
5
by: me | last post by:
I have a Class Library that contains a Form and several helper classes. A thread gets created that performs processing of data behind the scenes and the Form never gets displayed (it is for debug...
6
by: Rajesh N Thipse | last post by:
Hi, Files can be created using _creat. Similarly, is there a function to create a directory? ~Rajesh
37
by: Steven Bethard | last post by:
The PEP below should be mostly self explanatory. I'll try to keep the most updated versions available at: http://ucsu.colorado.edu/~bethard/py/pep_create_statement.txt...
18
by: Steven Bethard | last post by:
I've updated the PEP based on a number of comments on comp.lang.python. The most updated versions are still at: http://ucsu.colorado.edu/~bethard/py/pep_create_statement.txt...
3
by: kitty | last post by:
Hi, Iam using the Carbon framework to read data from XML files, but it does not read values placed inside curly braces {}. Example: <output>{property}</output> I have tried using numeric...
1
by: Konstantinos Pachopoulos | last post by:
Hi, i have the following files: current_dir/level1/Called.py current_dir/Caller.py Called.py: ----------- class Called: def exec1(self):
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...
1
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: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
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...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.