I have no idea how to even begin an archive. Can anyone give some direction? I've spent two days searching the web and trying to understand the Microsoft website directions along with Access for dummies. The only thing I can find is something about using Products_Appends and Products_Delete queries.
View RepliesWill archiving older data improve response time of a database?
What is the best way to archive data knowing that older records will still be accessed twice a year?
I have an application which creates 200 rows a day. As you can imagine the tables get big very quickly. Is there a mechansim that I can use to archive off the tables to a new seperate database rather than to a table in the same database. I would like to perfom this by allowing the user to click on a button and perform the archiving automatically.
my users are non technical and requesting them to perform any tasks behind the scenes would not be an option.
Anyone any experience of doing this ?
Thanx in Advanz.
Niall.
Hi guys
I need some help working out a way to archive records. All i really want is a button on a form that when clicked deletes the record currently displayed on the form from its table and moves it to another table that i will call 'archives'.
I think it might have something to do with using an append query but i cannot think how to do it.
Thanks in advance.
James
Hi Guys,
Im about to create a button on a form that will archive the current record to an archive table.....
The question is, Before I start to look into this, I would just like to clarify what I do.
I assume firstly I would need to make an exact copy of my current table, them remove all data, then change name to something like "tbl_archive_data"
( this would be to hold the archive info right ? )
Once I have done this, I`m assuming i`ll have to make some sort of query that will copy the data to the new table, then delete the record from my current table ?
Then I`ll have to create a button on my form, which can be cliocked on by the user to archive this particualr record...
In a nutshell, would this be sort of the correct way to do this ???
Any recommendations/advice will be much appreciated on this.
I know there is already some archive details in the forum, but none of them really made much sence, as they were answers to problems with this already half done....
Thanks in advance guys.
Max
Hi to everyone. I was assigned to collect all data regarding the electronic equipment of the firm that I work for and I just need a push to start with :D
My thought is to create 3 tables (one containing the users, another one containing info about the equipment and a third one to be something like an archive or a log...e.g. this monitor is now currently being used by me but in the past somebody else had it).
I am not quite sure how to do this (the archiving I mean...) Any help is welcome
Thanks...
Kyriakos
Hi guys
I need some help working out a way to archive records. All i really want is a button on a form that when clicked deletes the record currently displayed on the form from its table and moves it to another table that i will call 'archives'.
I think it might have something to do with using an append query but i cannot think how to do it.
Thanks in advance.
James
i created an audit trail in my DB... i do need to submit reports reflecting the relevant changes made.... however, i notice that the report can be very lengthy if not renewed every month.... can i archive these trails.. on a mthly basis? if yes.. how ? if not... is there anything else i can do?
View 1 Replies View RelatedHi all,
currently on my db it stores data on various projects, and these projects are sorted by a status of on hold, on going, or finished. What im trying do is move only the projects that are finished but still keeping a record of them so we can view them in the future.
i was thinking maybe i could move the finished projects into another db? but not sure how to do, or is there a better way to achieve this?
thank you
I have a database for tracking tasks. each task may have associated documents for it which are kept in a separate table.
I have an archiving procedure which takes closed or completed tasks (from the main table) and appends them to an archive table but it leaves the associated documents in the second table, or if I enforce integrity it will delete them.
Anyone have a suggestion on how i can archive records from two tables at the same time I include the archive code for your information....
Many Thanks as always for your help and assistance
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''
' procedure archives closed or complete records to archive table and then '
' Deletes original records from source table '
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''
On Error GoTo Err_btnArchiveAdhoc_Click
Dim strMySql As String, strResponse As String, intRecordCount As Integer
Dim cnn As ADODB.Connection
Dim rstTemp As ADODB.Recordset
strResponse = MsgBox("By clicking Yes you will Archive any closed or" & _
vbCrLf & "completed tasks and delete the original records from the table", vbExclamation + vbYesNo, gstrAppTitle)
If strResponse = vbNo Then
Exit Sub
Else
Set cnn = CurrentProject.Connection
Set rstTemp = New ADODB.Recordset
' builds sql string to append closed or complete records from the original table into archive table
strMySql = "INSERT INTO tblArchiveAdHocTasks ( Seq_Number, Item_Type, TaskTitle, Task_Description, TaskActions, StartDate, TgtDate, ReviewDate, TaskLead, Status, LastUpdatedBy, estHours, Progress, PersIDAssocPerson1, PersIDAssocPerson2, PersIDAssocPerson3, PersIDAssocPerson4, PersIDAssocPerson5 )"
strMySql = strMySql + " SELECT tblAdHocTask.Seq_Number, tblAdHocTask.Item_Type, tblAdHocTask.TaskTitle, tblAdHocTask.Task_Description, tblAdHocTask.TaskActions, tblAdHocTask.StartDate, tblAdHocTask.TgtDate, tblAdHocTask.ReviewDate, tblAdHocTask.TaskLead, tblAdHocTask.Status, tblAdHocTask.LastUpdatedBy, tblAdHocTask.estHours, tblAdHocTask.Progress, tblAdHocTask.PersIDAssocPerson1, tblAdHocTask.PersIDAssocPerson2, tblAdHocTask.PersIDAssocPerson3, tblAdHocTask.PersIDAssocPerson4, tblAdHocTask.PersIDAssocPerson5"
strMySql = strMySql + " FROM tblAdHocTask "
strMySql = strMySql + " WHERE (((tblAdHocTask.Status)=6 Or (tblAdHocTask.Status)=10))" ' status 6 is closed and 10 is complete
DoCmd.SetWarnings False
'DoCmd.RunSQL strMySql ' runs append query
rstTemp.CursorLocation = adUseClient
rstTemp.Open strMySql, cnn, adOpenDynamic, adLockOptimistic
DoCmd.SetWarnings True ' turn warnings back on
' deletes records that have been archived above
strMySql = "DELETE tblAdHocTask.Seq_Number, tblAdHocTask.Item_Type, tblAdHocTask.TaskTitle, tblAdHocTask.Task_Description, tblAdHocTask.TaskActions, tblAdHocTask.StartDate, tblAdHocTask.TgtDate, tblAdHocTask.ReviewDate, tblAdHocTask.TaskLead, tblAdHocTask.Status, tblAdHocTask.LastUpdatedBy, tblAdHocTask.estHours, tblAdHocTask.Progress, tblAdHocTask.PersIDAssocPerson1, tblAdHocTask.PersIDAssocPerson2, tblAdHocTask.PersIDAssocPerson3, tblAdHocTask.PersIDAssocPerson4, tblAdHocTask.PersIDAssocPerson5"
strMySql = strMySql + " FROM tblAdHocTask"
strMySql = strMySql + " WHERE (((tblAdHocTask.Status) = 6 Or (tblAdHocTask.Status) = 10))" ' status 6 is closed status 10 is complete
rstTemp.CursorLocation = adUseClient
rstTemp.Open strMySql, cnn, adOpenDynamic, adLockOptimistic
End If
MsgBox "Export Complete ", vbInformation, gstrAppTitle
Exit_btnArchiveAdhoc_Click:
Exit Sub
Err_btnArchiveAdhoc_Click:
If Err.Number = 2501 Then ' runtime error as user cancelled delete part of query
Exit Sub
End If
MsgBox "'Error" & Err.Number & "'" & vbCrLf & Err.Description
Resume Exit_btnArchiveAdhoc_Click
End Sub
I'm not even sure which forum to put this in, as it involves a form, two tables, some queries, and possibly a macro. I've poked around the message boards here and haven't quite found a method that works for my situation. So here we go!
I have a recruiting database that I've developed for some other users in the office who are not Access builders. They have asked if there is a way to archive old records into another table for safekeeping, which I can do through the database window, but which they probably couldn't. I want to give them the ability to tuck these records away as needed, and without them having to ask me to do so every time.
The main table in the DB is called tblPosition. There is a form called frmPositionManagement which is bound to this table. It's through this form that the recruiters make all their updates and whatnot to records stored in tblPosition. I also have a table called tblArchive which I have created using the exact same fields as tblPosition so I can store the outdated records there.
I'm looking for a programmatic way to do this. I was hoping to put a button on frmPositionManagement that would let the user remove that record from tblPosition and send it to tblArchive. I had figured on creating a macro (mcoArchive) that took the following steps:
* MsgBox "Are you certain you want to archive this record?"
* Run an Append Query to add the selected record to tblArchive
* Run a Delete Query to remove the record from tblPosition
* Close the Append Query
* Close the Delete Query
Then I started building those two queries and the macro, and the wheels fell off my brain.
The difficulty I'm having is getting the system to say to itself, "The record currently displayed on the frmPositionManagement is the one I need to append and delete." I have a specific record selected on the form, so how do I pass that record's unique ID through to the two queries to make this record the one which is appended/deleted?
I had tried setting the criteria for the unique key in the Append/Delete queries to =[frmPositionManagement]![AutoID], but that just led to a pop-up box that asked me to input frmPositionManagement!AutoID, which is not what I wanted at all.
My VB isn't all that great, so I was trying to stick to macros, but if someone has an idea for a programmatic solution for this conundrum, I'd appreciate hearing it!
Thanks,
Andreas
Hi, I have a form which is made up for 3 tables and I am trying to create an append query for each one in order to keep records of data before it is updated. The append queries seem to work but they append all data rather than just one selected record. I know I will next need to create a macro which can be used each time a record has been updated and a copy is sent to the archive. Can anyone help me with this, or have any useful suggestions?
Thanks.
I have a database with employees. The tables are as follows:
Deptdatatble
Depttble
Emptble
HRtble
Servicetble
Servicedatatble
Archivetble
Classestble
Classdatatble
At certain times, I want to archive employees out (lets say they are terminated). When I do this, something strange happens. If an employee has 4 records in the servicedata table and 4 records in the Classdata table, then it exports out 16 records (4 x 4). I would expect it to export out 8 records.
I'm trying to create an archiving routine as my database is becoming very large. For about 10 tables I want to shift certain records to an external database which would have the required 10 tables with the same table names and structure.
So far so good. I now want to automate everything using vba. I can see how to use the INSERT INTO statement but I don't want to have to name every field as there are hundreds. I just can't see how to do this.
If the table structures are identical how do I neatly shift a bunch of records from one to the other using code.
I'm trying to create an archiving system, where i use a simple Append Query followed by a Delete Query.
A typical criteria for the Append Query is less than Date()-30...so any records older than 30 days can be appended to an archive table. This works fine when i enter it in the Query Design criteria row.
But, I would like to make this user-defined. I have set up an unbound form as shown in the first attachment...and made a global variable entitled 'ArchiveDays'. I am hoping to use the variable to act as the criteria for the append criteria. (Please note that in the screendump...they can select an option button if they just want to stick to 1 month old. I also show you my assignment operations there).
My question is... how do i get the variable 'ArchiveDays' value to be the criteria for my append query....
How do i archive Ms Access 2007 table records with specific date?
Any easy way to do it without writing any macros?
I'm trying to set up a simple query that links four tables. However, the tables are extremely large, all in excess of 1.5GB each so I had to split the tables up into four separate DBs. I've tried the following with no success:
1) Link the 4 tables in the DB which contains my primary key. This quickly inflates increases the file size above 2GB and won't let me go any further.
2) Build a remote query to connect the four tables. This looked promising until I tried to run the query and it became evident that it only knows to point to the last database source that you specified.
I'm running everything locally on my C drive. The data source are simple text files (1.6 million rows) from the FDA website.
Hello All...
Well, I am facing one problem..in my application; I need to show all forms / reports name of other database( .mdb ) file without opening the other database physically. I tried a lot but didnt succeded. I tried with below code..
Set AcApl = New Access.Application
Call AcApl.OpenCurrentDatabase(strfolder, True)
Set AcProj = AcApl.CurrentProject
Set frm1 = AcProj.AllForms
intCount = frm1.Count
But here wen the second line AcApl.opencurrentdatabase get executed at that time the database get open physically, and i dont want that..So is there any other way around..If so..please please help me..
Thanks in advance..!!
i have a database that runs updates from within itself.what i need is, this database to then open a another database run a update query, then close it.
View 4 Replies View RelatedDatabase: Access 2007
I have designed a touchscreen input system using Visual Basic.net and this writes to an Access Database. Each Touchscreen has its database locally so it can still work even if there are Network problems.
Now what I would like to do is have all these local databases write to a central database say every minute but only write new records to the central database. The Central Database can either be Access or SQL.
What is the best way to do this?
I have been working on a database for over a month now, and my boss just threw a monkey wrench in my work. I believed that the Metal Market Prices would be entered once a week in the current DB. My boss informs me today that he wants an employee to go in every morning and enter that days Price for Each Metal with respect to many different markets.
There is a total of 12 metals, and 5 markets. I need the data to be stored first by date, then by either market or metal, and lastly by which ever isn't used second (Either: date-market-metal, or date-metal-market). I think the Latter of the two methods makes the most sense. Is it possible for my current DB to lookup values from the Metals Database based on date-metal-market?
I have a problem that seems to be happening on several users' databases and is causing a big problem. None of the databases is a shared database...they are all single-user databases on stand-alone computers. I have tried looking for help within previous posts, but all seem to be related to shared databases.
I am getting an error message: "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time." The database cannot be opened, imported, repaired...nothing seems to work.
Again...these are NOT shared databases. I appreciate any help I can get. I created the database for all of the secretaries in our school district to keep up with absence data. It involves many tables, queries, forms and reports, and has generally worked well. However I am now seeing several that are getting similar errors as mentioned.
Thanks!
I'm new to Access and VBA, for the record. What I need to do is copy a SQL database (table structures and records) and save it as a local Access database, so that the user can query and make any changes on the local file without editting the SQL database.
Looking around, it seems like the best option would be to use the TransferDatabase method with the acImport option. Does this sound reasonable, or can someone with more experience suggest a better way to go about it? Thanks.
I am testing the security of my DB X on Acccess 2007. I could create a new database Y and linked to the database X. Unfortunately i could change the records on the tables. I don't want other DB that make connections to my DB to change my tables!
View 1 Replies View RelatedIs it possible to access a table in one Database (Database A) from a separate Database (Database B) and if so how. I should clarify that this is an Access Database.
View 2 Replies View RelatedI created a database with forms, querys, etc. Now I was thinking of creating a exe so everyone can use this database without having access to all the design functions.
the only problem I see about doing this is if we need to use the same database for different project, we need to always create a new exe for each project.
Is there a way to make a empty database exe with just the layout, querys etc but with no data on the tables and have a save, open and save as option. So we can have several projects using the same database?
I was thinking to create a VB code for the database to delete all data on the tables when the database is open.
and before closing the database exporting all the data to a txt file or something.
Then the next time someone opens the database goes to the main form which has a open bottom which imports the txt file into the tables.
This way we can have several txt file for several project and use always the same exe database.