Archiving Records

Jul 24, 2006

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

View Replies


ADVERTISEMENT

Archiving Records

Oct 3, 2006

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

View 3 Replies View Related

Archiving Records

Oct 3, 2006

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

View 2 Replies View Related

Archiving Records From Two Tables

Nov 24, 2006

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

View 6 Replies View Related

Archiving Old Records Through A Form

Dec 11, 2007

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

View 4 Replies View Related

Archiving MS Access 2007 Table Records With Specific Date?

Jul 23, 2012

How do i archive Ms Access 2007 table records with specific date?

Any easy way to do it without writing any macros?

View 2 Replies View Related

Archiving

Jul 20, 2005

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.

View 1 Replies View Related

Archiving A Database

Sep 28, 2007

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 12 Replies View Related

Archiving Basics ???

Jul 6, 2005

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

View 7 Replies View Related

Archiving Of My Audit Trail

Nov 29, 2005

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 Related

Moving/ Archiving Data

Jul 24, 2006

Hi 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

View 2 Replies View Related

Archiving Data Each Time It Is Updated

Jun 27, 2005

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.

View 2 Replies View Related

Queries :: Archiving Data From Multiple Tables

Jun 24, 2013

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.

View 6 Replies View Related

Modules & VBA :: Archiving Data - Table Structures Are Identical

Jul 26, 2013

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.

View 1 Replies View Related

How To Set Criteria For Archiving Data Using Form-based Parameters

Dec 4, 2012

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....

View 14 Replies View Related

General :: Archiving Data - Improve Response Time Of Database?

Apr 30, 2015

Will 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?

View 2 Replies View Related

Modules & VBA :: Possible To Export Select Records And Fields In Those Records To A Specific Location?

Jun 15, 2013

In an Access 2010 form is it possible to export select records and fields in those records to a specific location?

Code:
Set objDialog = Application.FileDialog(4)
With objDialog
.AllowMultiSelect = False
.Title = "Please select a File"
.InitialFilename = "C:"
.Show
If .SelectedItems.Count = 0 Then
MsgBox ("Action Cancelled")
Else

[code]....

The user can select the directory using the code above, but can specific fields in records be exported to a excel workbook in that selected directory?For example, if the are 5 records in the database can the fields LastName,FirstName,BirthDate in records 1,2,3 be exported to Setup.xlsx in that selected directory?

View 1 Replies View Related

Tables :: Linking Records In One Table To Multiple Records In Another And Assign Percentage?

Nov 21, 2012

I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).

I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.

View 3 Replies View Related

Queries :: Mass Duplicate Main Records And Related Subform Records

May 29, 2014

In my simple database (attached), I need to mass duplicate Tasks and their Notes.

I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.

The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.

I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?

Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.

View 5 Replies View Related

Forms :: Getting Records On Start And End Date / Error - No Records Found

Jun 27, 2013

I am trying to get the records on start and end date, still showing error no records found.

My code is like this:

Private Sub Command90_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strReport As String
Const strcJetDate = "#dd/MM/yyyy#"

[code]...

View 1 Replies View Related

Forms :: Limited Number Of Records In Continuous Form / But Now Can't Add Records

Mar 22, 2013

One shows my form with the Transporters Subform with 3 entries, and 1 entry.The three line items that say "Transporter" are in one subform. I used this code

Code:
Private Sub Form_Current()
If Me.RecordsetClone.RecordCount >= 3 Then
Me.AllowAdditions = False
End If
End Sub

to limit the number of records I can add to 3 or less.My issue is that I lost the blank text box that allows you to add another record. So, if I only have one Transporter listed, there's no box to let me add a second or third.I have the following properties for the Transporters Subform set to "Yes":

Data Entry
Allow Additions
Allow Deletions
Allow Edits
Allow Filters

View 3 Replies View Related

Qry Results Show Duplicate Records, Records Are Not Dup In Table.

Nov 16, 2004

I have built a qry that initially shows the correct information. For example.

tblContent has 289 records with a Type = Class.

I built a Query to select from tblContent Type = Class and I get 289 records. I add additional criteria of Progress <>"Not Scheduled", I then get 206 records. I then add additional criteria Last Name <>"Demo" And <>"Care" And <>"Support". This brings up 200 records, but the query appears to duplicate each record 3 times. I do not have 3 of the same types of records.

The SQL Statement is below

SELECT tblProfile.LoginName, tblProfile.FirstName, tblProfile.LastName, tblProfile.Organization, tblProfile.CostCenter, tblContent.Title, tblContent.Type, tblContent.Code, tblContent.[Date Assigned], tblContent.[Date Started], tblContent.[Last Accessed], tblContent.Progress, tblContent.[Date Completed]
FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName
WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Support") AND ((tblContent.Type)="Class") AND ((tblContent.Progress)<>"Not Scheduled"));

The qry is named qryPhysical Class. I have provided the link to view the database. Can you help me?

http://briefcase.yahoo.com/turnerbkgabrobins

Thank you in advance for your assistance.

View 1 Replies View Related

Modules & VBA :: Exporting Subtable Records With Primary Records

Jan 12, 2014

All seemed to be working well, however, I noticed that all my subtable records in the database are exporting with each Primary table record. In my output, I'm looking to see each primary table record followed by one or more subtable records from a one to many relationship.

(Office 2010) Access/Word

Private Sub cmdPrint1_Click()
Dim objWord As Word.Application
Dim docm As Word.Document
Dim db As DAO.Database
Dim rstLandSales As DAO.Recordset

[Code] ......

View 14 Replies View Related

Modules & VBA :: Delete Records From A Table Based On Records In Another

Feb 7, 2014

I have a table InvPrice and Updated Pricing

Need to delete all records from InvPrice that Match UpdatedPricing

InvPrice.StockCode = UpdatedPricing.StockCode
InvPrice.PriceCode = UpdatedPricing.StockCode

I have tried something like this...

Dim dbs As DAO.Database, sql As String, rCount As Integer
Set dbs = CurrentDb
sql = "DELETE * dbo_InvPrice Inner Join (dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode ) ON on dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode "
dbs.Execute sql, dbFailOnError

View 14 Replies View Related

Forms :: How To Protect Old Records In A Form But Can Edit New Records

Dec 12, 2014

I have a form which needs update ever month. When the form is opened, the end user can see the old records and data, and also a new record is added for any new data. I want to protect the old saved data and the user can only add, edit, or delete the new data in the newly added record. The problem is once a user adds the new data and moves to another record or another form, then he/she cannot edit or change the new data in case if there is any mistake or need to change something after couple of minutes.

I changed the Form Data Properties "Allow Additions", "Allow Edits", and "Allow Deletions" many different times and situations to solve this problem but with no success. I tried the following with NO success too: One of the Fields of the Record is (Month). In the Data Properties, I set a Default Value for this field as(December 2014) for instance. I set the Data Properties "Allow Additions", "Allow Edits", and "Allow Deletions" to (Yes). Then I put the following code in the Form's Current Event:

Private Sub Form_Current()
If Me.month.Value = "December 2014" Then
Me.AllowAdditions = True
Me.AllowEdits = True
Me.AllowDeletions = True

[Code] ....

View 3 Replies View Related

Query Problem: Records Linked To Records

Oct 31, 2007

Hi,

I was wondering if anyone would be kind enough to help me out on this problem I have.

I have a table:
---------------------------------
Source | LinkedTo
---------------------------------
A1 B2
B2 C1
B2 C2
C1 D1
C2 D2
C2 D3
B2 D4
A1 D5

Another table:

-------------------------------
Item | Load
---------------------------------
D1 2
D2 4
D3 6
D4 8
D5 10

So looking at the tables,
C1 = 2
C2 = 4+6 = 10
B2 = C1 + C2 + D4 = 20
A1 = 20 + 10 = 30

Here, there are 4 levels that I have to go into to get the grand total. If I did not know how many levels there were, how would I be able to do this through code? through queries if possible??

If anyone could help, that would be great! Thank you in advance.

Bear

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved